I'm no expert on this, but it will likely be more helpful to others if
you include the table description with all the indices.
On Tue, Dec 4, 2012 at 8:44 PM, Edson Richter <edsonrichter@hotmail.com> wrote:
> 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.