Re: Slow views

On Sat, 10 Jul 2004 davidn-postgres(at)rebel(dot)net(dot)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)