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.
# EXPLAIN ANALYZE select * from (select distinct on (b_id) * from a order by b_id, id) sub left join b on b.id = sub.b_id;
                                                   QUERY PLAN
----------------------------------------------------------------------------------------------------------------- Hash Left Join (cost=187.45..243.70 rows=1230 width=44) (actual time=0.000..0.000 rows=3 loops=1)
[...]
(11 rows)

# EXPLAIN ANALYZE SELECT DISTINCT ON (a.b_id) * FROM a LEFT JOIN b ON b.id = a.b_id ORDER BY a.b_id, a.id;
                                                       QUERY PLAN
------------------------------------------------------------------------------------------------------------------------ Unique (cost=1339.24..1405.05 rows=200 width=44) (actual time=0.000..0.000 rows=3 loops=1)
[...]
(15 rows)

mage=# EXPLAIN ANALYZE select * from (select distinct on (b_id) * from a order by b_id, id) sub left join b on b.id = sub.b_id order by b.id;
                                                      QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Sort (cost=306.83..309.90 rows=1230 width=44) (actual time=0.000..0.000 rows=3 loops=1)


The subquery seems to be better choice even with double ordering. But is the second order required?

        Mage

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Reply via email to