Re: performance regression, 7.2.3 -> 7.3b5 w/ VIEW

Ross J. Reedstrom wrote:
> Hey Hackers -
> I was testing beta5 and found a performance regression involving
> application of constraints into a VIEW - I've got a view that is fairly
> expensive, involving a subselet and an aggregate. When the query is
> rewritten in 7.2.3, the toplevel constraint is used to filter before
> the subselect - in 7.3b5, it comes after.
>
> For this query, the difference is 160 ms vs. 2 sec. Any reason for this
> change?

I could be way off base, but here's a shot in the dark:

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&threadm=3D0885E1.8F369ACA%40mascari.com&rnum=3&prev=/groups%3Fq%3DMike%2BMascari%2Bsecurity%2BTom%2BLane%26ie%3DUTF-8%26oe%3DUTF-8%26hl%3Den

At the time I thought PostgreSQL was doing something naughty by
allowing user functions to be invoked on data that would
ultimately not be returned. Now I know how Oracle uses VIEWS for
row security: Oracle functions invoked in DML statements can't
record any changes to the database. So if the above is the
cause, I wouldn't have any problems with the patch being
reversed. Maybe separate privileges for read-only vs. read-write
functions are in order at some point in the future though...

Mike Mascari
mascarm(at)mascari(dot)com