From: | justin <justin(at)emproshunts(dot)com> |
---|---|
To: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: performance advice needed: join vs explicit subselect |
Date: | 2009-01-27 18:27:49 |
Message-ID: | 497F5225.8040808@emproshunts.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Alban Hertroys | 2009-01-27 18:37:22 | Re: Slow first query despite LIMIT and OFFSET clause |
Previous Message | justin | 2009-01-27 18:25:16 | Re: performance advice needed: join vs explicit subselect |