>>Atualize para 9.3.5.
Restaurei a base em outro computador que já estava atualizado com 9.3.5
Obs.: Novo computador com debian 7 64bits, 4GB ram
configurei shared_buffers 1GB e effective_cache_size 1GB
>>monitore memória no gerenciador de tarefas observando se há esgotamento
da memória física disponível
resultado do comando free -m: total 3946, used 3459, used -/+ buffers/cache
671
>>Qual é o número de registros na tabela anuncio?
1.600.000 registros
>>ajudaria bastante se tivesse apresentado a estrutura das tabelas
TABLE anuncio
id integer NOT NULL,
id_usuario integer,
codigo_bairro smallint,
tipo tipo_anuncio,
texto text,
inicio timestamp without time zone,
texto_search tsvector
CREATE INDEX idx_gin_texto_search ON anuncio USING gin(texto_search);
TABLE bairro
codigo smallserial NOT NULL,
nome character varying NOT NULL,
codigo_cidade integer
TABLE cidade
codigo_ibge integer NOT NULL,
nome character varying NOT NULL
Obs.: Todas as tabelas possuem índice para chaves estrangeiras.
>>e uma descrição do que pretende com a consulta testada
será uma consulta no texto do anúncio (classificados)
>>eu simplesmente *não* entendi a linha depois do inner join e o porquê do
"offset 0"
já retirei o offset do sql de teste, era apenas para efeito de paginação da
tela,
cada página possui 20 linhas, ao clicar na segunda página faço a mesma
consuta com offset 20
>>Por fim, antes de apresentar qualquer EXPLAIN faça um ANALYZE em todas as
tabelas envolvidas
realizado em anuncio, cidade, bairro
>>Você chegou a testar com um valor maior para estatísticas
alterei conforme solicitado
ALTER TABLE anuncio ALTER COLUMN texto_search SET STATISTICS 500;
Realizei então o EXPLAIN abaixo (obs.: esta não é a primeira consulta, já
possui páginas no buffer):
EXPLAIN (ANALYZE, VERBOSE, BUFFERS)
select tipo, inicio, texto, b.nome as bairro, c.nome as cidade,
ts_rank(texto_search, query) as rank
from anuncio
inner join bairro as b on b.codigo = codigo_bairro
inner join cidade as c on c.codigo_ibge = b.codigo_cidade,
to_tsquery('pt', 'coisa|porta|janela') as query
where texto_search @@ query
order by rank desc
limit 20
"Limit (cost=27908.41..27908.46 rows=20 width=1175) (actual
time=2453.635..2453.641 rows=20 loops=1)"
" Output: anuncio.tipo, anuncio.inicio, anuncio.texto, b.nome, c.nome,
(ts_rank(anuncio.texto_search, query.query))"
" Buffers: shared hit=8 read=150646"
" -> Sort (cost=27908.41..27928.41 rows=8000 width=1175) (actual
time=2453.633..2453.635 rows=20 loops=1)"
" Output: anuncio.tipo, anuncio.inicio, anuncio.texto, b.nome,
c.nome, (ts_rank(anuncio.texto_search, query.query))"
" Sort Key: (ts_rank(anuncio.texto_search, query.query))"
" Sort Method: top-N heapsort Memory: 36kB"
" Buffers: shared hit=8 read=150646"
" -> Hash Join (cost=146.17..27695.53 rows=8000 width=1175)
(actual time=199.979..2229.936 rows=224150 loops=1)"
" Output: anuncio.tipo, anuncio.inicio, anuncio.texto, b.nome,
c.nome, ts_rank(anuncio.texto_search, query.query)"
" Hash Cond: (b.codigo_cidade = c.codigo_ibge)"
" Buffers: shared hit=8 read=150646"
" -> Hash Join (cost=144.68..27564.04 rows=8000 width=1165)
(actual time=199.935..1769.702 rows=224150 loops=1)"
" Output: anuncio.tipo, anuncio.inicio, anuncio.texto,
anuncio.texto_search, b.nome, b.codigo_cidade, query.query"
" Hash Cond: (anuncio.codigo_bairro = b.codigo)"
" Buffers: shared hit=7 read=150646"
" -> Nested Loop (cost=110.00..27419.36 rows=8000
width=1142) (actual time=198.939..1540.259 rows=224150 loops=1)"
" Output: anuncio.tipo, anuncio.inicio,
anuncio.texto, anuncio.texto_search, anuncio.codigo_bairro, query.query"
" Buffers: shared hit=5 read=150639"
" -> Function Scan on query (cost=0.00..0.01
rows=1 width=32) (actual time=0.005..0.007 rows=1 loops=1)"
" Output: query.query"
" Function Call: '( ''cois'' | ''port'' ) |
''janel'''::tsquery"
" -> Bitmap Heap Scan on public.anuncio
(cost=110.00..27339.35 rows=8000 width=1110) (actual
time=198.927..1400.132 rows=224150 loops=1)"
" Output: anuncio.id, anuncio.id_usuario,
anuncio.codigo_bairro, anuncio.tipo, anuncio.texto, anuncio.inicio,
anuncio.texto_search"
" Recheck Cond: (anuncio.texto_search @@
query.query)"
" Buffers: shared hit=5 read=150639"
" -> Bitmap Index Scan on
idx_gin_texto_search (cost=0.00..108.00 rows=8000 width=0) (actual
time=133.814..133.814 rows=224150 loops=1)"
" Index Cond: (anuncio.texto_search @@
query.query)"
" Buffers: shared hit=4 read=178"
" -> Hash (cost=20.41..20.41 rows=1141 width=27)
(actual time=0.979..0.979 rows=1141 loops=1)"
" Output: b.nome, b.codigo, b.codigo_cidade"
" Buckets: 1024 Batches: 1 Memory Usage: 69kB"
" Buffers: shared hit=2 read=7"
" -> Seq Scan on public.bairro b
(cost=0.00..20.41 rows=1141 width=27) (actual time=0.007..0.348 rows=1141
loops=1)"
" Output: b.nome, b.codigo, b.codigo_cidade"
" Buffers: shared hit=2 read=7"
" -> Hash (cost=1.22..1.22 rows=22 width=18) (actual
time=0.023..0.023 rows=22 loops=1)"
" Output: c.nome, c.codigo_ibge"
" Buckets: 1024 Batches: 1 Memory Usage: 2kB"
" Buffers: shared hit=1"
" -> Seq Scan on public.cidade c (cost=0.00..1.22
rows=22 width=18) (actual time=0.004..0.010 rows=22 loops=1)"
" Output: c.nome, c.codigo_ibge"
" Buffers: shared hit=1"
"Total runtime: 2453.723 ms"
aumentando o shared_buffers para 2,5GB melhora o tempo:
"Limit (cost=27908.40..27908.45 rows=20 width=1175) (actual
time=1489.540..1489.547 rows=20 loops=1)"
" Output: anuncio.tipo, anuncio.inicio, anuncio.texto, b.nome, c.nome,
(ts_rank(anuncio.texto_search, query.query))"
" Buffers: shared hit=150654"
" -> Sort (cost=27908.40..27928.40 rows=8000 width=1175) (actual
time=1489.538..1489.542 rows=20 loops=1)"
" Output: anuncio.tipo, anuncio.inicio, anuncio.texto, b.nome,
c.nome, (ts_rank(anuncio.texto_search, query.query))"
" Sort Key: (ts_rank(anuncio.texto_search, query.query))"
" Sort Method: top-N heapsort Memory: 36kB"
" Buffers: shared hit=150654"
" -> Hash Join (cost=146.17..27695.53 rows=8000 width=1175)
(actual time=222.677..1277.727 rows=224150 loops=1)"
" Output: anuncio.tipo, anuncio.inicio, anuncio.texto, b.nome,
c.nome, ts_rank(anuncio.texto_search, query.query)"
" Hash Cond: (b.codigo_cidade = c.codigo_ibge)"
" Buffers: shared hit=150654"
" -> Hash Join (cost=144.67..27564.03 rows=8000 width=1165)
(actual time=222.629..838.208 rows=224150 loops=1)"
" Output: anuncio.tipo, anuncio.inicio, anuncio.texto,
anuncio.texto_search, b.nome, b.codigo_cidade, query.query"
" Hash Cond: (anuncio.codigo_bairro = b.codigo)"
" Buffers: shared hit=150653"
" -> Nested Loop (cost=110.00..27419.36 rows=8000
width=1142) (actual time=221.473..651.057 rows=224150 loops=1)"
" Output: anuncio.tipo, anuncio.inicio,
anuncio.texto, anuncio.texto_search, anuncio.codigo_bairro, query.query"
" Buffers: shared hit=150644"
" -> Function Scan on query (cost=0.00..0.01
rows=1 width=32) (actual time=0.007..0.008 rows=1 loops=1)"
" Output: query.query"
" Function Call: '( ''cois'' | ''port'' ) |
''janel'''::tsquery"
" -> Bitmap Heap Scan on public.anuncio
(cost=110.00..27339.35 rows=8000 width=1110) (actual time=221.455..501.126
rows=224150 loops=1)"
" Output: anuncio.id, anuncio.id_usuario,
anuncio.codigo_bairro, anuncio.tipo, anuncio.texto, anuncio.inicio,
anuncio.texto_search"
" Recheck Cond: (anuncio.texto_search @@
query.query)"
" Buffers: shared hit=150644"
" -> Bitmap Index Scan on
idx_gin_texto_search (cost=0.00..108.00 rows=8000 width=0) (actual
time=147.330..147.330 rows=224150 loops=1)"
" Index Cond: (anuncio.texto_search @@
query.query)"
" Buffers: shared hit=182"
" -> Hash (cost=20.41..20.41 rows=1141 width=27)
(actual time=1.135..1.135 rows=1141 loops=1)"
" Output: b.nome, b.codigo, b.codigo_cidade"
" Buckets: 1024 Batches: 1 Memory Usage: 69kB"
" Buffers: shared hit=9"
" -> Seq Scan on public.bairro b
(cost=0.00..20.41 rows=1141 width=27) (actual time=0.008..0.480 rows=1141
loops=1)"
" Output: b.nome, b.codigo, b.codigo_cidade"
" Buffers: shared hit=9"
" -> Hash (cost=1.22..1.22 rows=22 width=18) (actual
time=0.025..0.025 rows=22 loops=1)"
" Output: c.nome, c.codigo_ibge"
" Buckets: 1024 Batches: 1 Memory Usage: 2kB"
" Buffers: shared hit=1"
" -> Seq Scan on public.cidade c (cost=0.00..1.22
rows=22 width=18) (actual time=0.005..0.011 rows=22 loops=1)"
" Output: c.nome, c.codigo_ibge"
" Buffers: shared hit=1"
"Total runtime: 1489.635 ms"
Me parece que neste caso o shared_buffers do tamanho da base de dados tem
melhor performance.
Atenciosamente,
Alessandro Lima
email [email protected]
_______________________________________________
pgbr-geral mailing list
[email protected]
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
