Re: Table with million rows - and PostgreSQL 9.1 is not using the index

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.
>
>
> On 5 December 2012 06:02, Alan Hodgson <ahodgson(at)simkin(dot)ca
> <mailto:ahodgson(at)simkin(dot)ca>> wrote:
>
> On Wednesday, December 05, 2012 02:44:39 AM Edson Richter wrote:
> > Sort (cost=11938.72..11938.74 rows=91 width=93)
> > Sort Key: t0.nome
> > -> Nested Loop (cost=0.00..11938.42 rows=91 width=93)
> > -> Nested Loop (cost=0.00..11935.19 rows=91 width=85)
> > -> Seq Scan on logradouro t2 (cost=0.00..11634.42
> > rows=91 width=81)
> > Filter: ((cep)::text ~~ '81630160%'::text)
>
> According to that the logradouro table only has 91 rows, which is
> why it seq-
> scanned it. Has it been analyzed?
>
> Also, partial text matches require a special index declaration, as
> I recall.
> Maybe post a \d of each table to help troubleshoot this.
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org
> <mailto:pgsql-general(at)postgresql(dot)org>)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
>