From: | Marco Aurélio V(dot) da Silva <marcoprodata(at)gmail(dot)com> |
---|---|
To: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Problem with collector statistic |
Date: | 2007-12-19 17:43:26 |
Message-ID: | 010e01c84266$ae0eebd0$0402a8c0@marquinho |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Dears,
Running a query on 2 database-equal, with the same indices, but with
slightly different data, I have a very different result of performance. In
one the index is used to make the join, in another it is seqscan.
The next consultation with explain.
Select a.CodPagador,b.Descricao
>From Frete01 a Left Outer Join Frete02 b On (a.CodPagador = b.CodCliente)
explain in database bad:
"Merge Left Join (cost=46619.72..48113.50 rows=58995 width=39) (actual
time=7435.922..8359.063 rows=59161 loops=1)"
" Merge Cond: ("outer".codpagador = "inner".codcliente)"
" -> Sort (cost=24649.80..24797.29 rows=58995 width=11) (actual
time=4063.517..4171.374 rows=59161 loops=1)"
" Sort Key: a.codpagador"
" -> Seq Scan on frete01 a (cost=0.00..19974.95 rows=58995
width=11) (actual time=9.007..3223.000 rows=59161 loops=1)"
" -> Sort (cost=21969.91..22274.37 rows=121784 width=39) (actual
time=3372.375..3674.563 rows=169818 loops=1)"
" Sort Key: b.codcliente"
" -> Seq Scan on frete02 b (cost=0.00..8127.84 rows=121784
width=39) (actual time=17.330..1608.599 rows=121740 loops=1)"
"Total runtime: 8448.797 ms"
explain in good dabatase:
"Limit (cost=0.00..46856.62 rows=55204 width=39) (actual
time=11.205..536.761 rows=55204 loops=1)"
" -> Nested Loop Left Join (cost=0.00..295237.26 rows=55204 width=39)
(actual time=11.201..508.936 rows=55204 loops=1)"
" -> Seq Scan on frete01 a (cost=0.00..19974.95 rows=55204
width=11) (actual time=11.057..220.711 rows=55204 loops=1)"
" -> Index Scan using frete02_f021 on frete02 b (cost=0.00..4.65
rows=1 width=39) (actual time=0.021..0.024 rows=1 loops=9363)"
" Index Cond: ("outer".codpagador = b.codcliente)"
"Total runtime: 551.331 ms"
Thanks to advance for your´s attention,
Marco Aurélio V. da Silva
Prodata Inf. e Cad. Ltda.
MSN: marco(at)prodatanet(dot)com(dot)br
Fone: (33) 3322-3082
From | Date | Subject | |
---|---|---|---|
Next Message | Josh Harrison | 2007-12-19 17:46:15 | pgpool and slony |
Previous Message | Magnus Hagander | 2007-12-19 17:39:11 | Re: Deploy postgres - upgrade strategy |