From: Tom Lane <tgl@sss.pgh.pa.us>
To: Aaron Guyon <battlemage@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Postgres 8.3, four times slower queries?
Date: Tue, 03 Mar 2009 17:34:12 -0500
Message-ID: <17797.1236119652@sss.pgh.pa.us> (raw)
In-Reply-To: <f0afbb9b0903030931g7b219137n2954cf058d523e52@mail.gmail.com>
References: <f0afbb9b0903021618w616f1158t84dbf119f0b72c70@mail.gmail.com>
<2870.1236050632@sss.pgh.pa.us>
<f0afbb9b0903030931g7b219137n2954cf058d523e52@mail.gmail.com>
Aaron Guyon <battlemage@gmail.com> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, battlemage@gmail.com
Subject: Re: Postgres 8.3, four times slower queries?
In-Reply-To: <17797.1236119652@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox