Re: select on view shows different plan than select on table

On Wednesday, April 12, 2017, Bernd Lehmkuhl <bernd(dot)lehmkuhl(at)mailbox(dot)org>
wrote:
>
> 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.