Re: is it possible to do this? have a subselect that returns two columns

From: Greg Stark <gsstark(at)mit(dot)edu>
To: darren(at)crystalballinc(dot)com
Cc: Greg Stark <gsstark(at)mit(dot)edu>, pgsql-general(at)postgresql(dot)org
Subject: Re: is it possible to do this? have a subselect that returns two columns
Date: 2003-09-03 18:59:48
Message-ID: 87d6ehd7ff.fsf@stark.dyndns.tv
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


darren(at)crystalballinc(dot)com writes:
>
> On 3 Sep 2003, Greg Stark wrote:
>
> > So I have a query in which some of the select values are subqueries. The
> > subqueries are aggregates so I don't want to turn this into a join, it would
> > become too complex and postgres would have trouble optimizing things.
>
> You could try using it as a dynamic select as shown in the query below.
> This would give you the answer by you would have to have a binding between
> tab and the dynamic table z i believe

What you describe as a "dynamic select" is more precisely a "view" and turns
the query into a join, which is what I explained I didn't want to do.

To give a better idea why I don't want to do it, try using that approach for a
more complex example:

SELECT x,y,z, count(*) as n
(select a,count(*) as b from foo where b.x=tab.x group by a) as (a,b),
(select c,count(g) as d from bar where c.y=tab.y group by c) as (c,d)
FROM tab
GROUP BY x,y,z

The only way to turn that into a join is to do make both views aggregates like
this:

SELECT x,y,z,count(*) as n, a,b,c,d
FROM tab
JOIN (select x,a,count(*) as b from foo group by x) AS foo USING (x)
JOIN (select x,c,count(g) as d from bar group by x) AS bar USING (x)
GROUP BY x,y,z

However as I showed in another thread, postgres will be incapable of using an
index on x to do this join, leading it to have to do a full seq scan of both b
and d and calculate the aggregates on the entire table. That's what I meant by
"it would become too complex and postgres would have trouble optimizing
things"

--
greg

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Bruno BAGUETTE 2003-09-03 19:00:32 How many memory size takes numeric(3,0) ?
Previous Message Ron 2003-09-03 18:49:30 Re: is it possible to do this? have a subselect that returns