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

Поиск
Список
Период
Сортировка
От Edson Richter
Тема Table with million rows - and PostgreSQL 9.1 is not using the index
Дата
Msg-id BLU0-SMTP5671F943383A36BEE52B2CCF460@phx.gbl
обсуждение исходный текст
Ответы Re: Table with million rows - and PostgreSQL 9.1 is not using the index
Re: Table with million rows - and PostgreSQL 9.1 is not using the index
Список pgsql-general
I've a table with >1100000 rows, with streets.
I'm making a partial search using zip code, and PostgreSQL is ignoring
my ZIP index.
I'm sure I'm making some mistake, but I can't see where.
The query is:

SELECT t2.ID, t2.CEP, t2.COMPLEMENTO, t2.NOME, t2.NOMESEMACENTOS,
t2.TIPO, t2.BAIRRO_ID
   FROM LOCALIDADE t0, LOGRADOURO t2, BAIRRO t1
  WHERE t2.CEP LIKE '81630160%' AND ((t1.ID = t2.BAIRRO_ID) AND (t0.ID =
t1.LOCALIDADE_ID)) ORDER BY t0.NOME;

(for reference, BAIRRO = town, LOCALIDADE = city, LOGRADOURO = street)

Here is the result of explain analyze:

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)
               ->  Index Scan using pkbairro on bairro t1
(cost=0.00..3.30 rows=1 width=8)
                     Index Cond: (id = t2.bairro_id)
         ->  Index Scan using pklocalidade on localidade t0
(cost=0.00..0.03 rows=1 width=16)
               Index Cond: ((id)::text = (t1.localidade_id)::text)

I've few tweaks in postgresql.conf:
shared_buffers = 2GB
temp_buffers = 32MB
max_prepared_transactions = 50
work_mem = 32MB
maintenance_work_mem = 16MB
max_stack_depth = 4MB
max_files_per_process = 15000
random_page_cost = 2.0
cpu_tuple_cost = 0.001
cpu_index_tuple_cost = 0.0005
cpu_operator_cost = 0.00025
effective_cache_size = 512MB

Everything else is default configuration.

This machine is Intel Quad 3.1Ghz, with 8 threads, 8Gig of RAM, 8Gig of
Swap, running CentOS 6.3 64bit.
Machine is free almost all the time.

Thanks for your advice,

Edson


В списке pgsql-general по дате отправления:

Предыдущее
От: daniel
Дата:
Сообщение: Re: ts_headline and query with hyphen
Следующее
От: Lonni J Friedman
Дата:
Сообщение: Re: Table with million rows - and PostgreSQL 9.1 is not using the index