Received: from localhost (unknown [200.46.204.184]) by developer.postgresql.org (Postfix) with ESMTP id 6E3DF2E0059 for ; Fri, 9 May 2008 05:21:42 -0300 (ADT) Received: from developer.postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 11963-09 for ; Fri, 9 May 2008 05:21:35 -0300 (ADT) X-Greylist: delayed 00:11:48.689245 by SQLgrey-1.7.6 Received: from yergi.telenet-ops.be (yergi.telenet-ops.be [195.130.132.36]) by developer.postgresql.org (Postfix) with ESMTP id 384842E0057 for ; Fri, 9 May 2008 05:21:38 -0300 (ADT) Received: from edna.telenet-ops.be (unknown [195.130.132.58]) by yergi.telenet-ops.be (Postfix) with ESMTP id 631765C966A for ; Fri, 9 May 2008 10:10:09 +0200 (CEST) Received: from localhost (localhost.localdomain [127.0.0.1]) by edna.telenet-ops.be (Postfix) with SMTP id E4731E4021 for ; Fri, 9 May 2008 10:09:47 +0200 (CEST) Received: from mercurius.leuven.smartlounge.be (d5152FE02.access.telenet.be [81.82.254.2]) by edna.telenet-ops.be (Postfix) with ESMTP id DB89BE401E for ; Fri, 9 May 2008 10:09:47 +0200 (CEST) Received: from latas.local (unknown [172.16.19.22]) by mercurius.leuven.smartlounge.be (Postfix) with ESMTP id 7E93A681230A for ; Fri, 9 May 2008 10:09:47 +0200 (CEST) From: Frank Dekervel Organization: Smartlounge To: pgsql-performance@postgresql.org Subject: "append" takes a lot of time in a query Date: Fri, 9 May 2008 10:09:46 +0200 User-Agent: KMail/1.9.9 MIME-Version: 1.0 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200805091009.46893.frank.dekervel@smartlounge.be> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200805/137 X-Sequence-Number: 30105 Hello, I have a strange performance problem with postgresql 8.3 (shipped with ubuntu hardy) and a query that seems very simple: explain analyze SELECT * FROM (part LEFT OUTER JOIN part_lang ON part.id = part_lang.id) WHERE part.parent = 49110; query plan here: http://front7.smartlounge.be/~kervel/queryplan.txt the query does not return a single row the table "part" and "part_lang" have a whole lot of tables inheriting them, most of the inheriting tables only contain a few rows. this turns this query in an append of a whole lot of seq scan/ index scan's. These scans are predictably quick, but the "append" takes 5 seconds (and the numbers of the scans do not add up to the append actual time) if i leave out the "outer join" performance is okay: SELECT * FROM part WHERE part.parent = 49110; if i then add a "order by sequence number" the performance is bad again: SELECT * FROM part WHERE part.parent = 49110 order by sequencenumber; I'm a bit stuck with this problem, and i don't know how to continue finding out why. Does someone have an explanation / possible solution for this performance ? We use a similar scheme on a lot of other projects without problems (but this time, the number of tables inheriting from part is a bit bigger). Thanks a lot in advance, greetings, Frank -- ========================= Frank Dekervel frank.dekervel@smartlounge.be ========================= Smartlounge JP Minckelersstraat 78 3000 Leuven phone:+32 16 311 413 fax:+32 16 311 410 mobile:+32 473 943 421 ========================= http://www.smartlounge.be =========================