pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: masterchief <esimon@theiqgroup.com>
To: pgsql-performance@postgresql.org
Subject: Re: hashed subplan 5000x slower than two sequential operations
Date: Tue, 18 Jan 2011 10:56:59 -0800 (PST)
Message-ID: <1295377019385-3346652.post@n5.nabble.com> (raw)
In-Reply-To: <4CFFEBB5.8090401@obviously.com>
References: <4CFFD446.6040609@obviously.com>
	<E6DB850FDAD49A459E3C217442489C9225FB9256AB@HOV-MAIL.hovservices.com>
	<C4DAC901169B624F933534A26ED7DF3103E915FA@JENMAIL01.ad.intershop.net>
	<C4DAC901169B624F933534A26ED7DF3103E915FC@JENMAIL01.ad.intershop.net>
	<4CFFEBB5.8090401@obviously.com>


> Tom Lane wrote:
>
> The only really effective way the planner knows to optimize an
> "IN (sub-SELECT)" is to turn it into a semi-join, which is not possible
> here because of the unrelated OR clause.  You might consider replacing
> this with a UNION of two scans of "contexts".  (And yes, I know it'd be
> nicer if the planner did that for you.)

In moving our application from Oracle to Postgres, we've discovered that a
large number of our reports fall into this category.  If we rewrite them as
a UNION of two scans, it would be quite a big undertaking.  Is there a way
to tell the planner explicitly to use a semi-join (I may not grasp the
concepts here)?  If not, would your advice be to hunker down and rewrite the
queries?
-- 
View this message in context: http://postgresql.1045698.n5.nabble.com/hashed-subplan-5000x-slower-than-two-sequential-operations-t...
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.



view thread (11+ messages)  latest in thread

Message-ID: <1295377019385-3346652.post@n5.nabble.com>
Permalink:  ../1295377019385-3346652.post@n5.nabble.com/
Also on:    postgresql.org/message-id/1295377019385-3346652.post@n5.nabble.com

 · 

reply
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: esimon@theiqgroup.com
  Subject: Re: hashed subplan 5000x slower than two sequential operations
  In-Reply-To: <1295377019385-3346652.post@n5.nabble.com>

* 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