[hibernate-dev] Memory consumption

[Guillaume Smet]

On Thu, May 17, 2012 at 3:02 PM, Steve Ebersole <steve at hibernate.org> wrote:
> But just because you have 100 values in no way indicates how many rows will
> be returned.  And I personally know of no optimizers that make such an
> assumption; its a totally worthless assumption.

That's why I put "simplified" in front of it.

The number of clauses is taken into account to calculate the
estimation of the number of rows returned and depending on the plan
chosen to get the results, this might lead to results you don't expect
and don't want.

See the below example on PostgreSQL (which is far from having the
worst optimizer/planner of the market):
sitra=# explain select id from objettouristique where id=4 or id=4 or
id=4 or id=4 or id=5;
                                        QUERY PLAN
------------------------------------------------------------------------------------------
 Bitmap Heap Scan on objettouristique  (cost=1.80..2.41 rows=5 width=8)
   Recheck Cond: ((id = 4) OR (id = 4) OR (id = 4) OR (id = 4) OR (id = 5))
   ->  BitmapOr  (cost=1.80..1.80 rows=5 width=0)
         ->  Bitmap Index Scan on objettouristique_pkey
(cost=0.00..0.36 rows=1 width=0)
               Index Cond: (id = 4)
         ->  Bitmap Index Scan on objettouristique_pkey
(cost=0.00..0.36 rows=1 width=0)
               Index Cond: (id = 4)
         ->  Bitmap Index Scan on objettouristique_pkey
(cost=0.00..0.36 rows=1 width=0)
               Index Cond: (id = 4)
         ->  Bitmap Index Scan on objettouristique_pkey
(cost=0.00..0.36 rows=1 width=0)
               Index Cond: (id = 4)
         ->  Bitmap Index Scan on objettouristique_pkey
(cost=0.00..0.36 rows=1 width=0)
               Index Cond: (id = 5)

It sure looks stupid but the opinion of the PostgreSQL hackers is that
it's stupid to add several times the same clause. The cost of checking
all the conditions can be high and is wasted most of the time.

-- 
Guillaume