From: bricklen <bricklen@gmail.com>
To: Mark Fenbers <Mark.Fenbers@noaa.gov>
Cc: pgsql-sql@postgresql.org
Subject: Re: Rewrite without correlated subqueries
Date: Thu, 20 Aug 2009 14:16:04 -0700
Message-ID: <33b743250908201416h3ec8b61cp4976ab540c4e7cee@mail.gmail.com> (raw)
In-Reply-To: <4A8DB4D7.6030102@noaa.gov>
References: <33b743250908201332k5e7b0e3fga71a1ec6b44f302a@mail.gmail.com>
<4A8DB4D7.6030102@noaa.gov>
Interesting idea. Preferably this operation could be done in straight SQL in
a single transaction, to fit in with the way our application works, but if
that's not possible I may need to go the temporary table route.
On Thu, Aug 20, 2009 at 1:40 PM, Mark Fenbers <Mark.Fenbers@noaa.gov> wrote:
> Try putting your subqueries into temporary tables, first, inside a BEGIN
> ... COMMIT block. But your subqueries would produce the negative, i.e.,
> everything except where sitescategory.idsites = ps.idsites. Then reference
> these temp tables in your query with inner or outer joins as appropriate.
> Your new query would not include the ... IN ( <list> ) syntax...
>
> Mark
>
>
> bricklen wrote:
>
> Hi All,
>
> I'm having some trouble wrapping my head around the syntax to rewrite a
> query using correlated subqueries, to using outer joins etc.
>
> The query:
>
> SELECT ps.userid,
> SUM( ps.hits ) as numhits
> FROM primarystats AS ps
> INNER JOIN camp ON camp.id = ps.idcamp
> INNER JOIN sites ON sites.id = ps.idsite
> WHERE camp.idcatprimary NOT IN ( SELECT idcategory FROM sitescategory WHERE
> sitescategory.idsites = ps.idsites )
> AND camp.idcatsecondary NOT IN ( SELECT idcategory FROM sitescategory WHERE
> sitescategory.idsites = ps.idsites )
> GROUP BY ps.userid;
>
> Because I am rewriting this query to use Greenplum, I cannot use correlated
> subqueries (they are not currently supported).
>
> Can anyone suggest a version that will garner the same results? I tried
> with OUTER JOINS and some IS NULLs, but I couldn't get it right.
>
> Thanks!
>
> bricklen
>
>
>
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-sql@postgresql.org
Cc: bricklen@gmail.com, Mark.Fenbers@noaa.gov
Subject: Re: Rewrite without correlated subqueries
In-Reply-To: <33b743250908201416h3ec8b61cp4976ab540c4e7cee@mail.gmail.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