Re: performance advice needed: join vs explicit subselect - Mailing list pgsql-general

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

> Karsten Hilbert <Karsten.Hilbert@gmx.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.141rows=34 loops=1) 
                           ->  Index Scan using test_type_pkey on test_type tt1  (cost=0.00..0.62 rows=1 width=96)
(actualtime=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.008rows=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)
(actualtime=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.011rows=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)
(actualtime=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=1loops=14) 
                                   Filter: (pk = $12)
                             ->  Seq Scan on staff_role sr  (cost=0.00..1.11 rows=11 width=16) (actual
time=0.002..0.018rows=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=211loops=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.510rows=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=1width=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.015rows=1 loops=14) 
                                                       Filter: (rolcanlogin AND (rolname = $1))
                                               SubPlan
                                                 ->  Seq Scan on pg_auth_members  (cost=0.00..1.49 rows=4 width=4)
(actualtime=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=1width=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.013rows=1 loops=14) 
                                                       Filter: (rolcanlogin AND (rolname = $1))
                                               SubPlan
                                                 ->  Seq Scan on pg_auth_members  (cost=0.00..1.49 rows=4 width=4)
(actualtime=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=1loops=14) 
                                   Filter: (pk = $0)
                             ->  Seq Scan on staff_role sr  (cost=0.00..1.11 rows=11 width=16) (actual
time=0.003..0.016rows=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=211loops=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.554rows=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=1width=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.015rows=1 loops=14) 
                                                       Filter: (rolcanlogin AND (rolname = $1))
                                               SubPlan
                                                 ->  Seq Scan on pg_auth_members  (cost=0.00..1.49 rows=4 width=4)
(actualtime=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=1width=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.013rows=1 loops=14) 
                                                       Filter: (rolcanlogin AND (rolname = $1))
                                               SubPlan
                                                 ->  Seq Scan on pg_auth_members  (cost=0.00..1.49 rows=4 width=4)
(actualtime=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