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);
btrim |barcode|title|supplier
| format | rating | genre | release_date | price | srp | quantity
-+---+-++++---+--+---+--+--
B00392S | 9397810039223 | GREATEST WILDLIFE SHOW ON EARTH THE | ROADSHOW
ENTERTAINMENT | VHS|| | 2000-06-05 | 1.23 | 1.10 | 15
B00392S | 9397810039223 | GREATEST WILDLIFE SHOW ON EARTH THE | ROADSHOW
ENTERTAINMENT | VHS|| | 2000-06-05 | 1.23 | 1.10 | 15
| | |
||| | | 1.23 | 1.10 |5
(3 rows)
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);
btrim |barcode|title| description
| description | description | genre_list | release_date | price | srp | quantity
-+---+-++-+-++--+---+--+--
B00392S | 9397810039223 | GREATEST WILDLIFE SHOW ON EARTH THE | ROADSHOW
ENTERTAINMENT | VHS | || 2000-06-05 | 1.23 | 1.10 |
15
B00392S | 9397810039223 | GREATEST WILDLIFE SHOW ON EARTH THE | ROADSHOW
ENTERTAINMENT | VHS | || 2000-06-05 | 1.23 | 1.10 |
15
| | |
| | || | 1.23 | 1.10 |5
(3 rows)
Time: 26.820 ms
The plans are different; views are clearly not "essentially macros":
videos=> explain select trim(stock_code), barcode, title, supplier, format, rating,
genre, release_date, o.price, o.srp, quantity
videos-> from order_lines o left join stock_exp_v using (stock_code);
QUERY PLAN
Merge Left Join (cost=4344.01..4447.40 rows=202 width=217)
Merge Cond: ("outer"."?column5?" = "inner".stock_code)
-> Sort (cost=1.05..1.06 rows=3 width=45)
Sort Key: (o.stock_code)::bpchar
-> Seq Scan on order_lines o (cost=0.00..1.03 rows=3 width=45)
-> Sort (cost=4342.95..4393.38 rows=20171 width=188)
Sort Key: stock_exp_v.stock_code
-> Subquery Scan stock_exp_v (cost=3.85..1711.67 rows=20171 width=188)
-> Hash Left Join (cost=3.85..1509.96 rows=20171 width=125)
Hash Cond: ("outer".rating_code = "inner".rating_code)
-> Hash Left Join (cost=2.79..1233.63 rows=20171 width=135)
Hash Cond: ("outer".format_code = "inner".format_code)
-> Hash Left Join (cost=1.75..930.03 rows=20171 width=146)
Hash Cond: ("outer".supplier_code =
"inner".supplier_code)
-> Seq Scan on stock (cost=0.00..625.71 rows=20171
width=133)
-> Hash (cost=1.60..1.60 rows=60 width=33)
-> Seq Scan on supplier (cost=0.00..1.60
rows=60 width=33)
-> Hash (cost=1.03..1.03 rows=3 width=23)
-> Seq Scan
