Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id E7B8C9FB32A for ; Thu, 5 Oct 2006 16:21:11 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 75398-05 for ; Thu, 5 Oct 2006 16:21:02 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id B85169FB25C for ; Thu, 5 Oct 2006 16:21:01 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id k95JKxXt000263; Thu, 5 Oct 2006 15:20:59 -0400 (EDT) To: "=?ISO-8859-1?Q?William_Leite_Ara=FAjo?=" cc: "=?ISO-8859-1?Q?Jos=E9_Carlos?=" , pgsql-sql@postgresql.org Subject: Re: Postgresql quey planner In-reply-to: References: Comments: In-reply-to "=?ISO-8859-1?Q?William_Leite_Ara=FAjo?=" message dated "Thu, 05 Oct 2006 16:02:37 -0300" Date: Thu, 05 Oct 2006 15:20:59 -0400 Message-ID: <262.1160076059@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.092 tagged_above=0 required=5 tests=AWL, SPF_HELO_PASS, SPF_PASS X-Spam-Level: X-Archive-Number: 200610/68 X-Sequence-Number: 26457 "=?ISO-8859-1?Q?William_Leite_Ara=FAjo?=" 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