Re: Views- Advantages and Disadvantages

"Leif B. Kristensen" <leif(at)solumslekt(dot)org> writes:
> [ this query got slow in 8.2: ]
> $query = "select person_id, pb_date from tmg_persons
> where father_id = $p or mother_id = $p
> order by pb_date";

> tmg_persons is a view involving several function calls, and is a legacy
> from an earlier, flatter data model where the 'persons' table actually
> had this structure. I'm still using it in my Web application, and the
> primary function of the view is to make an easy export:

> CREATE OR REPLACE VIEW tmg_persons AS
> SELECT
> person_id,
> get_parent(person_id,1) AS father_id,
> get_parent(person_id,2) AS mother_id,
> last_edit,
> get_pbdate(person_id) AS pb_date,
> get_pddate(person_id) AS pd_date,
> gender AS s,
> living AS l,
> is_public AS p
> FROM persons;

Are get_parent() and/or get_pbdate() marked volatile by any chance?
8.2 is more conservative about optimizing sub-selects involving volatile
functions than previous releases were, because we got complaints about
surprising behavior when a volatile function is executed more or fewer
times than the text of the query would suggest. If they are really
stable or immutable, marking them so would probably help here. (If they
fetch from another table, stable is the right marking.)

regards, tom lane