Re: performance advice needed: join vs explicit subselect

On Tue, Jan 27, 2009 at 04:42:02PM -0500, Tom Lane wrote:

> Karsten Hilbert <Karsten(dot)Hilbert(at)gmx(dot)net> writes:
> > The view in question is in fact a lot more complicated. This
> > is the best I've been able to come up with so far (and it is
> > still slow - slow as in 3-4 seconds for 20 records out of
> > (currently only) 50 !):
>
> What does EXPLAIN ANALYZE say about it? Also, what is the use-case
> you are concerned about --- selecting the whole view contents, or
> selecting WHERE something-or-other?

The query that's run by my application (wiki.gnumed.de) is

select *, xmin_test_result from clin.v_test_results
where pk_patient = 138 <--- this is a variable
order by clin_when desc, pk_episode, unified_name
;

the explain analyze of which is (I've actually gotten it to
work better in the meantime as you can see):

SET
BEGIN
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=8512.91..8512.92 rows=1 width=721) (actual time=2039.771..2039.787 rows=14 loops=1)
Sort Key: tr.clin_when, tr.fk_episode, (COALESCE(ttu.name, tt1.name))
Sort Method: quicksort Memory: 22kB
InitPlan
-> Seq Scan on staff (cost=0.00..1.07 rows=1 width=4) (actual time=0.014..0.014 rows=0 loops=1)
Filter: (db_user = "current_user"())
-> Seq Scan on staff (cost=0.00..1.07 rows=1 width=4) (actual time=0.014..0.014 rows=0 loops=1)
Filter: (db_user = "current_user"())
-> Nested Loop Left Join (cost=3.29..8510.75 rows=1 width=721) (actual time=145.824..2039.427 rows=14 loops=1)
-> Nested Loop Left Join (cost=3.29..27.82 rows=1 width=671) (actual time=4.230..5.298 rows=14 loops=1)
-> Nested Loop Left Join (cost=3.29..23.66 rows=1 width=646) (actual time=4.209..5.061 rows=14 loops=1)
Join Filter: (tt1.pk = ltt2ut.fk_test_type)
-> Nested Loop (cost=2.20..21.42 rows=1 width=565) (actual time=4.089..4.444 rows=14 loops=1)
-> Merge Join (cost=2.20..20.79 rows=1 width=469) (actual time=4.069..4.201 rows=14 loops=1)
Merge Cond: (cenc.pk = tr.fk_encounter)
-> Index Scan using encounter_pkey on encounter cenc (cost=0.00..294.43 rows=16 width=8) (actual time=1.470..3.691 rows=29 loops=1)
Filter: (fk_patient = 138)
-> Sort (cost=2.20..2.29 rows=34 width=465) (actual time=0.279..0.330 rows=34 loops=1)
Sort Key: tr.fk_encounter
Sort Method: quicksort Memory: 25kB
-> Seq Scan on test_result tr (cost=0.00..1.34 rows=34 width=465) (actual time=0.027..0.141 rows=34 loops=1)
-> Index Scan using test_type_pkey on test_type tt1 (cost=0.00..0.62 rows=1 width=96) (actual time=0.007..0.009 rows=1 loops=14)
Index Cond: (tt1.pk = tr.fk_type)
-> Hash Join (cost=1.09..2.19 rows=4 width=89) (actual time=0.012..0.031 rows=4 loops=14)
Hash Cond: (ttu.pk = ltt2ut.fk_test_type_unified)
-> Seq Scan on test_type_unified ttu (cost=0.00..1.04 rows=4 width=85) (actual time=0.003..0.008 rows=4 loops=14)
-> Hash (cost=1.04..1.04 rows=4 width=8) (actual time=0.023..0.023 rows=4 loops=1)
-> Seq Scan on lnk_ttype2unified_type ltt2ut (cost=0.00..1.04 rows=4 width=8) (actual time=0.006..0.013 rows=4 loops=1)
-> Index Scan using episode_pkey on episode epi (cost=0.00..4.15 rows=1 width=29) (actual time=0.009..0.011 rows=1 loops=14)
Index Cond: (tr.fk_episode = epi.pk)
-> Index Scan using unique_review_per_row on reviewed_test_results rtr (cost=0.00..0.62 rows=1 width=50) (actual time=0.005..0.008 rows=1 loops=14)
Index Cond: (tr.pk = rtr.fk_reviewed_row)
SubPlan
-> Seq Scan on staff (cost=0.00..1.06 rows=1 width=4) (actual time=0.005..0.006 rows=1 loops=14)
Filter: (db_user = $20)
-> Seq Scan on staff (cost=0.00..1.06 rows=1 width=0) (actual time=0.006..0.007 rows=1 loops=14)
Filter: (db_user = $20)
-> Subquery Scan v_staff (cost=15.73..4240.07 rows=1 width=128) (actual time=29.739..74.520 rows=1 loops=14)
-> Nested Loop (cost=15.73..4240.04 rows=1 width=279) (actual time=29.731..74.510 rows=1 loops=14)
Join Filter: (s.fk_identity = i.pk)
-> Nested Loop (cost=0.00..2.31 rows=1 width=143) (actual time=0.015..0.051 rows=1 loops=14)
Join Filter: (s.fk_role = sr.pk)
-> Seq Scan on staff s (cost=0.00..1.06 rows=1 width=131) (actual time=0.006..0.008 rows=1 loops=14)
Filter: (pk = $12)
-> Seq Scan on staff_role sr (cost=0.00..1.11 rows=11 width=16) (actual time=0.002..0.018 rows=11 loops=14)
-> Hash Join (cost=15.73..4210.50 rows=207 width=120) (actual time=0.409..73.865 rows=209 loops=14)
Hash Cond: (n.id_identity = i.pk)
-> Seq Scan on names n (cost=0.00..4.27 rows=210 width=29) (actual time=0.007..0.387 rows=211 loops=14)
Filter: active
-> Hash (cost=13.12..13.12 rows=209 width=95) (actual time=0.883..0.883 rows=209 loops=1)
-> Seq Scan on identity i (cost=0.00..13.12 rows=209 width=95) (actual time=0.007..0.510 rows=209 loops=1)
Filter: ((deleted IS FALSE) AND (deceased IS NULL))
SubPlan
-> Nested Loop (cost=0.00..9.86 rows=1 width=8) (never executed)
-> Seq Scan on marital_status ms1 (cost=0.00..1.07 rows=1 width=8) (never executed)
Filter: (pk = $9)
-> Index Scan using identity_pkey on identity i1 (cost=0.00..8.27 rows=1 width=0) (never executed)
Index Cond: (i1.pk = $10)
-> Nested Loop (cost=0.00..9.35 rows=1 width=8) (never executed)
-> Seq Scan on marital_status ms (cost=0.00..1.07 rows=1 width=8) (never executed)
Filter: (pk = $9)
-> Index Scan using identity_pkey on identity i1 (cost=0.00..8.27 rows=1 width=0) (never executed)
Index Cond: (i1.pk = $10)
SubPlan
-> Result (cost=22.06..22.07 rows=1 width=0) (actual time=0.140..0.141 rows=1 loops=14)
InitPlan
-> Result (cost=11.02..11.03 rows=1 width=0) (actual time=0.068..0.069 rows=1 loops=14)
InitPlan
-> Index Scan using pg_authid_rolname_index on pg_authid (cost=1.25..11.02 rows=1 width=0) (actual time=0.062..0.062 rows=1 loops=14)
Index Cond: (rolname = 'gnumed_v10'::name)
Filter: ((NOT rolcanlogin) AND ($13 = ANY ((subplan))))
InitPlan
-> Seq Scan on pg_authid (cost=0.00..1.25 rows=1 width=4) (actual time=0.011..0.015 rows=1 loops=14)
Filter: (rolcanlogin AND (rolname = $1))
SubPlan
-> Seq Scan on pg_auth_members (cost=0.00..1.49 rows=4 width=4) (actual time=0.015..0.021 rows=4 loops=14)
Filter: (roleid = $3)
-> Result (cost=11.02..11.03 rows=1 width=0) (actual time=0.060..0.061 rows=1 loops=14)
InitPlan
-> Index Scan using pg_authid_rolname_index on pg_authid (cost=1.25..11.02 rows=1 width=0) (actual time=0.056..0.056 rows=1 loops=14)
Index Cond: (rolname = 'gm-logins'::name)
Filter: ((NOT rolcanlogin) AND ($16 = ANY ((subplan))))
InitPlan
-> Seq Scan on pg_authid (cost=0.00..1.25 rows=1 width=4) (actual time=0.010..0.013 rows=1 loops=14)
Filter: (rolcanlogin AND (rolname = $1))
SubPlan
-> Seq Scan on pg_auth_members (cost=0.00..1.49 rows=4 width=4) (actual time=0.005..0.021 rows=5 loops=14)
Filter: (roleid = $3)
-> Subquery Scan v_staff (cost=15.73..4240.07 rows=1 width=128) (actual time=29.500..70.712 rows=1 loops=14)
-> Nested Loop (cost=15.73..4240.04 rows=1 width=279) (actual time=29.492..70.701 rows=1 loops=14)
Join Filter: (s.fk_identity = i.pk)
-> Nested Loop (cost=0.00..2.31 rows=1 width=143) (actual time=0.016..0.048 rows=1 loops=14)
Join Filter: (s.fk_role = sr.pk)
-> Seq Scan on staff s (cost=0.00..1.06 rows=1 width=131) (actual time=0.007..0.008 rows=1 loops=14)
Filter: (pk = $0)
-> Seq Scan on staff_role sr (cost=0.00..1.11 rows=11 width=16) (actual time=0.003..0.016 rows=11 loops=14)
-> Hash Join (cost=15.73..4210.50 rows=207 width=120) (actual time=0.562..70.059 rows=209 loops=14)
Hash Cond: (n.id_identity = i.pk)
-> Seq Scan on names n (cost=0.00..4.27 rows=210 width=29) (actual time=0.009..0.356 rows=211 loops=14)
Filter: active
-> Hash (cost=13.12..13.12 rows=209 width=95) (actual time=0.925..0.925 rows=209 loops=1)
-> Seq Scan on identity i (cost=0.00..13.12 rows=209 width=95) (actual time=0.012..0.554 rows=209 loops=1)
Filter: ((deleted IS FALSE) AND (deceased IS NULL))
SubPlan
-> Nested Loop (cost=0.00..9.86 rows=1 width=8) (never executed)
-> Seq Scan on marital_status ms1 (cost=0.00..1.07 rows=1 width=8) (never executed)
Filter: (pk = $9)
-> Index Scan using identity_pkey on identity i1 (cost=0.00..8.27 rows=1 width=0) (never executed)
Index Cond: (i1.pk = $10)
-> Nested Loop (cost=0.00..9.35 rows=1 width=8) (never executed)
-> Seq Scan on marital_status ms (cost=0.00..1.07 rows=1 width=8) (never executed)
Filter: (pk = $9)
-> Index Scan using identity_pkey on identity i1 (cost=0.00..8.27 rows=1 width=0) (never executed)
Index Cond: (i1.pk = $10)
SubPlan
-> Result (cost=22.06..22.07 rows=1 width=0) (actual time=0.144..0.145 rows=1 loops=14)
InitPlan
-> Result (cost=11.02..11.03 rows=1 width=0) (actual time=0.070..0.071 rows=1 loops=14)
InitPlan
-> Index Scan using pg_authid_rolname_index on pg_authid (cost=1.25..11.02 rows=1 width=0) (actual time=0.064..0.064 rows=1 loops=14)
Index Cond: (rolname = 'gnumed_v10'::name)
Filter: ((NOT rolcanlogin) AND ($2 = ANY ((subplan))))
InitPlan
-> Seq Scan on pg_authid (cost=0.00..1.25 rows=1 width=4) (actual time=0.011..0.015 rows=1 loops=14)
Filter: (rolcanlogin AND (rolname = $1))
SubPlan
-> Seq Scan on pg_auth_members (cost=0.00..1.49 rows=4 width=4) (actual time=0.016..0.022 rows=4 loops=14)
Filter: (roleid = $3)
-> Result (cost=11.02..11.03 rows=1 width=0) (actual time=0.062..0.063 rows=1 loops=14)
InitPlan
-> Index Scan using pg_authid_rolname_index on pg_authid (cost=1.25..11.02 rows=1 width=0) (actual time=0.057..0.057 rows=1 loops=14)
Index Cond: (rolname = 'gm-logins'::name)
Filter: ((NOT rolcanlogin) AND ($6 = ANY ((subplan))))
InitPlan
-> Seq Scan on pg_authid (cost=0.00..1.25 rows=1 width=4) (actual time=0.010..0.013 rows=1 loops=14)
Filter: (rolcanlogin AND (rolname = $1))
SubPlan
-> Seq Scan on pg_auth_members (cost=0.00..1.49 rows=4 width=4) (actual time=0.006..0.022 rows=5 loops=14)
Filter: (roleid = $3)
Total runtime: 2041.314 ms
(140 Zeilen)

count
-------
14
(1 Zeile)

ROLLBACK

(the count is simply there to verify the view selects the
same number of rows as I am expecting from the base table,
the rollback is there because I have been experimenting
with additional indices)

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346