Re: performance advice needed: join vs explicit subselect

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/