Re: Any way to Convince postgres to push join clause inside subquery aggregate? - Mailing list pgsql-general

Greg Stark <gsstark@mit.edu> writes:
> slo=> explain
> select *
>  from (select foo_id,count(*) as n from foo_bar group by foo_id) as x
>  join (select * from foo limit 1) as foo using (foo_id)
> ;

Why not put the subselect in the output list, if that's the kind of plan
you want?

regression=# explain select foo.*, (select count(*) from foo_bar where foo_id = foo.foo_id) as n from foo;
                                     QUERY PLAN
------------------------------------------------------------------------------------
 Seq Scan on foo  (cost=0.00..17102.50 rows=1000 width=8)
   SubPlan
     ->  Aggregate  (cost=17.08..17.08 rows=1 width=0)
           ->  Index Scan using foobi on foo_bar  (cost=0.00..17.07 rows=5 width=0)
                 Index Cond: (foo_id = $0)
(5 rows)


            regards, tom lane