pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedPostgresql quey planner
2+ messages / 2 participants
[nested] [flat]
* Postgresql quey planner
@ 2006-10-05 19:02 William Leite Araújo <william.bh@gmail.com>
0 siblings, 1 reply; 2+ messages in thread
From: William Leite Araújo @ 2006-10-05 19:02 UTC (permalink / raw)
To: José Carlos <jc4520@gmail.com>; +Cc: pgsql-sql
Estou confuso com o funcionamento do "query planner" do postgresql.
Tenho 2 queries conceitualmente idênticas:
I'm confused about the planner functionality. I'd 2 queries
contextualy indentical:
SELECT p.pos_id, count(aut_id) as pesados
FROM posto p LEFT OUTER JOIN pesagem e USING(pos_id)
WHERE e.pos_id IS NULL OR (pes_dat_tstam BETWEEN '2006-03-01' AND
'2006-03-31')
GROUP BY p.pos_id ORDER BY pos_id
SELECT p.pos_id, count(pes_id) as autuados
FROM posto p LEFT OUTER JOIN autuacao a USING(pos_id)
WHERE a.pos_id IS NULL OR (aut_dat_tstam BETWEEN '2006-03-01' AND
'2006-03-31')
GROUP BY p.pos_id ORDER BY pos_id
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 o resultado e afetado. Não
deveria. A saida do planner é :
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. Seeing the planner
output, I couldn't understand why he uses "GroupAggregate" on the first but
not 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?
'GroupAggregate (cost=0.00..1027.66 rows=10 width=8) (actual time=
0.318..30.741 rows=6 loops=1)'
' -> Merge Left Join (cost=0.00..1027.43 rows=21 width=8) (actual time=
0.289..30.651 rows=54 loops=1)'
' Merge Cond: ("outer".pos_id = "inner".pos_id)'
' Filter: (("inner".pos_id IS NULL) OR (("inner".pes_dat_tstam >=
'2006-03-01'::date) AND ("inner".pes_dat_tstam <= '2006-03-31'::date)))'
' -> Index Scan using prk_posto on posto p (cost=0.00..5.35 rows=10
width=4) (actual time=0.064..0.081 rows=10 loops=1)'
' -> Index Scan using fki_frk_pos_id on pesagem a (cost=
0.00..801.48 rows=12604 width=12) (actual time=0.059..16.331 rows=12604
loops=1)'
'Total runtime: 31.035 ms'
'Sort (cost=8.38..8.41 rows=10 width=8) (actual time=0.557..0.564 rows=10
loops=1)'
' Sort Key: p.pos_id'
' -> HashAggregate (cost=8.09..8.22 rows=10 width=8) (actual time=
0.520..0.532 rows=10 loops=1)'
' -> Merge Right Join (cost=1.27..7.85 rows=49 width=8) (actual
time=0.062..0.443 rows=58 loops=1)'
' Merge Cond: ("outer".pos_id = "inner".pos_id)'
' Filter: (("outer".pos_id IS NULL) OR (("outer".aut_dat_tstam
>= '2006-03-01'::date) AND ("outer".aut_dat_tstam <= '2006-03-31'::date)))'
' -> Index Scan using idx_autuacao_pos_id on autuacao a
(cost=0.00..4.85 rows=77 width=12) (actual time=0.007..0.103 rows=77
loops=1)'
' -> Sort (cost=1.27..1.29 rows=10 width=4) (actual time=
0.049..0.107 rows=86 loops=1)'
' Sort Key: p.pos_id'
' -> Seq Scan on posto p (cost=0.00..1.10 rows=10
width=4) (actual time=0.005..0.016 rows=10 loops=1)'
'Total runtime: 0.659 ms'
Não consigo entender porquê o primeiro usa no final "GroupAggregate"
mas o segundo não. É erro?
Atenciosamente,
--
William Leite Araújo
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Postgresql quey planner
@ 2006-10-05 19:20 Tom Lane <tgl@sss.pgh.pa.us>
parent: William Leite Araújo <william.bh@gmail.com>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2006-10-05 19:20 UTC (permalink / raw)
To: William Leite Araújo <william.bh@gmail.com>; +Cc: José Carlos <jc4520@gmail.com>; pgsql-sql
"=?ISO-8859-1?Q?William_Leite_Ara=FAjo?=" <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 into
mergejoin logic by performance improvement patch of 2005-05-13.
When we find that a new inner tuple can't possibly match any outer
tuple (because it contains a NULL), we can't immediately skip the
tuple when we are in NEXTINNER state. Doing so can lead to
emitting multiple copies of the tuple in FillInner mode, because we
may rescan the tuple after returning to a previous marked tuple.
Instead, proceed to NEXTOUTER state the same as we used to do.
After we've found that there's no need to return to the marked
position, we can go to SKIPINNER_ADVANCE state instead of SKIP_TEST
when the inner tuple is unmatchable; this preserves the performance
improvement. Per bug report from Bruce. I also made a couple of
cosmetic code rearrangements and added a regression test for the
problem.
regards, tom lane
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2006-10-05 19:20 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-10-05 19:02 Postgresql quey planner William Leite Araújo <william.bh@gmail.com>
2006-10-05 19:20 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox