Scott Marlowe <scott.marlowe@xxxxxxxxx> writes: > On Tue, Apr 5, 2011 at 1:25 PM, Maria L. Wilson > <Maria.L.Wilson-1@xxxxxxxx> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
