Em 05/12/2012 09:59, ERR ORR escreveu:
Hi Edson,
since you are using 'like' in your select, you may want to try the following (example):
CREATE INDEX "MY_LONG_INDEX_NAME_IDX"
ON "MY_TABLE_NAME"
USING btree
("MY_VARCHAR_FIELD_NAME" COLLATE pg_catalog."default" varchar_pattern_ops);
(for TEXT fields, use text_pattern_ops in the index declaration).
That's it: you did the trick!
Query time dropped from 250ms to 15ms after correct index! It is a huge improvement, I'll start changing all indexes that I expect to be used with "like" operator to use the varchar_pattern_ops.
Thank you very much!
Edson
I declare all my indexes on string fields that way because MOST of my queries are with like/ilike anyway, and I haven't noticed that the indexes would be bigger than without those clauses - I have tables with up to 3M rows.
Next thing, perhaps your index is declared only for a part of the values in the column (partial index)?
Next, as Alan said, check if the index is up-to-date (reindex), if in doubt drop- and recreate it.
I hope that helps.
