Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 3F4D3633EAB for ; Mon, 2 Mar 2009 23:24:00 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 44421-03 for ; Mon, 2 Mar 2009 23:23:55 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by mail.postgresql.org (Postfix) with ESMTP id C32F36337A8 for ; Mon, 2 Mar 2009 23:23:55 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id n233NqY0002871; Mon, 2 Mar 2009 22:23:52 -0500 (EST) To: Aaron Guyon cc: pgsql-performance@postgresql.org Subject: Re: Postgres 8.3, four times slower queries? In-reply-to: References: Comments: In-reply-to Aaron Guyon message dated "Mon, 02 Mar 2009 19:18:30 -0500" Date: Mon, 02 Mar 2009 22:23:52 -0500 Message-ID: <2870.1236050632@sss.pgh.pa.us> From: Tom Lane 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: 200903/22 X-Sequence-Number: 32944 Aaron Guyon writes: > We are currently running postgres 8.2 and are evaluating the upgrade to 8.3. > Some of our tests are indicating that postgresql 8.3 is actually degrading > the performance of some of our queries by a factor of 10 or more. Are you sure you are comparing apples to apples here? Same configure options for the builds, same parameter values in postgresql.conf, both databases ANALYZEd, etc? And are they running on the same hardware? The rowcount estimates seem to be a bit different, which might account for the difference in plan choices, but I'm not convinced that that is the reason for the slowness. The parts of the plans that are exactly comparable show very significant speed differences, eg > -> Index Scan using idx_skin_day_part_id on skin t2 (cost=0.00..6.28 rows=1 width=24) (actual time=2.484..2.486 rows=1 loops=7) > Index Cond: (t2.day_part_id = t10.id) > Filter: (t2.active <> 0::numeric) > -> Index Scan using idx_skin_slot_skin_id on skin_slot t11 (cost=0.00..6.54 rows=92 width=25) (actual time=12.726..276.412 rows=94 loops=4) > Index Cond: (t11.skin_id = t2.id) > Filter: (t11.active <> 0::numeric) > -> Index Scan using idx_skin_day_part_id on skin t2 (cost=0.00..6.28 rows=1 width=30) (actual time=0.028..0.031 rows=1 loops=7) > Index Cond: (t2.day_part_id = t10.id) > Filter: (active <> 0::numeric) > -> Index Scan using idx_skin_slot_skin_id on skin_slot t11 (cost=0.00..6.85 rows=93 width=30) (actual time=0.053..1.382 rows=94 loops=4) > Index Cond: (t2.id = t11.skin_id) > Filter: (active <> 0::numeric) There's nothing in 8.3 vs 8.2 to explain that, if they're configured the same and running in the same environment. regards, tom lane