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

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(at)hotmail(dot)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.