Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1UZORE-0006w0-U0 for pgsql-performance@arkaria.postgresql.org; Mon, 06 May 2013 16:38:57 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1UZORD-0006om-Uw for pgsql-performance@arkaria.postgresql.org; Mon, 06 May 2013 16:38:56 +0000 Received: from makus.postgresql.org ([2001:4800:7903:4::125]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1UZORA-0006mQ-Ri for pgsql-performance@postgresql.org; Mon, 06 May 2013 16:38:53 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1UZOR9-0001ZT-07 for pgsql-performance@postgresql.org; Mon, 06 May 2013 16:38:52 +0000 Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.5/8.14.5) with ESMTP id r46Gcmhl001584; Mon, 6 May 2013 12:38:49 -0400 (EDT) From: Tom Lane To: Christoph Berg cc: PostgreSQL Performance Subject: Re: RT3.4 query needed a lot more tuning with 9.2 than it did with 8.1 In-reply-to: <20130430112055.GA27525@msgid.df7cb.de> References: <20130430112055.GA27525@msgid.df7cb.de> Comments: In-reply-to Christoph Berg message dated "Tue, 30 Apr 2013 13:20:55 +0200" Date: Mon, 06 May 2013 12:38:48 -0400 Message-ID: <1583.1367858328@sss.pgh.pa.us> X-Pg-Spam-Score: -3.1 (---) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org Christoph Berg writes: > We recently (finally) migrated an Request Tracker 3.4 database running > on 8.1.19 to 9.2.4. The queries used by rt3.4 are sometimes weird, but > 8.1 coped without too much tuning. The schema looks like this: The newer rowcount estimates are much further away from reality: > Unique (cost=1117.67..1118.46 rows=9 width=1115) (actual time=82.646..85.695 rows=439 loops=1) > Unique (cost=784205.94..796940.08 rows=145533 width=1061) (actual time=9710.683..9713.175 rows=439 loops=1) Has the new DB been analyzed? Maybe you had custom stats targets in the old DB that didn't get copied to the new one? regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance