From: | Mage <mage(at)mage(dot)hu> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: subquery join order by |
Date: | 2010-11-19 21:50:24 |
Message-ID: | 4CE6F120.3080500@mage.hu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 11/19/2010 03:21 AM, Thom Brown wrote:
>
> You should always use ORDER BY on the outer-most part of the query
> since that's what will be finally returning your data. Don't bother
> with ordering sub-selects.
I definiatelly have to use the "order by" inside for two reasons.
When "distinct on (x)" is used then x must be in the first column in the
order by part.
The second column in the order by decides which records will I include
in the join so it is very important to use it for ordering.
> So in your case, just use:
>
> SELECT *
> FROM (SELECT DISTINCT ON (b_id) * FROM a) sub
> LEFT JOIN b ON b.id = sub.b_id
> ORDER BY sub.b_id, sub.id;
select distinct on (id) * from b order by name;
ERROR: SELECT DISTINCT ON expressions must match initial ORDER BY
expressions
> But why bother with a sub-select anyway? You can write it as:
>
> SELECT DISTINCT ON (a.b_id) *
> FROM a
> LEFT JOIN b ON b.id = a.b_id
> ORDER BY a.b_id, a.id;
I considered this, however the subquery is generated by an ORM. I wanted
to separate it.
Also the whole join affects many rows. I thought it's cheaper to
preselect them inside the subquery then do the join. I am not sure.
Explain analyze is my good friend but in this case I prefer to ask.
Mage
From | Date | Subject | |
---|---|---|---|
Next Message | Mage | 2010-11-19 22:00:47 | Re: subquery join order by |
Previous Message | Vick Khera | 2010-11-19 21:10:33 | Re: limits of constraint exclusion |