Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 6691E632291 for ; Mon, 30 Aug 2010 03:39:28 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 18573-03 for ; Mon, 30 Aug 2010 06:39:20 +0000 (UTC) X-Greylist: delayed 00:18:30.076057 by SQLgrey-1.7.6 Received: from fep23.mx.upcmail.net (fep23.mx.upcmail.net [62.179.121.43]) by mail.postgresql.org (Postfix) with ESMTP id AE7736321B4 for ; Mon, 30 Aug 2010 03:39:20 -0300 (ADT) Received: from edge01.upcmail.net ([192.168.13.236]) by viefep20-int.chello.at (InterMail vM.8.01.02.02 201-2260-120-106-20100312) with ESMTP id <20100830062048.LDS1378.viefep20-int.chello.at@edge01.upcmail.net> for ; Mon, 30 Aug 2010 08:20:48 +0200 Received: from wiesinger.com ([84.112.167.138]) by edge01.upcmail.net with edge id 0WLm1f04v2zVakc01WLofz; Mon, 30 Aug 2010 08:20:48 +0200 X-SourceIP: 84.112.167.138 Received: from bbs.intern (localhost [127.0.0.1]) by wiesinger.com (8.14.4/8.14.4) with ESMTP id o7U6K5UM007188 for ; Mon, 30 Aug 2010 08:20:05 +0200 Received: from localhost (gerhard@localhost) by bbs.intern (8.14.4/8.14.4/Submit) with ESMTP id o7U6K5US007183 for ; Mon, 30 Aug 2010 08:20:05 +0200 Date: Mon, 30 Aug 2010 08:20:05 +0200 (CEST) From: Gerhard Wiesinger To: pgsql-performance@postgresql.org Subject: Major performance problem after upgrade from 8.3 to 8.4 Message-ID: User-Agent: Alpine 2.01 (LFD 1266 2009-07-14) MIME-Version: 1.0 Content-Type: TEXT/PLAIN; format=flowed; charset=US-ASCII X-MailScanner-Information-wiesinger-com: Please contact the ISP for more information X-wiesinger-com-MailScanner-ID: o7U6K5UM007188 X-MailScanner-wiesinger-com: Found to be clean X-MailScanner-SpamCheck-wiesinger-com: not spam, SpamAssassin (not cached, score=-4.399, required 4.5, autolearn=not spam, ALL_TRUSTED -1.80, BAYES_00 -2.60) X-MailScanner-From: lists@wiesinger.com X-Cloudmark-Analysis: v=1.1 cv=/bZgxt9lcTTiCogXEfKVRpwqFqbGXHB0knonYiER/Vo= c=1 sm=0 a=VqvA1ACkJl4A:10 a=ORa4HqFjfvEA:10 a=kj9zAlcOel0A:10 a=-ProOGmuAAAA:8 a=Oo3URmxqmTfmtUOyYy8A:9 a=gvFoKnko_hnsehvRQcWnG2YuIE4A:4 a=CjuIK1q_8ugA:10 a=HpAAvcLHHh0Zw7uRqdWCyQ==:117 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9, RCVD_IN_DNSWL_NONE=-0.0001 X-Spam-Level: X-Archive-Number: 201008/308 X-Sequence-Number: 40151 Hello, I just upgraded with pg_dump/restore from PostgreSQL 8.3.11 to 8.4.4 but I'm having major performance problems with a query with many left joins. Problem is that costs are now very, very, very high (was ok in 8.3). Analyze has been done. Indexes are of course there. -> Merge Left Join (cost=1750660.22..4273805884876845789194861338991916289697885665127154313046252183850255795798561612107149662486528.00 rows=238233578115856634454073334945297075430094545596765511255148896328828230572227215727052643001958400 width=16) Merge Cond: (l.id = d2000903.fk_id) Details with execution plan can be found at: http://www.wiesinger.com/tmp/pg_perf_84.txt I know that the data model is key/value pairs but it worked well in 8.3. I need this flexibility. Any ideas? Thnx. Ciao, Gerhard -- http://www.wiesinger.com/