BUG #9743: subquery on view is not pulling up. - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      9743
Logged by:          Karl Walbrecht
Email address:      kwalbrecht@cghtech.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)