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 по дате отправления: