Re: View performance

Bruno Wolff III <bruno(at)wolff(dot)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