Hi, it seems that screen shots were stripped by the mailing list software. I have uploaded them to flickr. Here are the links: http://www.flickr.com/photos/78207256@N07/7166944408/in/photostream http://www.flickr.com/photos/78207256@N07/7166945108/in/photostream Best regards Andrej Golovnin On 09.05.2012, at 22:35, Andrej Golovnin wrote: > Hi all, > > I have today deployed our application for the first time > on JBoss 7 with the latest Hibernate and Hibernate Search versions. > And I was totally shocked when I saw the memory consumption. > > I have created two screen shots from the memory dumps. The screen shots > are attached to this mail. Please take a look at them. The screen shot > names contains version numbers of the used Hibernate and Hibernate Search > frameworks. The memory dumps were created for one and the same > application version. I can't believe that the latest versions require nearly > twice as much memory as the old versions to perform the same tasks. > What's happen? > > Is this a known problem? Do you have any plans to change this situation, > e.g. to work on reducing memory consumption? Or maybe you already > started to work on this problem? > > We have been running our application with 768MB for max heap size and > serving hundreds of users. And now using the same memory settings I can > just deploy the application. :-( > > Best regards > Andrej Golovnin > > _______________________________________________ > hibernate-dev mailing list > hibernate-dev(a)lists.jboss.org > https://lists.jboss.org/mailman/listinfo/hibernate-dev _______________________________________________ hibernate-dev mailing list hibernate-dev(a)lists.jboss.org https://lists.jboss.org/mailman/listinfo/hibernate-dev

Hi Andrej, Hibernate Search at version 3.0.1 did a small fraction of what it does today so I would expect indeed it to take a bit more memory but no I wasn't aware of significant increases. Looking just at the Search-related figures however it looks like memory consumption is significantly *lower* than the old times? That's not really expected, so I'm assuming that some of the memory used by SessionFactoryImpl, Configuration, JavaReflectionManager is actually holding to references which are Search related. In short, it would help us a lot if you could open the details and explore a bit around the references to understand where things might have done out of control :-( Among other improvements in Search, it does now keep open references to both IndexReaders and IndexWriters, and both consume quite some memory as they aggressively cache metadata from the indexes. This is a good thing, as otherwise you would be using this same memory anyway, but per-hit causing a lot of work for GC but still showing better figures for the older version as the memory is not really "reserved".. could you compare them as well by disabling such features in Search ? Same for FieldCaches and other stuff.. in practice: - hibernate.search.​default.​exclusive_index_use = false - hibernate.search.default.reader.strategy = not-shared - @CacheFromIndex <- all explicitly disabled since it defaults to "class" - avoid filter caching I guess you're comparing them without any application or configuration changes, still you would need to make these configuration changes to make the comparison fair as these features didn't exist at the time and are now enabled by default. This will disable some benefits you get from latest Search, but not all of them.. just curious to see if it gets us into more reasonable limits. Regards, Sanne On 9 May 2012 22:11, Scott Marlow <smarlow(a)redhat.com&gt; wrote: > This sounds related to the discussion here > https://community.jboss.org/message/729224#729224. > > How many persistence units do you have defined in the application? Can > you get a list of files in the deployment (e.g. run "jar tf > Appname.?ar") and show us the files. > > On 05/09/2012 04:58 PM, Andrej Golovnin wrote: >> Hi, >> >> it seems that screen shots were stripped by the mailing list software. >> I have uploaded them to flickr. Here are the links: >> >> http://www.flickr.com/photos/78207256@N07/7166944408/in/photostream >> http://www.flickr.com/photos/78207256@N07/7166945108/in/photostream >> >> Best regards >> Andrej Golovnin >> >> On 09.05.2012, at 22:35, Andrej Golovnin wrote: >> >>> Hi all, >>> >>> I have today deployed our application for the first time >>> on JBoss 7 with the latest Hibernate and Hibernate Search versions. >>> And I was totally shocked when I saw the memory consumption. >>> >>> I have created two screen shots from the memory dumps. The screen shots >>> are attached to this mail. Please take a look at them. The screen shot >>> names contains version numbers of the used Hibernate and Hibernate Search >>> frameworks. The memory dumps were created for one and the same >>> application version. I can't believe that the latest versions require nearly >>> twice as much memory as the old versions to perform the same tasks. >>> What's happen? >>> >>> Is this a known problem? Do you have any plans to change this situation, >>> e.g. to work on reducing memory consumption? Or maybe you already >>> started to work on this problem? >>> >>> We have been running our application with 768MB for max heap size and >>> serving hundreds of users. And now using the same memory settings I can >>> just deploy the application. :-( >>> >>> Best regards >>> Andrej Golovnin >>> >>> _______________________________________________ >>> hibernate-dev mailing list >>> hibernate-dev(a)lists.jboss.org >>> https://lists.jboss.org/mailman/listinfo/hibernate-dev >> >> >> _______________________________________________ >> hibernate-dev mailing list >> hibernate-dev(a)lists.jboss.org >> https://lists.jboss.org/mailman/listinfo/hibernate-dev > > _______________________________________________ > hibernate-dev mailing list > hibernate-dev(a)lists.jboss.org > https://lists.jboss.org/mailman/listinfo/hibernate-dev _______________________________________________ hibernate-dev mailing list hibernate-dev(a)lists.jboss.org https://lists.jboss.org/mailman/listinfo/hibernate-dev

