Re: Postgresql quey planner

"=?ISO-8859-1?Q?William_Leite_Ara=FAjo?=" <william(dot)bh(at)gmail(dot)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