pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRewrite without correlated subqueries
5+ messages / 3 participants
[nested] [flat]
* Rewrite without correlated subqueries
@ 2009-08-20 20:32 bricklen <bricklen@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: bricklen @ 2009-08-20 20:32 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Rewrite without correlated subqueries
@ 2009-08-20 20:40 Mark Fenbers <Mark.Fenbers@noaa.gov>
parent: bricklen <bricklen@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Mark Fenbers @ 2009-08-20 20:40 UTC (permalink / raw)
To: bricklen <bricklen@gmail.com>; +Cc: pgsql-sql
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
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...<br>
<br>
Mark<br>
<br>
bricklen wrote:
<blockquote
cite="mid:33b743250908201332k5e7b0e3fga71a1ec6b44f302a@mail.gmail.com"
type="cite">Hi All,<br>
<br>
I'm having some trouble wrapping my head around the syntax to rewrite a
query using correlated subqueries, to using outer joins etc.<br>
<br>
The query:<br>
<br>
SELECT ps.userid,<br>
SUM( ps.hits ) as numhits<br>
FROM primarystats AS ps<br>
INNER JOIN camp ON <a moz-do-not-send="true" href="http://camp.id";
target="_blank">camp.id</a> = ps.idcamp<br>
INNER JOIN sites ON <a moz-do-not-send="true" href="http://sites.id";
target="_blank">sites.id</a> = ps.idsite<br>
WHERE camp.idcatprimary NOT IN ( SELECT idcategory FROM sitescategory
WHERE sitescategory.idsites = ps.idsites )<br>
AND camp.idcatsecondary NOT IN ( SELECT idcategory FROM sitescategory
WHERE sitescategory.idsites = ps.idsites )<br>
GROUP BY ps.userid;<br>
<br>
Because I am rewriting this query to use Greenplum, I cannot use
correlated subqueries (they are not currently supported).<br>
<br>
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.<br>
<br>
Thanks!<br>
<br>
bricklen<br>
<br>
<br>
</blockquote>
</body>
</html>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Rewrite without correlated subqueries
@ 2009-08-20 21:16 bricklen <bricklen@gmail.com>
parent: Mark Fenbers <Mark.Fenbers@noaa.gov>
0 siblings, 1 reply; 5+ messages in thread
From: bricklen @ 2009-08-20 21:16 UTC (permalink / raw)
To: Mark Fenbers <Mark.Fenbers@noaa.gov>; +Cc: pgsql-sql
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
>
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Rewrite without correlated subqueries
@ 2009-08-20 21:59 Scott Marlowe <scott.marlowe@gmail.com>
parent: bricklen <bricklen@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Scott Marlowe @ 2009-08-20 21:59 UTC (permalink / raw)
To: bricklen <bricklen@gmail.com>; +Cc: Mark Fenbers <Mark.Fenbers@noaa.gov>; pgsql-sql
On Thu, Aug 20, 2009 at 3:16 PM, bricklen<bricklen@gmail.com> wrote:
> 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.
Temp tables can be included in a transaction, and they're not visible
to other connections.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Rewrite without correlated subqueries
@ 2009-08-20 22:03 bricklen <bricklen@gmail.com>
parent: Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: bricklen @ 2009-08-20 22:03 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: pgsql-sql
On Thu, Aug 20, 2009 at 2:59 PM, Scott Marlowe <scott.marlowe@gmail.com>wrote:
> On Thu, Aug 20, 2009 at 3:16 PM, bricklen<bricklen@gmail.com> wrote:
> > 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.
>
> Temp tables can be included in a transaction, and they're not visible
> to other connections.
>
Yeah I know, but I was thinking more of replacing this query with vanilla
SQL. Maybe that's just not be feasible.
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2009-08-20 22:03 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-08-20 20:32 Rewrite without correlated subqueries bricklen <bricklen@gmail.com>
2009-08-20 20:40 ` Mark Fenbers <Mark.Fenbers@noaa.gov>
2009-08-20 21:16 ` bricklen <bricklen@gmail.com>
2009-08-20 21:59 ` Scott Marlowe <scott.marlowe@gmail.com>
2009-08-20 22:03 ` bricklen <bricklen@gmail.com>
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