> After updating JBoss 7 to use the Hibernate Validator 4.3.0 and > adding<property name="javax.persistence.validation.mode" value="NONE"/> > to our persistence.xml file I was able to reduce memory consumption > of SerializableValidatorFactory from ca. 90MB to ca. 256KB. :-) It sounds like adding <property name="javax.persistence.validation.mode" value="NONE"/>, is responsible for the memory savings, more than using Hibernate Validator 4.3.0. If not, it would be helpful for us to know that.

Also, it might be helpful for us to see your persistence.xml.

> What I was not able to do, is to avoid loading of Hibernate Validator > and Hibernate Envers at all as we don't use them.

> It's also unclear to me why Configuration, JavaReflectionManager, > FullTextIndexEventListener don't show up.

> I need more time to investigate the differences between the old and > new versions. But one thing you can change already: > Configuration#seconPasses should be recreated > when Configuration#secondPassCompile() finishes or consider using > LinkedList. In my case the #seconPasses has the size of 0. But the backend > array has the length of ca. 27000. With this change you will not save a lot of > memory, but I welcome every single byte I can get. :-)

Hi Hardy, > Right, in this case validation mode NONE is the determining factor. I would think you get the same result w/ Validator 4.2.0. Nope, it was the Validator 4.3.0. > application or as part of the container startup? > And what exactly do you mean w/ Hibernate Validator gets loaded? Do you just see the ValidatorFactory or are there other instances from the org.hibernate.validator. Right after startup of the application server I see instances from the org.hibernate.validator package (see http://www.flickr.com/photos/78207256@N07/7177561006/in/photostream). Some time later they are garbage collected. SerializableValidatorFactory remains in memory and consumes ca 256KB. There is also either AuditConfiguration or AuditEntitiesConfiguration (don't remember now) which consumes ca. 2.5MB. As I already wrote we don't use Envers and we don't use Validator. None of our classes has Validator related annotations So why they are loaded at all? I have tried to exclude this modules in jboss-deployment-structure.xml by adding following exclusions: <exclusions> <module name="org.hibernate.validator"/> <module name="org.hibernate.envers"/> </exclusions> But it does not seem to work. Best regards, Andrej Golovnin _______________________________________________ hibernate-dev mailing list hibernate-dev(a)lists.jboss.org https://lists.jboss.org/mailman/listinfo/hibernate-dev

After updating JBoss 7 to use the Hibernate Validator 4.3.0 and adding <property name="javax.persistence.validation.mode" value="NONE"/> to our persistence.xml file I was able to reduce memory consumption of SerializableValidatorFactory from ca. 90MB to ca. 256KB. :-) What I was not able to do, is to avoid loading of Hibernate Validator and Hibernate Envers at all as we don't use them.

