Re: [GENERAL] select on view shows different plan than select on table

Поиск
Список
Период
Сортировка
От David G. Johnston
Тема Re: [GENERAL] select on view shows different plan than select on table
Дата
Msg-id CAKFQuwafuoeVcQDZ59uWezCpmNrZd7hzYUSU+6PR6oS8=Kz0Uw@mail.gmail.com
обсуждение исходный текст
Ответ на [GENERAL] select on view shows different plan than select on table  (Bernd Lehmkuhl <bernd.lehmkuhl@mailbox.org>)
Список pgsql-general
On Wednesday, April 12, 2017, Bernd Lehmkuhl <bernd.lehmkuhl@mailbox.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.

В списке pgsql-general по дате отправления:

Предыдущее
От: Bernd Lehmkuhl
Дата:
Сообщение: [GENERAL] select on view shows different plan than select on table
Следующее
От: Osahon Oduware
Дата:
Сообщение: Re: [GENERAL] Error During PostGIS Build From Source on Linux