Re: Place of subselect

It seems that you are right. By further testing I found that a WHERE condition in the subquery was making the query hundred times slower. As I'm not very familiar with explain analyze, I paste them below. Why do I have "merge join" and "merge cond" in one case and "subplan" in the other case? Note that "u_xref_ug_id" is a reference and therefore b-tree indexed.


vf_cn2fr=# EXPLAIN ANALYZE SELECT ug_id AS id, ug_en AS name, ug_type AS type,
(SELECT count(*) FROM forms_groups JOIN users ON fg_xref_u_id = u_id WHERE u_xref_ug_id = ug_id) as groupes                          
 FROM users_groups ORDER BY "ug_type","ug_en" LIMIT 5;
                                                                                 QUERY PLAN                                                                                
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=513022.74..513022.76 rows=5 width=26) (actual time=31172.258..31172.271 rows=5 loops=1)
   ->  Sort  (cost=513022.74..513023.74 rows=397 width=26) (actual time=31172.255..31172.259 rows=5 loops=1)
         Sort Key: ug_type, ug_en
         ->  Seq Scan on users_groups  (cost=0.00..513005.61 rows=397 width=26) (actual time=83.273..31167.266 rows=397 loops=1)
               SubPlan
                 ->  Aggregate  (cost=1292.18..1292.19 rows=1 width=0) (actual time=78.498..78.499 rows=1 loops=397)
                       ->  Hash Join  (cost=146.18..1290.52 rows=663 width=0) (actual time=30.023..78.389 rows=102 loops=397)
                             Hash Cond: (forms_groups.fg_xref_u_id = users.u_id)
                             ->  Seq Scan on forms_groups  (cost=0.00..985.88 rows=40488 width=4) (actual time=0.005..42.046 rows=40490 loops=372)
                             ->  Hash  (cost=137.10..137.10 rows=726 width=4) (actual time=0.306..0.306 rows=112 loops=397)
                                   ->  Index Scan using users_u_xref_ug_id_idx on users  (cost=0.00..137.10 rows=726 width=4) (actual time=0.013..0.169 rows=112 loops=397)
                                         Index Cond: (u_xref_ug_id = $0)
 Total runtime: 31172.363 ms
(13 rows)

vf_cn2fr=# EXPLAIN ANALYZE SELECT ug_id AS id, ug_en AS name, ug_type AS type,
(SELECT count(*) FROM forms_groups JOIN users ON fg_xref_u_id = u_id) as groupes
 FROM users_groups ORDER BY "ug_type","ug_en" LIMIT 5;
                                                                                 QUERY PLAN                                                                                
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=4530.86..4530.87 rows=5 width=26) (actual time=325.353..325.365 rows=5 loops=1)
   InitPlan
     ->  Aggregate  (cost=4505.74..4505.75 rows=1 width=0) (actual time=320.673..320.674 rows=1 loops=1)
           ->  Merge Join  (cost=0.00..4404.52 rows=40488 width=0) (actual time=0.176..281.602 rows=40490 loops=1)
                 Merge Cond: (forms_groups.fg_xref_u_id = users.u_id)
                 ->  Index Scan using forms_groups_fg_xref_u_id_idx on forms_groups  (cost=0.00..1576.38 rows=40488 width=4) (actual time=0.020..64.556 rows=40490 loops=1)
                 ->  Index Scan using users_pkey on users  (cost=0.00..2212.00 rows=44313 width=4) (actual time=0.015..73.373 rows=47689 loops=1)
   ->  Sort  (cost=25.11..26.10 rows=397 width=26) (actual time=325.350..325.355 rows=5 loops=1)
         Sort Key: ug_type, ug_en
         ->  Seq Scan on users_groups  (cost=0.00..7.97 rows=397 width=26) (actual time=320.693..321.192 rows=397 loops=1)
 Total runtime: 325.457 ms
(11 rows)