From: Tom Lane <tgl@sss.pgh.pa.us>
To: Scott Marlowe <scott.marlowe@gmail.com>
Cc: Maria.L.Wilson-1@nasa.gov, "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: help speeding up a query in postgres 8.4.5
Date: Wed, 11 May 2011 10:31:36 -0400
Message-ID: <25611.1305124296@sss.pgh.pa.us> (raw)
In-Reply-To: <BANLkTinc8wJx7ARuRK_eYVYUg1CqnQ3OuQ@mail.gmail.com>
References: <4D9B6CBA.2050609@nasa.gov>
<BANLkTinc8wJx7ARuRK_eYVYUg1CqnQ3OuQ@mail.gmail.com>
Scott Marlowe <scott.marlowe@gmail.com> writes:
> On Tue, Apr 5, 2011 at 1:25 PM, Maria L. Wilson
> <Maria.L.Wilson-1@nasa.gov> wrote:
> This bit:
>> left outer join INVENTORY IV on GV.GRANULE_ID = IV.GRANULE_ID, INVSENSOR
>> INVS
> has both an explicit and an implicit join. This can constrain join
> re-ordering in the planner. Can you change it to explicit joins only
> and see if that helps?
Since there's a WHERE constraint on IV, the outer join is going to be
strength-reduced to an inner join (note the lack of any outer joins in
the plan). So that isn't going to matter.
AFAICS this is just plain an expensive query. The two filter
constraints are not very selective, each passing more than a million
rows up to the join. You can't expect to join millions of rows in no
time flat. About all you can do is try to bump up work_mem enough that
the join won't use temp files --- for something like this, that's likely
to require a setting of hundreds of MB. I'm not sure whether Maria is
using a version in which EXPLAIN ANALYZE will show whether a hash join
was batched, but that's what I'd be looking at.
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, scott.marlowe@gmail.com
Subject: Re: help speeding up a query in postgres 8.4.5
In-Reply-To: <25611.1305124296@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