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@xxxxxxxxxxx> 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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
