Re: intercepting where clause on a view or other performance tweak

[Tom Lane]

Russell Keane <Russel...@inps.co.uk> writes:
> Running the following query takes 56+ ms as it does a seq scan of the whole table:
> SELECT CODE FROM stuff
> WHERE SEARCH_KEY LIKE 'AAAAAA%'

Why don't you create an index on search_key, and forget all these other
machinations? (If your locale isn't C you'll need to use a
varchar_pattern_ops index.)

regards, tom lane


--
Sent via pgsql-performance mailing list (pgsql-pe...@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

[Russell Keane]

Sorry, I should've added that in the original description.
I have an index on search_key and it's never used.

If it makes any difference, the table is about 9MB and the index on that field alone is 3MB.

[Russell Keane]

I should've also mentioned that we're using PG 9.0.

[Tom Lane]

Russell Keane <Russel...@inps.co.uk> writes:
> Sorry, I should've added that in the original description.
> I have an index on search_key and it's never used.

Did you pay attention to the point about the nondefault operator class?
If the LIKE pattern is left-anchored and as selective as your example
implies, the planner certainly ought to try to use a compatible index.