Search Postgresql Archives

select on view shows different plan than select on table

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hi list,

I have a view defined as:
CREATE VIEW kkm_ergebnisse.v_protokoll_details_mit_dauer AS
SELECT
  id,
  schritt,
  objekt_typ,
  objekt_id,
  zeit,
  rechenweg_thema,
  rechenweg_variante,
  rechenweg_stoffgruppe,
  formel_inhalt,
  formel_stoff,
  formel_variablen,
  ergebnis_variable,
  ergebnis_wert,
  status,
  id_rechenlauf,
  formel_id,
  formel_name,
  formel_variante,
  ergebnis_variable_einheit,
  zeitpunkt,
DATE_PART('seconds', zeitpunkt::time without time zone - LAG(zeitpunkt::time without time zone) OVER (ORDER BY id)) * 1000 AS dauer_in_ms
FROM
  kkm_ergebnisse.t_protokoll_details ;

Table kkm_ergebnisse.t_protokoll_details is defined like this:
CREATE TABLE kkm_ergebnisse.t_protokoll_details
(
  id serial NOT NULL,
  schritt integer,
  objekt_typ smallint NOT NULL,
  objekt_id integer,
  zeit integer,
  rechenweg_thema character varying(256),
  rechenweg_variante character varying(256),
  rechenweg_stoffgruppe character varying(256),
  formel_inhalt character varying(4000),
  formel_stoff character varying(256),
  formel_variablen character varying(4000),
  ergebnis_variable character varying(256),
  ergebnis_wert double precision,
  status character varying(4000),
  id_rechenlauf integer NOT NULL,
  formel_id integer,
  formel_name character varying(256),
  formel_variante character varying(256),
  ergebnis_variable_einheit character varying(255),
  zeitpunkt time with time zone DEFAULT clock_timestamp(),
  CONSTRAINT pk PRIMARY KEY (id),
  CONSTRAINT fk_rechenlauf FOREIGN KEY (id_rechenlauf)
      REFERENCES kkm_ergebnisse.t_rechenlaeufe (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT ck_protokoll_details_objekt_typ CHECK (objekt_typ = ANY (ARRAY[1, 2]))
);

Why do I get different execution plans when querying the view like this:
SELECT
  *
FROM
  kkm_ergebnisse.v_protokoll_details_mit_dauer
WHERE
  id_rechenlauf = 123
ORDER BY
  schritt ;

opposed to querying against the definition of the view?
SELECT
  id,
  schritt,
  objekt_typ,
  objekt_id,
  zeit,
  rechenweg_thema,
  rechenweg_variante,
  rechenweg_stoffgruppe,
  formel_inhalt,
  formel_stoff,
  formel_variablen,
  ergebnis_variable,
  ergebnis_wert,
  status,
  id_rechenlauf,
  formel_id,
  formel_name,
  formel_variante,
  ergebnis_variable_einheit,
  zeitpunkt,
DATE_PART('seconds'::text, zeitpunkt::time without time zone - LAG(zeitpunkt::time without time zone) OVER (ORDER BY id)) * 1000::double precision AS dauer_in_ms
FROM
  kkm_ergebnisse.t_protokoll_details
WHERE
  id_rechenlauf = 123
ORDER BY
  schritt ;

Execution plan query against view (slow):
Sort (cost=570776.54..570779.14 rows=1037 width=347) (actual time=4067.919..4068.133 rows=11250 loops=1) Output: v_protokoll_details_mit_dauer.id, v_protokoll_details_mit_dauer.schritt, v_protokoll_details_mit_dauer.objekt_typ, v_protokoll_details_mit_dauer.objekt_id, v_protokoll_details_mit_dauer.zeit, v_protokoll_details_mit_dauer.rechenweg_thema, v_proto (...)
  Sort Key: v_protokoll_details_mit_dauer.schritt
  Sort Method: quicksort  Memory: 6185kB
  Buffers: shared hit=30925 read=166050
-> Subquery Scan on v_protokoll_details_mit_dauer (cost=0.43..570724.60 rows=1037 width=347) (actual time=4038.722..4062.481 rows=11250 loops=1) Output: v_protokoll_details_mit_dauer.id, v_protokoll_details_mit_dauer.schritt, v_protokoll_details_mit_dauer.objekt_typ, v_protokoll_details_mit_dauer.objekt_id, v_protokoll_details_mit_dauer.zeit, v_protokoll_details_mit_dauer.rechenweg_thema, v (...)
        Filter: (v_protokoll_details_mit_dauer.id_rechenlauf = 123)
        Rows Removed by Filter: 3091203
        Buffers: shared hit=30925 read=166050
-> WindowAgg (cost=0.43..531778.35 rows=3115700 width=339) (actual time=0.127..3839.099 rows=3102453 loops=1) Output: pd.id, pd.schritt, pd.objekt_typ, pd.objekt_id, pd.zeit, pd.rechenweg_thema, pd.rechenweg_variante, pd.rechenweg_stoffgruppe, pd.formel_inhalt, pd.formel_stoff, pd.formel_variablen, pd.ergebnis_variable, pd.ergebnis_wert, pd.status, p (...)
              Buffers: shared hit=30925 read=166050
-> Index Scan using pk on kkm_ergebnisse.t_protokoll_details pd (cost=0.43..446096.60 rows=3115700 width=339) (actual time=0.109..1682.884 rows=3102453 loops=1) Output: pd.id, pd.schritt, pd.objekt_typ, pd.objekt_id, pd.zeit, pd.rechenweg_thema, pd.rechenweg_variante, pd.rechenweg_stoffgruppe, pd.formel_inhalt, pd.formel_stoff, pd.formel_variablen, pd.ergebnis_variable, pd.ergebnis_wert, pd.sta (...)
                    Buffers: shared hit=30925 read=166050
Planning time: 0.323 ms
Execution time: 4069.073 ms

Execution plan of query against table (fast):
Sort (cost=305.58..308.17 rows=1037 width=339) (actual time=51.558..52.140 rows=11250 loops=1) Output: id, schritt, objekt_typ, objekt_id, zeit, rechenweg_thema, rechenweg_variante, rechenweg_stoffgruppe, formel_inhalt, formel_stoff, formel_variablen, ergebnis_variable, ergebnis_wert, status, id_rechenlauf, formel_id, formel_name, formel_variante, (...)
  Sort Key: t_protokoll_details.schritt
  Sort Method: quicksort  Memory: 6185kB
  Buffers: shared hit=687
-> WindowAgg (cost=222.52..253.63 rows=1037 width=339) (actual time=15.925..37.210 rows=11250 loops=1) Output: id, schritt, objekt_typ, objekt_id, zeit, rechenweg_thema, rechenweg_variante, rechenweg_stoffgruppe, formel_inhalt, formel_stoff, formel_variablen, ergebnis_variable, ergebnis_wert, status, id_rechenlauf, formel_id, formel_name, formel_var (...)
        Buffers: shared hit=687
-> Sort (cost=222.52..225.12 rows=1037 width=339) (actual time=15.905..16.660 rows=11250 loops=1) Output: id, schritt, objekt_typ, objekt_id, zeit, rechenweg_thema, rechenweg_variante, rechenweg_stoffgruppe, formel_inhalt, formel_stoff, formel_variablen, ergebnis_variable, ergebnis_wert, status, id_rechenlauf, formel_id, formel_name, form (...)
              Sort Key: t_protokoll_details.id
              Sort Method: quicksort  Memory: 6185kB
              Buffers: shared hit=687
-> Index Scan using fki_protokoll_details_id_rechenlauf on kkm_ergebnisse.t_protokoll_details (cost=0.43..170.58 rows=1037 width=339) (actual time=0.037..7.281 rows=11250 loops=1) Output: id, schritt, objekt_typ, objekt_id, zeit, rechenweg_thema, rechenweg_variante, rechenweg_stoffgruppe, formel_inhalt, formel_stoff, formel_variablen, ergebnis_variable, ergebnis_wert, status, id_rechenlauf, formel_id, formel_name (...)
                    Index Cond: (t_protokoll_details.id_rechenlauf = 123)
                    Buffers: shared hit=687
Planning time: 0.229 ms
Execution time: 55.245 ms

Thanks for any insight...


--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux