BUG #9743: subquery on view is not pulling up.

The following bug has been logged on the website:

Bug reference: 9743
Logged by: Karl Walbrecht
Email address: kwalbrecht(at)cghtech(dot)com
PostgreSQL version: 9.2.1
Operating system: solaris
Description:

When I query a view which has calculated values, even if I don't select one
of the calculated values, they are being calculated. I believe this is
called subquery unnesting in oracle. In essence since the calculated
portion of the table is not being referenced then the calculation should not
be preformed. I tried setting the cost of sda.KML_Sector() and
sda.GeoJSON_Sector() to 150000 but it had no effect on the query plan. I
have tried rewriting the queries but again to no effect. I have concluded
that this is a performance issue in the query optimization routines. I
apologize in advance if this is just stupidity on my part.

Thanks

The functions sda.KML_sector(), and sda.GeoJSON_Sector() are written in
pgplsql.

CREATE TABLE sda.sectors_base_table
(
sector_id INTEGER NOT NULL
, airspace_id INTEGER NOT NULL
, area_id INTEGER NOT NULL
, sector_num VARCHAR NOT NULL
, sector_name VARCHAR NOT NULL
, color_number INTEGER NOT NULL DEFAULT 0
, bb_adjacent_array INTEGER[]
, sector GEOMETRY

, CONSTRAINT sectors_sector_pk PRIMARY KEY(sector_id)
, CONSTRAINT sectors_geom_ck CHECK (GeometryType(sector) =
'GEOMETRYCOLLECTION')
, CONSTRAINT sectors_airspace_fk FOREIGN KEY (airspace_id) REFERENCES
sda.airspaces_(airspace_id)
, CONSTRAINT sectors_area_fk FOREIGN KEY (area_id) REFERENCES
sda.areas_(area_id)
, CONSTRAINT sector_color_number_ck CHECK(color_number >= 0 and
color_number <= 12)
);

CREATE OR REPLACE VIEW sda.sectors_view AS
SELECT s.*
, sda.KML_Sector(s.sector_id, s.airspace_id) as kml_fragment
, sda.GeoJSON_Sector(s.sector_id, s.airspace_id) as
geojson_fragment
FROM sda.sectors_base_table as s
;

sdat=> explain analyze verbose select sector_id from sda.sectors_view;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Subquery Scan on sectors (cost=0.00..271.83 rows=54 width=4) (actual
time=16.602..3186.404 rows=54 loops=1)
Output: sectors.sector_id
-> Seq Scan on sda.sectors_ s (cost=0.00..271.29 rows=54 width=53976)
(actual time=16.600..3186.360 rows=54 loops=1)
Output: s.sector_id, s.airspace_id, s.area_id, s.sector_num,
s.sector_name, s.color_number, s.bb_adjacent_array, s.sector,
sda.kml_sector(s.sector_id, s.airspace_id), sda.geojson_sector(s.sector_id,
s.airspace_id)
Total runtime: 3186.526 ms
(5 rows)

sdat=> explain analyze verbose select sector_id from
sda.sectors_base_table;
QUERY PLAN
---------------------------------------------------------------------------------------------------------
Seq Scan on sda.sectors_ (cost=0.00..48.54 rows=54 width=4) (actual
time=0.015..0.070 rows=54 loops=1)
Output: sector_id
Total runtime: 0.102 ms
(3 rows)