Re: 7.3 no longer using indexes for LIKE queries

On Tue, 3 Dec 2002, Matthew Gabeler-Lee wrote:

> I have a database that has a lot of records (~6mil, iirc), with a varchar
> column that often wants to be queried using something like "where acc like
> 'foo%'". There is a B-Tree index on the acc column. In 7.2.3, Postgres
> would use that index to do the queries and things were lightning fast. In
> 7.3, it is refusing to use the index, even if I set enable_seqscan = off,
> meaning that the query that used to take a few msec now takes a few aeons.
> I've run vacuum analyze on the whole database, and it doesn't change
> anything.

Did you perhaps not initdb in "C" locale? I think pg_controldata will
tell you what it was created with. The optimization for converting
anchored likes into an indexable form doesn't work in all locales and is
currently only done in C locale I believe.

> Something I noticed in trying to force the use of an index scan ... setting
> enable_seqscan = off here doesn't change whether it uses a seq scan, but it
> makes it change the cost estimate to '100000000.00..100148503.29'; bit
> weird, that, if you ask me.

That's probably because it doesn't believe that there's an index scan
possible here.