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 on format (cost=0.00..1.03 rows=3
width=23)
-> Hash (cost=1.05..1.05 rows=5 width=24)
-> Seq Scan on rating (cost=0.00..1.05 rows=5 width=24)
(21 rows)
Time: 22.585 ms
videos=> explain select trim(stock_code), barcode, title, supplier.description,
format.description, rating.description, genre_list(o.stock_code), release_date,
o.price, o.srp, quantity
videos-> 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);
QUERY PLAN
--------------------------------------------------------------------------------------------------------
Hash Left Join (cost=24.59..25.04 rows=3 width=132)
Hash Cond: ("outer".rating_code = "inner".rating_code)
-> Hash Left Join (cost=23.53..23.92 rows=3 width=142)
Hash Cond: ("outer".format_code = "inner".format_code)
-> Merge Left Join (cost=22.49..22.83 rows=3 width=153)
Merge Cond: ("outer".supplier_code = "inner".supplier_code)
-> Sort (cost=19.12..19.13 rows=3 width=140)
Sort Key: stock.supplier_code
-> Nested Loop Left Join (cost=0.00..19.09 rows=3 width=140)
-> Seq Scan on order_lines o (cost=0.00..1.03 rows=3
width=45)
-> Index Scan using stock_pkey on stock (cost=0.00..6.01
rows=1 width=111)
Index Cond: (("outer".stock_code)::bpchar =
stock.stock_code)
-> Sort (cost=3.37..3.52 rows=60 width=33)
Sort Key: supplier.supplier_code
-> 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 on format (cost=0.00..1.03 rows=3 width=23)
-> Hash (cost=1.05..1.05 rows=5 width=24)
-> Seq Scan on rating (cost=0.00..1.05 rows=5 width=24)
(19 rows)
Time: 29.351 ms
There are 20,000 rows in the stock table. Relevant DDL is:
create table deal(
deal_code character(16) not null unique primary key,
tag varchar(6) not null,
description varchar(92),
icon character(6),
first_available date,
last_available date
);
create table genre(
genre_code character(13) not null unique primary key,
description varchar(20) not null
);
create table rating(
rating_code character(13) not null unique primary key,
description varchar(6),
icon character(6)
);
create table format(
format_code character(13) not null unique primary key,
description varchar(6),
icon character(6)
);
create table supplier(
supplier_code character(6) not null unique primary key,
description varchar(30)
);
create table stock(
stock_code character(12) not null unique primary key,
barcode varchar(15),
title varchar(92),
supplier_code character(6) references supplier(supplier_code) on delete set
null,
format_code character(13) references format(format_code) on delete set null,
rating_code character(13) references rating(rating_code) on delete set null,
release_date date,
price numeric(9,2),
srp numeric(9,2)
);
create table stock_genre(
stock_code character(12) not null references stock(stock_code) on delete
cascade,
genre_code character(13) not null references genre(genre_code) on delete
cascade
);
create index stock_genre_stock on stock_genre(stock_code);
create index stock_genre_genre on stock_genre(genre_code);
create table stock_deal(
stock_code character(12) not null references stock(stock_code) on delete
cascade,
deal_code character(16) not null references deal(deal_code) on delete cascade,
sort_order integer,
price numeric(9,2),
srp numeric(9,2),
unique (deal_code, stock_code)
);
create index stock_deal_stock_code on stock_deal(stock_code);
create table order_lines(
orderid integer not null references orders(orderid) on delete cascade,
stock_code varchar(15) references stock(stock_code) on delete set null,
price numeric(9,2),
srp numeric(9,2),
quantity numeric(5)
);
create index order_lines_orderid on order_lines(orderid);
-- returns string listing all of the genres for a stock item
CREATE OR REPLACE FUNCTION genre_list(character) RETURNS varchar AS '
DECLARE
code ALIAS FOR $1;
result VARCHAR(100) := '''';
genre RECORD;
BEGIN
FOR genre IN SELECT * FROM genre JOIN stock_genre USING (genre_code) WHERE
stock_code = code LOOP
IF result != '''' THEN
result := result || ''/'';
END IF;
result := result || genre.description;
END LOOP;
RETURN result;
END;
' LANGUAGE plpgsql IMMUTABLE STRICT;
-- view of the stock table with supplier, format, rating and genre tags expanded
create or replace 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);
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
