From: Mark Fenbers <Mark.Fenbers@noaa.gov>
To: bricklen <bricklen@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Rewrite without correlated subqueries
Date: Thu, 20 Aug 2009 16:40:55 -0400
Message-ID: <4A8DB4D7.6030102@noaa.gov> (raw)
In-Reply-To: <33b743250908201332k5e7b0e3fga71a1ec6b44f302a@mail.gmail.com>
References: <33b743250908201332k5e7b0e3fga71a1ec6b44f302a@mail.gmail.com>
<!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>
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: Mark.Fenbers@noaa.gov, bricklen@gmail.com
Subject: Re: Rewrite without correlated subqueries
In-Reply-To: <4A8DB4D7.6030102@noaa.gov>
* 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