Hi Emmanuel, > Andrej, I wonder if you could share with us these memory dumps privately so we could also analyze them. I'm sorry but this is not possible. But I will give my best to find the root cause of this problem. Here are my findings so far: - In the memory dump with Hibernate 4 there are a lot of small String objects. The content of these objects starts always with "$PlaceHolder$." followed by a column name. This was introduced by HHH-4440. Maybe interning of these strings would help. I will try it on monday. - EntityPersisters contain SQL related strings which are never used. For example: AbstractEntityPersister#temporaryIdTableDDL and #temporaryIdTableName. It exists also in Hibernate 3. I know for sure that this #temporaryIdTableDDL is never used in our application as the database user used by the application has no rights to create temporary tables. Hibernate generates always all SQL statements regardless whether they are required during the life time of an application or not. Because Hibernate 3 shows the same behavior in this case, I wouldn't touch it for now. But you should consider it as possible improvement for the future. - We make use of UserTypes. Although the specification of UserType requires custom implementations to be immutable, Hibernate creates multiple instances, to be exactly for every occurrence of the Type annotation, of the same implementation of UserType instead of reusing already created instances. Hibernate 3 and 4 share here the behavior. This could be also improved in the future. Personally I would prefer to not use the Type annotation at all. Users of Hibernate may publish they implementations of UserType by means of the ServiceLoader from JDK 6. - There are a lot of empty ArrayLists, HashMaps and HashSets which are empty and have the default initial capacity (e.g. 10 by ArrayLists and 16 by HashMaps). But those objects exists also in the memory dump with Hibernate 3. We may improve it when we change how collections are created and filled in Hibernate code. I have done small changes to org.hibernate.mapping.SimpleValue (see http://goo.gl/yKx5D). These changes reduce the size of Configuration in my case from 16187776 bytes to 15161672. The changes may look like an ugly hack and they have higher resource costs (CPU, memory) during creation of SessionFactoryImpl. But it should help to reduce memory if we use it wherever it makes sense. What do you think? BTW, what is the purpose of CollectionHelper.EMPTY_LIST? Why don't you use Collections.EMPTY_LIST? That's all for now. Best regards, Andrej Golovnin

Btw for sure simpleValue should use an arrayList initialized to 1 by default as it's the most common case. We might even consider that an array would be better as the final holder even though there are no agreement in the team yet on the pro/con of arrays vs collections wrt ease of use, speed and memory consumption. On 13 mai 2012, at 11:07, Emmanuel Bernard <emmanuel(a)hibernate.org&gt; wrote: > We deliberately chose to optimize hibernate ORM to be fast even if that means a bit more memory. > We can try and avoid some objects here as there as you propose but I don't think that would be as beneficial as finding the real culprit between H3 and H4's memory consumption difference. > > On 12 mai 2012, at 22:48, Andrej Golovnin <golovnin(a)gmx.net&gt; wrote: > >> Hi Emmanuel, >> >>> Andrej, I wonder if you could share with us these memory dumps privately so we could also analyze them. >> >> I'm sorry but this is not possible. But I will give my best to find the root cause >> of this problem. >> >> Here are my findings so far: >> >> - In the memory dump with Hibernate 4 there are a lot of small >> String objects. The content of these objects starts always with "$PlaceHolder$." >> followed by a column name. This was introduced by HHH-4440. >> Maybe interning of these strings would help. I will try it on monday. >> >> - EntityPersisters contain SQL related strings which are never used. >> For example: AbstractEntityPersister#temporaryIdTableDDL and >> #temporaryIdTableName. It exists also in Hibernate 3. I know for sure >> that this #temporaryIdTableDDL is never used in our application >> as the database user used by the application has no rights to create >> temporary tables. Hibernate generates always all SQL statements >> regardless whether they are required during the life time of an >> application or not. Because Hibernate 3 shows the same behavior >> in this case, I wouldn't touch it for now. But you should consider >> it as possible improvement for the future. >> >> - We make use of UserTypes. Although the specification of UserType >> requires custom implementations to be immutable, Hibernate creates >> multiple instances, to be exactly for every occurrence of the Type annotation, >> of the same implementation of UserType instead of reusing already >> created instances. Hibernate 3 and 4 share here the behavior. >> This could be also improved in the future. Personally I would prefer >> to not use the Type annotation at all. Users of Hibernate may >> publish they implementations of UserType by means of the ServiceLoader >> from JDK 6. >> >> - There are a lot of empty ArrayLists, HashMaps and HashSets which >> are empty and have the default initial capacity (e.g. 10 by ArrayLists and 16 >> by HashMaps). But those objects exists also in the memory dump >> with Hibernate 3. We may improve it when we change how collections >> are created and filled in Hibernate code. I have done small changes to >> org.hibernate.mapping.SimpleValue (see http://goo.gl/yKx5D). These >> changes reduce the size of Configuration in my case from 16187776 bytes >> to 15161672. The changes may look like an ugly hack and they have >> higher resource costs (CPU, memory) during creation of SessionFactoryImpl. >> But it should help to reduce memory if we use it wherever it makes sense. >> What do you think? >> >> BTW, what is the purpose of CollectionHelper.EMPTY_LIST? >> Why don't you use Collections.EMPTY_LIST? >> >> That's all for now. >> >> Best regards, >> Andrej Golovnin >> > > _______________________________________________ > hibernate-dev mailing list > hibernate-dev(a)lists.jboss.org > https://lists.jboss.org/mailman/listinfo/hibernate-dev _______________________________________________ hibernate-dev mailing list hibernate-dev(a)lists.jboss.org https://lists.jboss.org/mailman/listinfo/hibernate-dev

Configuration is not kept around by Hibernate itself once the SessionFactory is built.  Nor is SimpleValue.  And as Hardy already mentioned, that code is undergoing major changes already for 5.0.

Take 'temporaryIdTableDDL' as the perfect example. Hibernate cannot know at start up (building the SessionFactory) that the application will or will not use HQL updates/deletes against "multi-table structures" that would therefore need access to 'temporaryIdTableDDL'? And the problem with lazily generating them later is that that would require keeping around the Configuration as part of the SessionFactory.

Hm, I had not thought of the UserType ServiceLoader approach. Thats interesting, though not at all related to the "instance per usage" situation. If you want to get around having an instance-per-usage, use the TypeRegistry: http://docs.jboss.org/hibernate/orm/4.1/manual/en-US/html_single/#types-r...

Configuration is not kept around by Hibernate itself once the SessionFactory is built. Nor is SimpleValue. And as Hardy already mentioned, that code is undergoing major changes already for 5.0.

One more thing: Could we have a property similar to "hibernate.listeners.envers.autoRegister" for Validator? I would like to disable Validator completely as it produces too much garbage (look at the pictures) when the application is deployed.

Hi Emmanuel, > javax.persistence.validation.mode NONE > > should already do that ie disable the integration between JPA and Bean Validation. If that's not the case, there is a bug somewhere but I could not spot it. > > However, Bean Validation has to be activated by the EE container as per the spec. But the "lazy" validator factory should mean that Hibernate Validator is not bootstrapped unless it is used somewhere. I have got it. Adding<property name="javax.persistence.validation.mode" value="NONE"/> to persistence.xml has no effect at all. org.jboss.as.jpa.puparser.PersistenceUnitXmlParser assumes that the validation mode would be defined in the persistence.xml by the tag "validation-mode". This tag has been added in JPA 2.1 which is not yet final. Solution: add<validation-mode>NONE</validation-mode> to persistence.xml, although it violates the persistence_2_0.xsd schema. At least it works for me now. :-)

Best regards, Andrej Golovnin _______________________________________________ hibernate-dev mailing list hibernate-dev(a)lists.jboss.org https://lists.jboss.org/mailman/listinfo/hibernate-dev

Hi Scott, > Validation-mode is part of the JPA 2.0 spec and its not a property. > > Try adding: > > <validation-mode>NONE</validation-mode> you are absolutely right. I think I was blind. I don't understand how could I overlook it. Thanks!

Best regards, Andrej Golovnin

1. Consider avoiding usage of String#toLowerCase() and String#toUpperCase() without specifying a Locale. Users running Hibernate on systems with Turkish as default locale may see unexpected results. In Turkish there are two lowercase letters \u0069 ‘i’ and \u0131 ‘ı’ (dotless ‘I’). And they are totally unrelated. Their uppercase versions are \u0130 ‘İ’ (capital letter ‘I’ with dot above it) and \u0049 ‘I’. So if you convert \u0049 ‘I’ to lower case and the system uses Turkish as default locale, you will get \u0131 ‘ı’ (dotless ‘I’) and not 'i', e.g. 'MY_COLUMN_I' would be converted to 'my_column_ı' and not 'my_column_i'. If you run Hibernate tests with Turkish as default locale some tests fail. I have for now no idea, what locale should be used instead of the default one. :-(

2. Do not use HashMap#clone(). If the JVM is started with the option -XX:+AggressiveOpts, the HashMap#clone() may produce memory leaks. See http://bugs.sun.com/bugdatabase/view_bug.do?bug_id=7042126. IMHO the copy-constructor should be as fast as #clone(). Now to the memory consumption problem. The problem was introduced with the fix of https://hibernate.onjira.com/browse/HHH-4546. Additional LockModes lead to increased memory consumption. When I comment the changes made by HHH-4546 in the method AbstractEntityPersister#createLoaders() out, I get nearly the same size for SessionFactoryImpl as it was in Hibernate 3.2.7 (see http://goo.gl/UB47c).

> 1. Consider avoiding usage of String#toLowerCase() and String#toUpperCase() > without specifying a Locale. Users running Hibernate on systems with Turkish as default > locale may see unexpected results. In Turkish there are two lowercase letters > \u0069 ‘i’ and \u0131 ‘ı’ (dotless ‘I’). And they are totally unrelated. Their uppercase > versions are \u0130 ‘İ’ (capital letter ‘I’ with dot above it) and \u0049 ‘I’. > So if you convert \u0049 ‘I’ to lower case and the system uses Turkish as default > locale, you will get \u0131 ‘ı’ (dotless ‘I’) and not 'i', e.g. 'MY_COLUMN_I' would be converted > to 'my_column_ı' and not 'my_column_i'. If you run Hibernate tests with Turkish as default locale > some tests fail. I have for now no idea, what locale should be used instead of the default > one. :-( I am not sure whether this is a problem in Hibernate as such. A lot of tests are just written without Locale in mind. They just makes assertions based on English. Obviously the tests could be written more carefully in this respect. Or do you think there is an actual bug in the runtime code caused by using the default Locale?

Nice find! Could you drill into one of the LockMode loaders and see what the larger objects referenced by that are?

Well another option is to change the way batch load sql is created/handled which is something that has come up many times before, and someone was just discussing on the list last few weeks. Given a batch size, Hibernate currently generates a non-unintuitive number of loaders. Lets look at an example number. You gave 16 as the batch size. That means Hibernate would generate loaders for sizes: 16,10,9,8,7,6,5,4,3,2,1 The idea being how to efficiently load batches when the number of things to load is less that 16. Deferring initialization of these batch loaders is one option, but that just really delays the memory consumption. Personally, if it were my app I'd rather that sizing be done up front rather than over the course of the next few hours or weeks as the system continues to run. Its been proposed a number of times to instead generate just a single loader for loading that number of items. This would mean generating a single SQL statement that has parameter holders for the full batch size and then somehow "filling out" the empty slots when the number of things to load is less that 16. If this could be made workable across all dialects, I personally think it would be the best approach as it probably gets close to the start up sizings you claimed but would never grow beyond that. But we would need to discuss this to see if it is a real possibility. Lets work an example with batch-size = 3. That would produce SQL like "select a.x, a.y, a.z from a a where a.x = ? or a.x = ? or a.x = ?" So for a case where we have 2 things to batch load, how do we best leverage that single statement? Which really comes down to how we handle the the 3rd parameter binding. I have seen simply reusing one of the keys as a proposal, but that will not work. Null should work, but would it work in all cases against all dialects? On 05/16/2012 03:14 PM, Andrej Golovnin wrote: > Hi Scott, > >> >> Nice find! Could you drill into one of the LockMode loaders and see >> what the larger objects referenced by that are? >> > > Largest objects are of type BatchingEntityLoader. > > The problem has to do with the value of > hibernate.default_batch_fetch_size. > In our case we use 16 as the value. In this case Hibernate creates 11 > EntityLoaders > for every entity class involved in a collection association. So the > most memory > is consumed by generated SQL statements. If I compare memory snapshots, > I see ca. 100MB more of String objects in JBoss 7 as in JBoss 4. > > Changing the value of hibernate.default_batch_fetch_size from 16 to 4 > reduces the size of SessionFactoryImpl in our case from ca. 370MB to > 180MB. > And if I use patched version of Hibernate as suggested by me, the > size of > SessionFactoryImpl is ca. 80MB. So we will now investigate, wether it is > really needed to set hibernate.default_batch_fetch_size to 16 or maybe > the lower value would also give us decent performance. > > Best regards > Andrej Golovnin > _______________________________________________ > hibernate-dev mailing list > hibernate-dev(a)lists.jboss.org > https://lists.jboss.org/mailman/listinfo/hibernate-dev

> Its been proposed a number of times to instead generate just a single > loader for loading that number of items. This would mean generating a > single SQL statement that has parameter holders for the full batch size > and then somehow "filling out" the empty slots when the number of things > to load is less that 16. If this could be made workable across all > dialects, I personally think it would be the best approach as it > probably gets close to the start up sizings you claimed but would never > grow beyond that. +1 if it is possible it sounds like the best way of doing it --hardy _______________________________________________ hibernate-dev mailing list hibernate-dev(a)lists.jboss.org https://lists.jboss.org/mailman/listinfo/hibernate-dev

Deferring initialization of these batch loaders is one option, but that just really delays the memory consumption. Personally, if it were my app I'd rather that sizing be done up front rather than over the course of the next few hours or weeks as the system continues to run.

Its been proposed a number of times to instead generate just a single loader for loading that number of items. This would mean generating a single SQL statement that has parameter holders for the full batch size and then somehow "filling out" the empty slots when the number of things to load is less that 16. If this could be made workable across all dialects, I personally think it would be the best approach as it probably gets close to the start up sizings you claimed but would never grow beyond that. But we would need to discuss this to see if it is a real possibility. Lets work an example with batch-size = 3. That would produce SQL like "select a.x, a.y, a.z from a a where a.x = ? or a.x = ? or a.x = ?"

So for a case where we have 2 things to batch load, how do we best leverage that single statement? Which really comes down to how we handle the the 3rd parameter binding. I have seen simply reusing one of the keys as a proposal, but that will not work. Null should work, but would it work in all cases against all dialects?

Hi Steve, > Deferring initialization of these batch loaders is one option, but that just really delays the memory consumption.  Personally, if it were my app I'd rather that sizing be done up front rather than over the course of the next few hours or weeks as the system continues to run. The question is, if you really need all LockModes for an entity. I personally doubt it.

Hi Steve, > Deferring initialization of these batch loaders is one option, but that just really delays the memory consumption. Personally, if it were my app I'd rather that sizing be done up front rather than over the course of the next few hours or weeks as the system continues to run. The question is, if you really need all LockModes for an entity. I personally doubt it.

> > Its been proposed a number of times to instead generate just a single loader for loading that number of items. This would mean generating a single SQL statement that has parameter holders for the full batch size and then somehow "filling out" the empty slots when the number of things to load is less that 16. If this could be made workable across all dialects, I personally think it would be the best approach as it probably gets close to the start up sizings you claimed but would never grow beyond that. But we would need to discuss this to see if it is a real possibility. Lets work an example with batch-size = 3. That would produce SQL like "select a.x, a.y, a.z from a a where a.x = ? or a.x = ? or a.x = ?" I think you mean "select a.x, a.y, a.z from a a where a.x in (?, ?, ?)". At least this is what Hibernate generates now. The first rule I learned in SQL: avoid "OR" as much as possible in WHERE clause. Query optimizers produce better query execution plans for "IN" as for equivalent "OR"-statements.

The only difference may be in the costs for the query calculated by the optimizer. But the query execution time should be the same. Example from PostgreSQL: Table: CREATE TABLE test ( oid integer NOT NULL, ttt character varying(10), CONSTRAINT test_pkey PRIMARY KEY (oid ) ); Number of rows: 100000 Query: SELECT * FROM test WHERE oid IN (100, 101, 102); Explain output: "Index Scan using test_pkey on public.test (cost=0.00..16.91 rows=3 width=9) (actual time=0.025..0.035 rows=3 loops=1)" " Output: oid, ttt" " Index Cond: (test.oid = ANY ('{100,101,102}'::integer[]))" " Buffers: shared hit=7" Query: SELECT * FROM test WHERE oid IN (100, 101, 102, 100, 100, 100); "Index Scan using test_pkey on public.test (cost=0.00..29.72 rows=6 width=9) (actual time=0.024..0.034 rows=3 loops=1)" " Output: oid, ttt" " Index Cond: (test.oid = ANY ('{100,101,102,100,100,100}'::integer[]))" " Buffers: shared hit=7" Explain output: Query: SELECT * FROM test WHERE oid IN (100, 101, 102, null, null, null); "Index Scan using test_pkey on public.test (cost=0.00..29.69 rows=3 width=9) (actual time=0.023..0.033 rows=3 loops=1)" " Output: oid, ttt" " Index Cond: (test.oid = ANY ('{100,101,102,NULL,NULL,NULL}'::integer[]))" " Buffers: shared hit=7" As you see the difference is only in the calculated costs.