Thread: performance advice needed: join vs explicit subselect
Hello all,
maybe some general advice can be had on this:
table test_results
modified_by integer foreign key staff(pk),
intended_reviewer integer foreign key staff(pk),
actual_reviewer integer foreign key staff(pk)
(this table will contain millions of rows)
table staff
pk integer
name text
(this table will contain at most 50 rows)
Now I want to set up a view which aggregates test results
with staff names for all three foreign keys. This would mean
I would either have to
- join test_results to staff three times, once for each
of the foreign keys, this is going to be messy with
tracking table aliases, duplicate column names etc
- write three explicit sub-selects for the columns I want
to denormalize into the view definition
Is there general advice as to which of the alternatives is
worse under most if not all circumstances ?
Thanks,
Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
Karsten Hilbert wrote:
> Hello all,
>
> maybe some general advice can be had on this:
>
> table test_results
> modified_by integer foreign key staff(pk),
> intended_reviewer integer foreign key staff(pk),
> actual_reviewer integer foreign key staff(pk)
>
> (this table will contain millions of rows)
>
> table staff
> pk integer
> name text
>
> (this table will contain at most 50 rows)
>
> Now I want to set up a view which aggregates test results
> with staff names for all three foreign keys. This would mean
> I would either have to
>
> - join test_results to staff three times, once for each
> of the foreign keys, this is going to be messy with
> tracking table aliases, duplicate column names etc
>
> - write three explicit sub-selects for the columns I want
> to denormalize into the view definition
>
>
Select testresults.*, Modifer.Name, Intended.name, Actual.name from
testresults
left join (Select pk, name from staff) Modifer
on Modifer.pk = testresults.modified_by
left join (Select pk, name from staff) Intended
on Reviewer.pk = testresults.intended_reviewer
left join (Select pk, name from staff) Actual
on pk = testresults.actual_reviewer
This is what i think you are after. You can do this via nested queries
also for each name
typo sorry justin wrote: > Karsten Hilbert wrote: >> Hello all, >> >> maybe some general advice can be had on this: >> >> table test_results >> modified_by integer foreign key staff(pk), >> intended_reviewer integer foreign key staff(pk), >> actual_reviewer integer foreign key staff(pk) >> >> (this table will contain millions of rows) >> >> table staff >> pk integer >> name text >> >> (this table will contain at most 50 rows) >> >> Now I want to set up a view which aggregates test results >> with staff names for all three foreign keys. This would mean >> I would either have to >> >> - join test_results to staff three times, once for each >> of the foreign keys, this is going to be messy with >> tracking table aliases, duplicate column names etc >> >> - write three explicit sub-selects for the columns I want >> to denormalize into the view definition >> >> > Select testresults.*, Modifer.Name, Intended.name, Actual.name from > testresults > left join (Select pk, name from staff) Modifer > on Modifer.pk = testresults.modified_by > left join (Select pk, name from staff) Intended > on Inteded.pk = testresults.intended_reviewer > left join (Select pk, name from staff) Actual > on Actual.pk = testresults.actual_reviewer > > > This is what i think you are after. You can do this via nested > queries also for each name >
On Tue, Jan 27, 2009 at 07:12:05PM +0100, Karsten Hilbert wrote:
> Hello all,
>
> maybe some general advice can be had on this:
>
> table test_results
> modified_by integer foreign key staff(pk),
> intended_reviewer integer foreign key staff(pk),
> actual_reviewer integer foreign key staff(pk)
>
> (this table will contain millions of rows)
>
> table staff
> pk integer
> name text
>
> (this table will contain at most 50 rows)
>
> Now I want to set up a view which aggregates test results
> with staff names for all three foreign keys. This would mean
> I would either have to
>
> - join test_results to staff three times, once for each
> of the foreign keys, this is going to be messy with
> tracking table aliases, duplicate column names etc
if you've only got three columns it shouldn't be too bad should it?
> - write three explicit sub-selects for the columns I want
> to denormalize into the view definition
This would look a bit prettier, but PG tends not to optimize at all. It
always executes it as a subplan and hence will only work nicely when
you've got a very small subset of the test_results coming back. PG will
*sometimes* remove subexpressions, but doesn't seem very predictable
about it:
SELECT id
FROM (
SELECT a.id, (SELECT b.name FROM bar b WHERE a.tid = b.tid)
FROM foo a) x;
PG seems to recognize that it can remove the subselect in the above
which is nice, but in other situations it doesn't seem to.
--
Sam http://samason.me.uk/
On Jan 27, 2009, at 7:12 PM, Karsten Hilbert wrote:
> Hello all,
>
> maybe some general advice can be had on this:
>
> table test_results
> modified_by integer foreign key staff(pk),
> intended_reviewer integer foreign key staff(pk),
> actual_reviewer integer foreign key staff(pk)
>
> (this table will contain millions of rows)
>
> table staff
> pk integer
> name text
>
> (this table will contain at most 50 rows)
>
> Now I want to set up a view which aggregates test results
> with staff names for all three foreign keys. This would mean
> I would either have to
>
> - join test_results to staff three times, once for each
> of the foreign keys, this is going to be messy with
> tracking table aliases, duplicate column names etc
>
> - write three explicit sub-selects for the columns I want
> to denormalize into the view definition
>
> Is there general advice as to which of the alternatives is
> worse under most if not all circumstances ?
I did something similar once using expression logic for my aggregates:
SELECT
SUM(CASE WHEN modified_by = pk THEN 1 ELSE 0 END) AS modified_by_count,
SUM(CASE WHEN intended_reviewer = pk THEN 1 ELSE 0 END) AS
intended_reviewer_count,
SUM(CASE WHEN actual_reviewer = pk THEN 1 ELSE 0 END) AS
actual_reviewer_count
FROM test_results, staff
WHERE pk IN (modified_by, intended_reviewer, actual_reviewer)
Mind, this will very probably do a sequential scan over the product of
both tables, but at least now the staff table is in that product only
once.
In actuality I didn't use CASE statements but cast the boolean results
of the expressions directly to integer, something like
SUM((modified_by = pk)::int), but that cast may no longer work since
8.3.
I no longer have access to the project that I used this on, so I can't
verify unfortunately.
Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.
!DSPAM:737,497f5aa8747035160810079!
On Tue, Jan 27, 2009 at 06:48:11PM +0000, Sam Mason wrote:
> > table test_results
> > modified_by integer foreign key staff(pk),
> > intended_reviewer integer foreign key staff(pk),
> > actual_reviewer integer foreign key staff(pk)
> >
> > (this table will contain millions of rows)
> >
> > table staff
> > pk integer
> > name text
> >
> > (this table will contain at most 50 rows)
> >
> > Now I want to set up a view which aggregates test results
> > with staff names for all three foreign keys. This would mean
> > I would either have to
> >
> > - join test_results to staff three times, once for each
> > of the foreign keys, this is going to be messy with
> > tracking table aliases, duplicate column names etc
>
> if you've only got three columns it shouldn't be too bad should it?
This is what one deserves for thinking to be able to distill
the essence of a problem :-)
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 !):
create view clin.v_test_results as
select
cenc.fk_patient
as pk_patient,
-- test_result
tr.pk as pk_test_result,
tr.clin_when,
-- unified
vttu.unified_code,
vttu.unified_name,
case when coalesce(trim(both from tr.val_alpha), '') = ''
then tr.val_num::text
else case when tr.val_num is null
then tr.val_alpha
else tr.val_num::text || ' (' || tr.val_alpha || ')'
end
end as unified_val,
coalesce(tr.val_target_min, tr.val_normal_min)
as unified_target_min,
coalesce(tr.val_target_max, tr.val_normal_max)
as unified_target_max,
coalesce(tr.val_target_range, tr.val_normal_range)
as unified_target_range,
tr.soap_cat,
tr.narrative
as comment,
-- test result data
tr.val_num,
tr.val_alpha,
tr.val_unit,
vttu.conversion_unit,
tr.val_normal_min,
tr.val_normal_max,
tr.val_normal_range,
tr.val_target_min,
tr.val_target_max,
tr.val_target_range,
tr.abnormality_indicator,
tr.norm_ref_group,
tr.note_test_org,
tr.material,
tr.material_detail,
-- test type data
vttu.code_tt,
vttu.name_tt,
vttu.coding_system_tt,
vttu.comment_tt,
vttu.code_unified,
vttu.name_unified,
vttu.coding_system_unified,
vttu.comment_unified,
-- episode/issue data
epi.description
as episode,
-- status of last review
coalesce(rtr.fk_reviewed_row, 0)::bool
as reviewed,
rtr.is_technically_abnormal
as is_technically_abnormal,
rtr.clinically_relevant
as is_clinically_relevant,
rtr.comment
as review_comment,
(select
short_alias || ' (' ||
coalesce(title || ' ', '') ||
coalesce(firstnames || ' ', '') ||
coalesce(lastnames, '') ||
')'
from dem.v_staff
where pk_staff = rtr.fk_reviewer
) as last_reviewer,
rtr.modified_when
as last_reviewed,
coalesce (
(rtr.fk_reviewer = (select pk from dem.staff where db_user = current_user)),
False
)
as review_by_you,
coalesce (
(tr.fk_intended_reviewer = rtr.fk_reviewer),
False
)
as review_by_responsible_reviewer,
-- potential review status
(select
short_alias || ' (' ||
coalesce(title || ' ', '') ||
coalesce(firstnames || ' ', '') ||
coalesce(lastnames, '') ||
')'
from dem.v_staff
where pk_staff = tr.fk_intended_reviewer
) as responsible_reviewer,
coalesce (
(tr.fk_intended_reviewer = (select pk from dem.staff where db_user = current_user)),
False
)
as you_are_responsible,
case when ((select 1 from dem.staff where db_user = tr.modified_by) is null)
then '<' || tr.modified_by || '>'
else (select short_alias from dem.staff where db_user = tr.modified_by)
end
as modified_by,
tr.modified_when,
tr.row_version as row_version,
-- management keys
-- clin.clin_root_item
tr.pk_item,
tr.fk_encounter as pk_encounter,
tr.fk_episode as pk_episode,
-- test_result
tr.fk_type as pk_test_type,
tr.fk_intended_reviewer as pk_intended_reviewer,
tr.xmin as xmin_test_result,
-- v_unified_test_types
vttu.pk_test_org,
vttu.pk_test_type_unified,
-- v_pat_episodes
epi.fk_health_issue
as pk_health_issue,
-- reviewed_test_results
rtr.fk_reviewer as pk_last_reviewer
from
clin.test_result tr
left join clin.encounter cenc on (tr.fk_encounter = cenc.pk)
left join clin.episode epi on (tr.fk_episode = epi.pk)
left join clin.reviewed_test_results rtr on (tr.pk = rtr.fk_reviewed_row)
,
clin.v_unified_test_types vttu
where
tr.fk_type = vttu.pk_test_type
;
> > - write three explicit sub-selects for the columns I want
> > to denormalize into the view definition
>
> This would look a bit prettier, but PG tends not to optimize at all. It
> always executes it as a subplan and hence will only work nicely when
> you've got a very small subset of the test_results coming back.
Potentially in the low hundreds.
Thanks !
Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
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?
regards, tom lane
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
On Tue, Jan 27, 2009 at 04:42:02PM -0500, Tom Lane wrote:
> 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?
Oh, and the use case is to select all the test_results which
belong to a certain patient:
where pk_patient = <scalar>
Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
Karsten Hilbert <Karsten.Hilbert@gmx.net> writes:
> the explain analyze of which is (I've actually gotten it to
> work better in the meantime as you can see):
Looks like most of the problem is in the subquery scans on v_staff,
which seems to be a rather expensive view :-(. Maybe you can
simplify that a bit.
regards, tom lane
On Tue, Jan 27, 2009 at 05:30:23PM -0500, Tom Lane wrote: > Karsten Hilbert <Karsten.Hilbert@gmx.net> writes: > > the explain analyze of which is (I've actually gotten it to > > work better in the meantime as you can see): > > Looks like most of the problem is in the subquery scans on v_staff, > which seems to be a rather expensive view :-(. Maybe you can > simplify that a bit. Thanks so much. I wasn't quite sure how to correlate the seemingly expensive parts of the explain with the view/query parts. Will experiment with that... Well, going directly to the dem.staff table below dem.v_staff forces me to forego the actual name of the staff entry - but the alias will need to suffice ;-) This brings down query time from 2000ms to 7ms. Our doctors won't complain about slow lab data retrieval anymore ... ;-) Thanks, Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346