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 ;
The window aggregate defined in the view is an optimization fence which prevents the view from having the where clause of the user pushed down. Thus you are computing lag over all three million plus records in the table before throwing away most of them. When done inline the partition seen is smaller and so is evaluated more quickly.
David J.
