Обсуждение: Postgresql quey planner

      Estou confuso com o funcionamento do "query planner" do postgresql. Tenho 2 queries conceitualmente idênticas:<br
/>     I'm confused about the planner functionality. I'd 2 queries contextualy indentical:<br /><br /><span
style="font-weight:bold;">SELECT</span> p.pos_id, <span style="color: rgb(0, 102, 0);">count</span>(aut_id) as
pesados<br/><span style="font-weight: bold;">FROM</span> posto p <span style="font-weight: bold;">LEFT OUTER JOIN
</span>pesageme <span style="font-weight: bold;">USING</span>(pos_id)<br /><span style="font-weight:
bold;">WHERE</span>e.pos_id <span style="font-weight: bold;">IS NULL OR </span>(pes_dat_tstam <span style="font-weight:
bold;">BETWEEN</span> <span style="color: rgb(255, 0, 0);">'2006-03-01'</span> <span style="font-weight:
bold;">AND</span><span style="color: rgb(255, 0, 0);">'2006-03-31'</span>)<br /><span style="font-weight: bold;">GROUP
BY</span>p.pos_id <span style="font-weight: bold;">ORDER BY</span> pos_id<br /><br /><br /><span style="font-weight:
bold;">SELECT</span>p.pos_id, <span style="color: rgb(0, 102, 0);">count</span>(pes_id) as autuados<br /><span
style="font-weight:bold;"> FROM </span>posto p <span style="font-weight: bold;">LEFT OUTER JOIN</span> autuacao a <span
style="font-weight:bold;">USING</span>(pos_id)<br /><span style="font-weight: bold;">WHERE </span>a.pos_id <span
style="font-weight:bold;"> IS NULL OR </span>(aut_dat_tstam <span style="font-weight: bold;">BETWEEN</span> <span
style="color:rgb(255, 0, 0);">'2006-03-01'</span> <span style="font-weight: bold;">AND</span> <span style="color:
rgb(255,0, 0);">'2006-03-31' </span>)<br /><span style="font-weight: bold;">GROUP BY </span>p.pos_id <span
style="font-weight:bold;">ORDER BY</span> pos_id<br clear="all" /><br /><br />     A segunda faz realmente o que eu
desejo.Mostra todos os postos, inclusive os que não apresentam resultado. Já a primeira, alguns postos simplesmente não
aparecem.A diferença entre as tabelas é o número de registros. A tabela de "pesagem" possui muito mais registros que a
de"autuacao". Avaliando o planner, ví que realmente trada de forma muito diferente as consultas, mas não entendi porque
oresultado e afetado. Não deveria. A saida do planner é : <br /><br />     The second does really what I desire. Show
allrecords in table "posto", including that's without count in table "autuados". But the first, doesn't. Some elements
ontable "Posto" are ommited. Seeing the planner output, I couldn't understand why he uses "GroupAggregate" on the first
butnot on the second query. The relevant difference on tables is the number of records. "pesagem" has about 22000 but
"autuacao"only 100. Is my queries wrongs? <br /><br />'GroupAggregate  (cost=0.00..1027.66 rows=10 width=8) (actual
time=0.318..30.741rows=6 loops=1)'<br />'  ->  Merge Left Join  (cost=0.00..1027.43 rows=21 width=8) (actual
time=0.289..30.651rows=54 loops=1)'<br /> '        Merge Cond: ("outer".pos_id = "inner".pos_id)'<br />'        Filter:
(("inner".pos_idIS NULL) OR (("inner".pes_dat_tstam >= '2006-03-01'::date) AND ("inner".pes_dat_tstam <=
'2006-03-31'::date)))'<br />'        ->  Index Scan using prk_posto on posto p  (cost=0.00..5.35 rows=10 width=4)
(actualtime=0.064..0.081 rows=10 loops=1)'<br />'        ->  Index Scan using fki_frk_pos_id on pesagem a 
(cost=0.00..801.48rows=12604 width=12) (actual time= 0.059..16.331 rows=12604 loops=1)'<br />'Total runtime: 31.035
ms'<br/><br /><br />'Sort  (cost=8.38..8.41 rows=10 width=8) (actual time=0.557..0.564 rows=10 loops=1)'<br />'  Sort
Key:p.pos_id'<br />'  ->  HashAggregate  (cost= 8.09..8.22 rows=10 width=8) (actual time=0.520..0.532 rows=10
loops=1)'<br/>'        ->  Merge Right Join  (cost=1.27..7.85 rows=49 width=8) (actual time=0.062..0.443 rows=58
loops=1)'<br/>'              Merge Cond: ("outer".pos_id = "inner".pos_id)' <br />'              Filter:
(("outer".pos_idIS NULL) OR (("outer".aut_dat_tstam >= '2006-03-01'::date) AND ("outer".aut_dat_tstam <=
'2006-03-31'::date)))'<br/>'              ->  Index Scan using idx_autuacao_pos_id on autuacao a  (cost= 0.00..4.85
rows=77width=12) (actual time=0.007..0.103 rows=77 loops=1)'<br />'              ->  Sort  (cost=1.27..1.29 rows=10
width=4)(actual time=0.049..0.107 rows=86 loops=1)'<br />'                    Sort Key: p.pos_id '<br
/>'                   ->  Seq Scan on posto p  (cost=0.00..1.10 rows=10 width=4) (actual time=0.005..0.016 rows=10
loops=1)'<br/>'Total runtime: 0.659 ms'<br /><br />      Não consigo entender porquê o primeiro usa no final
"GroupAggregate"mas o segundo não. É erro? <br />      Atenciosamente,<br />-- <br />William Leite Araújo<br /> 

"William Leite Araújo" <william.bh@gmail.com> writes:
>      The second does really what I desire. Show all records in table
> "posto", including that's without count in table "autuados". But the first,
> doesn't. Some elements on table "Posto" are ommited.

What PG version is this?  8.1.0 through 8.1.3 had a logic bug in merge
right joins, which perhaps is your problem, although the commit message
only mentions duplicate rows not missing ones.

2006-03-17 14:38  tgl
* src/: backend/executor/nodeMergejoin.c,test/regress/expected/join.out,
test/regress/expected/join_1.out,test/regress/sql/join.sql(REL8_1_STABLE): Fix bug introduced intomergejoin logic by
performanceimprovement patch of 2005-05-13. When we find that a new inner tuple can't possibly match any outertuple
(becauseit contains a NULL), we can't immediately skip thetuple when we are in NEXTINNER state.  Doing so can lead
toemittingmultiple copies of the tuple in FillInner mode, because wemay rescan the tuple after returning to a previous
markedtuple. Instead, proceed to NEXTOUTER state the same as we used to do. After we've found that there's no need to
returnto the markedposition, we can go to SKIPINNER_ADVANCE state instead of SKIP_TESTwhen the inner tuple is
unmatchable;this preserves the performanceimprovement.  Per bug report from Bruce.  I also made a couple ofcosmetic
coderearrangements and added a regression test for theproblem.
 
        regards, tom lane