I have been observing a case where the row count estimation for LIKE 'foo' is
(much) higher than for LIKE 'foo%', the rest of the query being the same.
This is a special case of the estimation for equality being higher than for a
range query that includes the value used in the equality.
I haven't been able to get a copy of the data from the client yet, but
considering the nature of the data and the description of the selectivity
estimation algorithms
(http://www.postgresql.org/docs/8.3/static/row-estimation-examples.html),
this behavior appears to be mathematically plausible. I have been wondering
whether in general the eqsel should try to compare its result with the
estimation of (x >= 'foo' AND x <= 'foo') and use that as a ceiling or
something.
Has anyone else observed something similar?
--
Peter Eisentraut
http://developer.postgresql.org/~petere/