Re: Table with million rows - and PostgreSQL 9.1 is not using the index
| От | ERR ORR |
|---|---|
| Тема | Re: Table with million rows - and PostgreSQL 9.1 is not using the index |
| Дата | |
| Msg-id | CALtFtEK0utJy9MtoWKxaK7LWjNPpBrhrkTia08zS8hKh_YUTxA@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: Table with million rows - and PostgreSQL 9.1 is not using the index (Alan Hodgson <ahodgson@simkin.ca>) |
| Ответы |
Re: Table with million rows - and PostgreSQL 9.1 is not
using the index
|
| Список | pgsql-general |
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).
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@simkin.ca> wrote:
On Wednesday, December 05, 2012 02:44:39 AM Edson Richter wrote:According to that the logradouro table only has 91 rows, which is why it seq-
> 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)
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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
В списке pgsql-general по дате отправления: