Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread 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@xxxxxxxxx> 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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux