Re: Slow views - Mailing list pgsql-bugs

On Sat, 10 Jul 2004 davidn-postgres@rebel.net.au wrote:

> I know this has been discussed many times before but I'm not entirely
> satisfied with the answer, which I understand is "views are essentially
> macros".  Despite that Postgres is producing the correct output, I hope
> you'll all agree that the performance issue is serious enough to call
> this a "bug."
>
> I'm running Postgres 7.4.3 installed on Fedora Core 1 (i386) from rpms
> downloaded from ftp.postgres.org.
>
> I've got a view defined as:
>
>   CREATE VIEW stock_exp_v AS
>     SELECT stock_code, barcode, title, supplier.description AS supplier,
>            format.description AS format, rating.description AS rating,
>            genre_list(stock_code) AS genre, release_date, price, srp
>      FROM stock
>      LEFT JOIN supplier USING (supplier_code)
>      LEFT JOIN format USING (format_code)
>      LEFT JOIN rating USING (rating_code);
>
>
> When I use the view in a join the query takes 52 seconds, for example:
>
>   SELECT trim(stock_code), barcode, title, supplier, format, rating, genre,
>          release_date, o.price, o.srp, quantity
>   FROM order_lines o
>   LEFT JOIN stock_exp_v USING (stock_code);
>
> Time: 52110.369 ms
>
> When I expand the view by hand it takes only 27 milliseconds:
>
>   SELECT trim(stock_code), barcode, title, supplier.description,
>          format.description, rating.description, genre_list(o.stock_code),
>          release_date, o.price, o.srp, quantity
>   FROM order_lines o
>   LEFT JOIN stock USING (stock_code)
>   LEFT JOIN supplier USING (supplier_code)
>   LEFT JOIN format USING (format_code)
>   LEFT JOIN rating USING (rating_code);

That's not expanding the view.  Expanding the view would be something
like:

select trim(stock_code), barcode, title, supplier, format, rating, genre,
release_date, o.price, o.srp, quantity
FROM order_lines o
LEFT JOIN
 ( select stock_code, barcode, title, supplier.description as supplier,
   format.description AS format, rating.description AS rating,
   genre_list(stock_code) AS genre, release_date, price, srp
   FROM stock LEFT JOIN supplier USING (supplier_code)
   LEFT JOIN format USING (format_code)
   LEFT JOIN rating USING (rating_code) ) stock_exp_v USING (stock_code)

You also did an optimization, removing the subquery which PostgreSQL
isn't.

The problem is that while I believe it was safe for the query above
because it uses USING throughout, in general it is not necessarily safe,
because the queries may have different results if a join condition in the
view could return true for NULL values in stock.

 For example, I think
A LEFT JOIN B USING (blah) LEFT JOIN C ON (B.bval = C.bval or B.bval is
null)
 is different from
A LEFT JOIN (B LEFT JOIN C ON (B.bval = C.bval or B.bval is null)) USING
(blah)