Re: Another seq scan instead of index question

Nicholas Piper <nick@nickpiper.co.uk> writes:
> On Tue, 07 Aug 2001, Tom Lane wrote:
>> Your real problem is the bogus selectivity estimate.  What version
>> are you running?  If 7.0, see contrib/likeplanning/.  If 7.1, I'd
>> be interested to see what you get from

> I'm on 7.1
> (PostgreSQL 7.1.2 on i686-pc-linux-gnu, compiled by GCC 2.95.4)

Okay ... [ looks at code ... ] oh, I see the problem.  The pattern-match
selectivity code doesn't try to deal with "lower(foo) LIKE ...":

    /*
     * If expression is not var op constant for
     * a simple var of a real relation (no subqueries, for now),
     * then punt and return a default estimate.
     */

This could probably be improved, but I don't have time to think about it
now.  In the meantime you could try knocking DEFAULT_MATCH_SEL down a
little bit.  (It's already been reduced to 0.005 in current sources,
in fact.)  See src/backend/utils/adt/selfuncs.c.

            regards, tom lane