Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 161E4635127 for ; Tue, 3 Mar 2009 18:34:21 -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 63907-03 for ; Tue, 3 Mar 2009 18:34:17 -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 6BF82635123 for ; Tue, 3 Mar 2009 18:34:17 -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 n23MYCGv017798; Tue, 3 Mar 2009 17:34:12 -0500 (EST) To: Aaron Guyon cc: pgsql-performance@postgresql.org Subject: Re: Postgres 8.3, four times slower queries? In-reply-to: References: <2870.1236050632@sss.pgh.pa.us> Comments: In-reply-to Aaron Guyon message dated "Tue, 03 Mar 2009 12:31:04 -0500" Date: Tue, 03 Mar 2009 17:34:12 -0500 Message-ID: <17797.1236119652@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/37 X-Sequence-Number: 32959 Aaron Guyon writes: > I find it telling that the query plan differs so much between postgres 8.2. Well, you haven't shown us either the query or the table definitions, so we're just guessing in the dark. However, the occurrences of "::numeric" in the query plan make me wonder whether all of your join keys are numeric type. If so, the reason 8.2 didn't use any hash joins is that it couldn't --- it didn't have a hash method for numerics. 8.3 does and therefore has more flexibility of plan choice. Comparisons on numerics aren't terribly fast though (in either release). I wonder whether you could change the key columns to int or bigint. I also find it a tad fishy that both releases are choosing *exactly* the same join order when there is hardly anything else that is identical about the plans --- given the cross-release variance in rowcount estimates etc I'd have expected at least one difference. Are you doing something to force the join order, like running with a small join_collapse_limit setting? If so maybe you shouldn't. regards, tom lane