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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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(at)algonet(dot)se