Slow query with join - Mailing list pgsql-general

Hello all,
I am using PostgreSQL 9.4.1, compiled by Visual C++ build 1800, 64-bit, as downloaded from EnterpriseDB, and is running
onmy dev system under Win 7 64-bit. 
I hope someone can help me with a problem I'm having when joining a view with a table. The view is somewhat involved,
butI can provide the details if necessary 
A query on the table is quick (16 ms):
explain analyze select ir_actor_id from f_intervenant_ref where ir_dos_id = '5226' order by ir_actor_id;

"Sort  (cost=17.28..17.30 rows=8 width=4) (actual time=0.032..0.033 rows=8 loops=1)"
"  Sort Key: ir_actor_id"
"  Sort Method: quicksort  Memory: 25kB"
"  ->  Index Scan using ir_dos_id_idx on f_intervenant_ref  (cost=0.28..17.16 rows=8 width=4) (actual time=0.019..0.024
rows=8loops=1)" 
"        Index Cond: ((ir_dos_id)::text = '5226'::text)"
"Planning time: 0.180 ms"
"Execution time: 0.049 ms"

A query on the view is also quick (31 ms), using the results from the previous query on the table:
explain analyze select * from v_actor where v_actor.actor_id in(77170,77170,77184,77184,77185,77185,77186,77186);
"Nested Loop Left Join  (cost=0.86..385.18 rows=8 width=138) (actual time=2.819..9.652 rows=4 loops=1)"
"  Join Filter: (actor.type = 'physical'::business.actor_type)"
"  ->  Nested Loop Left Join  (cost=0.58..105.16 rows=8 width=114) (actual time=0.015..0.032 rows=4 loops=1)"
"        Join Filter: (actor.type = 'moral'::business.actor_type)"
"        ->  Index Scan using actor_pkey on actor  (cost=0.29..38.66 rows=8 width=78) (actual time=0.010..0.018 rows=4
loops=1)"
"              Index Cond: (id = ANY ('{77170,77170,77184,77184,77185,77185,77186,77186}'::integer[]))"
"              Filter: (deleted IS FALSE)"
"        ->  Index Scan using moral_actor_pkey on moral_actor  (cost=0.28..8.30 rows=1 width=40) (actual
time=0.002..0.002rows=0 loops=4)" 
"              Index Cond: (id = actor.id)"
"  ->  Index Scan using physical_actor_pkey on physical_actor  (cost=0.29..8.30 rows=1 width=28) (actual
time=0.003..0.004rows=1 loops=4)" 
"        Index Cond: (id = actor.id)"
"  SubPlan 1"
"    ->  Limit  (cost=8.30..8.31 rows=1 width=8) (never executed)"
"          ->  Sort  (cost=8.30..8.31 rows=1 width=8) (never executed)"
"                Sort Key: contact.rank"
"                ->  Index Scan using contact_actor_idx on contact  (cost=0.28..8.29 rows=1 width=8) (never executed)"
"                      Index Cond: (actor_id = actor.id)"
"                      Filter: (NOT deleted)"
"  SubPlan 2"
"    ->  Limit  (cost=8.30..8.31 rows=1 width=8) (actual time=0.005..0.005 rows=0 loops=4)"
"          ->  Sort  (cost=8.30..8.31 rows=1 width=8) (actual time=0.004..0.004 rows=0 loops=4)"
"                Sort Key: contact_1.rank"
"                Sort Method: quicksort  Memory: 25kB"
"                ->  Index Scan using contact_actor_idx on contact contact_1  (cost=0.28..8.29 rows=1 width=8) (actual
time=0.002..0.002rows=0 loops=4)" 
"                      Index Cond: (actor_id = actor.id)"
"                      Filter: (NOT deleted)"
"Planning time: 0.721 ms"
"Execution time: 9.759 ms"

However, when I combine the two queries into one, the result set takes 6742 ms:
explain analyze select * from v_actor where v_actor.actor_id in(select ir_actor_id from f_intervenant_ref where
ir_dos_id= '5226' order by ir_actor_id); 
"Merge Semi Join  (cost=71.79..108061.92 rows=8 width=1461) (actual time=7884.994..7927.699 rows=4 loops=1)"
"  Merge Cond: (actor.id = f_intervenant_ref.ir_actor_id)"
"  ->  Merge Left Join  (cost=0.85..554314.28 rows=20670 width=138) (actual time=2.820..7926.001 rows=3072 loops=1)"
"        Merge Cond: (actor.id = physical_actor.id)"
"        Join Filter: (actor.type = 'physical'::business.actor_type)"
"        ->  Merge Left Join  (cost=0.57..1679.17 rows=20670 width=114) (actual time=0.016..4.768 rows=3072 loops=1)"
"              Merge Cond: (actor.id = moral_actor.id)"
"              Join Filter: (actor.type = 'moral'::business.actor_type)"
"              ->  Index Scan using actor_pkey on actor  (cost=0.29..1275.50 rows=20670 width=78) (actual
time=0.008..3.190rows=3072 loops=1)" 
"                    Filter: (deleted IS FALSE)"
"              ->  Index Scan using moral_actor_pkey on moral_actor  (cost=0.28..268.78 rows=5548 width=40) (actual
time=0.006..0.006rows=1 loops=1)" 
"        ->  Index Scan using physical_actor_pkey on physical_actor  (cost=0.29..725.98 rows=15122 width=28) (actual
time=0.003..3.208rows=3072 loops=1)" 
"        SubPlan 1"
"          ->  Limit  (cost=8.30..8.31 rows=1 width=8) (never executed)"
"                ->  Sort  (cost=8.30..8.31 rows=1 width=8) (never executed)"
"                      Sort Key: contact.rank"
"                      ->  Index Scan using contact_actor_idx on contact  (cost=0.28..8.29 rows=1 width=8) (never
executed)"
"                            Index Cond: (actor_id = actor.id)"
"                            Filter: (NOT deleted)"
"        SubPlan 2"
"          ->  Limit  (cost=8.30..8.31 rows=1 width=8) (actual time=0.006..0.006 rows=0 loops=3072)"
"                ->  Sort  (cost=8.30..8.31 rows=1 width=8) (actual time=0.005..0.005 rows=0 loops=3072)"
"                      Sort Key: contact_1.rank"
"                      Sort Method: quicksort  Memory: 25kB"
"                      ->  Index Scan using contact_actor_idx on contact contact_1  (cost=0.28..8.29 rows=1 width=8)
(actualtime=0.003..0.003 rows=0 loops=3072)" 
"                            Index Cond: (actor_id = actor.id)"
"                            Filter: (NOT deleted)"
"  ->  Materialize  (cost=17.28..17.40 rows=8 width=4) (actual time=0.024..0.027 rows=8 loops=1)"
"        ->  Sort  (cost=17.28..17.30 rows=8 width=4) (actual time=0.021..0.022 rows=8 loops=1)"
"              Sort Key: f_intervenant_ref.ir_actor_id"
"              Sort Method: quicksort  Memory: 25kB"
"              ->  Index Scan using ir_dos_id_idx on f_intervenant_ref  (cost=0.28..17.16 rows=8 width=4) (actual
time=0.012..0.017rows=8 loops=1)" 
"                    Index Cond: ((ir_dos_id)::text = '5226'::text)"
"Planning time: 0.820 ms"
"Execution time: 7927.838 ms"

Any suggestions to help me speed this up will be greatly appreciated.



Mark Watson
Service au client - R&D
www.jurisconcept.ca