can I move sort to first outer join ? - Mailing list pgsql-performance

Hi all,

This sql is taking too long for the size of my tiny db.  Any tips from
this alias?  I tried moving the sort to the first left outer join
(between projects and features tables) using a nested subquery, but
postgres tells me only one column could be returned from a subqueyr.

TIA,

fdo

SELECT projects."id" AS t0_r0, projects."name" AS t0_r1, projects."display_name"
 AS t0_r2, projects."description" AS t0_r3, projects."community_id" AS t0_r4, projects."parent_id" AS t0_r5,
projects."visible" AS t0_r6, projects."created_at" AS t0_r7, projects."updated_at" AS t0_r8, projects."image_path"
 AS t0_r9, projects."with_navigation" AS t0_r10, projects."static_home" AS t0_r11, projects."active" AS t0_r12,
projects."image_id" AS t0_r13, projects."request_message" AS t0_r14, projects."response_message" AS t0_r15,
projects."approval_status" AS t0_r16, projects."approved_by_id" AS t0_r17, projects."owner_id" AS t0_r18,
 project_tags."id" AS t1_r0, project_tags."project_id" AS t1_r1, project_tags."name" AS t1_r2,
project_tags."created_at" AS t1_r3, project_tags."updated_at" AS t1_r4, person_roles."id" AS t2_r0,
person_roles."project_id" AS t2_r1, person_roles."person_id" AS t2_r2, person_roles."role_id" AS t2_r3,
person_roles."authorized" AS t2_r4, person_roles."created_at" AS t2_r5, person_roles."updated_at" AS t2_r6,
person_roles."request_message" AS t2_r7, person_roles."response_message" AS t2_r8, features."id" AS t3_r0,
features."project_id" AS t3_r1, features."name" AS t3_r2, features."display_name" AS t3_r3,
 features."feature_uri" AS t3_r4, features."provisioned" AS t3_r5, features."service_name" AS t3_r6,
 features."created_at" AS t3_r7, features."updated_at" AS t3_r8, features."active" AS t3_r9,
features."description" AS t3_r10, features."type" AS t3_r11, features."forum_topic_count" AS t3_r12,
 features."forum_post_count" AS t3_r13, features."forum_last_post_at" AS t3_r14,
features."forum_last_post_by_id" AS t3_r15, features."wiki_default_page_id" AS t3_r16,
features."wiki_default_page_name" AS t3_r17, features."wiki_format" AS t3_r18,
 features."service_id" AS t3_r19, features."service_type_id" AS t3_r20 FROM projects
 LEFT OUTER JOIN project_tags ON project_tags.project_id = projects.id
LEFT OUTER JOIN person_roles ON person_roles.project_id = projects.id
LEFT OUTER JOIN features ON features.project_id = projects.id
WHERE (projects."visible" = 't') AND projects.id IN (3, 4, 5, 6, 10, 7, 8, 9, 13, 11)
ORDER BY projects.name asc;

                                                              QUERY PLAN
               

---------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=57.17..57.20 rows=12 width=4925) (actual time=147.880..148.325 rows=846 loops=1)
   Sort Key: projects.name
   ->  Hash Left Join  (cost=45.53..56.95 rows=12 width=4925) (actual time=1.374..6.694 rows=846 loops=1)
         Hash Cond: (projects.id = project_tags.project_id)
         ->  Hash Left Join  (cost=22.48..33.48 rows=4 width=4819) (actual time=1.243..3.018 rows=222 loops=1)
               Hash Cond: (projects.id = person_roles.project_id)
               ->  Hash Left Join  (cost=10.90..21.86 rows=4 width=3754) (actual time=1.121..1.702 rows=78 loops=1)
                     Hash Cond: (projects.id = features.project_id)
                     ->  Seq Scan on projects  (cost=0.00..10.90 rows=4 width=1884) (actual time=0.039..0.109 rows=10
loops=1)
                           Filter: (visible AND (id = ANY ('{3,4,5,6,10,7,8,9,13,11}'::integer[])))
                     ->  Hash  (cost=10.40..10.40 rows=40 width=1870) (actual time=1.048..1.048 rows=101 loops=1)
                           ->  Seq Scan on features  (cost=0.00..10.40 rows=40 width=1870) (actual time=0.026..0.464
rows=101loops=1) 
               ->  Hash  (cost=10.70..10.70 rows=70 width=1065) (actual time=0.098..0.098 rows=29 loops=1)
                     ->  Seq Scan on person_roles  (cost=0.00..10.70 rows=70 width=1065) (actual time=0.014..0.037
rows=29loops=1) 
         ->  Hash  (cost=15.80..15.80 rows=580 width=106) (actual time=0.105..0.105 rows=32 loops=1)
               ->  Seq Scan on project_tags  (cost=0.00..15.80 rows=580 width=106) (actual time=0.013..0.036 rows=32
loops=1)
 Total runtime: 149.622 ms
(17 rows)