Re: Planner estimates cost of 'like' a lot lower than '='??

   From: Tom Lane <tgl@sss.pgh.pa.us>

   Actually, now that I look more closely, I bet that the real failure in
   this example is not in estimation of the find0 scan, but in estimation
   of the find1 scan.  Notice that the plan switches from using
   datavalueindex for find1 (ie, it's keying off "find1.value like
   'test_0'", which means that the indexscan limits are 'test' to 'tesu')
   to using dataindex (since this is an inner indexscan, values are
   available for all three of key0, key1, key2).  Since dataindex is a
   unique index, that means only one row will be fetched from the index,
   as opposed to however many are selected by "where find1.value >= 'test'
   AND find1.value < 'tesu'".

   By eyeball, it seems obvious that the unique-index lookup should be
   preferred.  I am not sure why the planner is selecting the other
   instead, but it probably points to bogus estimation of the LIKE range
   selectivity.  What do you get from both EXPLAIN and actual execution
   of

       select count(*) from data where value like 'test_0';

       select count(*) from data where value >= 'test' and value < 'tesu';

               regards, tom lane



testdb=> select count(*) from data where value like 'test_0';
 count
-------
     9
(1 row)

testdb=> select count(*) from data where value >= 'test' and value < 'tesu';
 count
-------
 10000
(1 row)


This exact query probably isn't what I would see in a production db,
but on the other hand some values searched for will be extremely
common (e.g. 'true' and 'false'), so with this table using dataindex
(for find1) will always be superior to using datavalueindex.

      _
Mats Lofkvist
mal@algonet.se