Hello all,
I am using PostgreSQL 9.4.1, compiled by Visual C++ build 1800, 64-bit, as downloaded from EnterpriseDB, and is running on my 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, but I 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=8 loops=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.002 rows=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.004 rows=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.002 rows=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.190 rows=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.006 rows=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.208 rows=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) (actual time=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.017 rows=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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
