Slow views - Mailing list pgsql-bugs

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
formatusing (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);