Re: View performance - Mailing list pgsql-performance

From Tom Lane
Subject Re: View performance
Date
Msg-id 2666.1040931759@sss.pgh.pa.us
Whole thread Raw
In response to View performance  (Bruno Wolff III <bruno@wolff.to>)
Responses Re: View performance  (Bruno Wolff III <bruno@wolff.to>)
List pgsql-performance
Bruno Wolff III <bruno@wolff.to> writes:
> I was looking at some queries that appeared to be slower than I remembered
> them being under 7.2 (which may be a wrong perception) and noticed
> that a view wasn't being handled very efficiently.

The change in behavior from 7.2 is probably due to this patch:

2002-12-05 16:46  tgl

    * src/backend/optimizer/plan/planner.c (REL7_3_STABLE): Avoid
    pulling up sublinks from a subselect's targetlist.  Works around
    problems that occur if sublink is referenced via a join alias
    variable.  Perhaps this can be improved later, but a simple and
    safe fix is needed for 7.3.1.

which means that views using subselects in their targetlists will not be
flattened into the calling query in 7.3.1.  This is not real desirable,
but I see no other short-term fix.

In the particular case, your view definition seemed mighty inefficient
anyway (it must recompute the subselects for each column retrieved from
the view) so I think your rewrite is a good change.

            regards, tom lane

pgsql-performance by date:

Previous
From: Bruno Wolff III
Date:
Subject: Re: View performance
Next
From: Tom Lane
Date:
Subject: Re: View performance