Die Kapselung einer Postgres-Abfrage in einer Ansicht macht sie extrem langsam

Question

Ich habe eine Abfrage, die in etwa 5 Sekunden auf Postgres 8.4 läuft. Sie wählt Daten aus einem View aus, der mit einigen anderen Tabellen verbunden ist, verwendet aber auch die lag() Fensterfunktion, d.h..

SELECT *, lag(column1) OVER (PARTITION BY key1 ORDER BY ...), lag(...)
FROM view1 v
JOIN othertables USING (...)
WHERE ...

Der Einfachheit halber habe ich eine neue Ansicht erstellt, die einfach

SELECT *, lag(column1) OVER (PARTITION BY key1 ORDER BY ...), lag(...)
FROM view1 v

und dann SELECT daraus, wobei alle anderen JOINs und Filter wie zuvor verwendet werden. Zu meiner Überraschung wird diese Abfrage nicht in 12 Minuten abgeschlossen (ich habe sie an diesem Punkt abgebrochen). Offensichtlich hat Postgres einen anderen Ausführungsplan gewählt. Wie bringe ich es dazu, dies nicht zu tun, d. h. denselben Plan wie in der ursprünglichen Abfrage zu verwenden? Ich hätte gedacht, dass eine Ansicht den Ausführungsplan nicht ändern sollte, aber anscheinend tut sie es doch.

Edit: Darüber hinaus habe ich festgestellt, dass selbst wenn ich den Inhalt der ersten Ansicht in die zweite kopiere, es immer noch kehrt nicht zurück.

Edit 2: OK, ich habe die Abfrage soweit vereinfacht, dass ich die Pläne veröffentlichen kann.

Verwendung der Ansicht (diese kehrt nicht innerhalb einer angemessenen Zeit zurück):

Subquery Scan sp  (cost=5415201.23..5892463.97 rows=88382 width=370)
  Filter: (((sp.ticker)::text ~~ 'Some Ticker'::text) AND (sp.price_date >= '2010-06-01'::date))
  ->  WindowAgg  (cost=5415201.23..5680347.20 rows=53029193 width=129)
        ->  Sort  (cost=5415201.23..5441715.83 rows=53029193 width=129)
              Sort Key: sp.stock_id, sp.price_date
              ->  Hash Join  (cost=847.87..1465139.61 rows=53029193 width=129)
                    Hash Cond: (sp.stock_id = s.stock_id)
                    ->  Seq Scan on stock_prices sp  (cost=0.00..1079829.20 rows=53029401 width=115)
                    ->  Hash  (cost=744.56..744.56 rows=29519 width=18)
                          ->  Seq Scan on stocks s  (cost=0.00..744.56 rows=29519 width=18)

Herausnehmen der Fensterfunktion aus der Ansicht und Einfügen in die Abfrage selbst (diese kehrt sofort zurück):

WindowAgg  (cost=34.91..34.95 rows=7 width=129)
  ->  Sort  (cost=34.91..34.92 rows=7 width=129)
        Sort Key: sp.stock_id, sp.price_date
        ->  Nested Loop  (cost=0.00..34.89 rows=7 width=129)
              ->  Index Scan using stocks_ticker_unique on stocks s  (cost=0.00..4.06 rows=1 width=18)
                    Index Cond: ((ticker)::text = 'Some Ticker'::text)
                    Filter: ((ticker)::text ~~ 'Some Ticker'::text)
              ->  Index Scan using stock_prices_id_date_idx on stock_prices sp  (cost=0.00..30.79 rows=14 width=115)
                    Index Cond: ((sp.stock_id = s.stock_id) AND (sp.price_date >= '2010-06-01'::date))

Es scheint also, dass im langsamen Fall versucht wird, die Fensterfunktion zuerst auf alle Daten anzuwenden und sie dann zu filtern, was wahrscheinlich das Problem ist. Ich weiß allerdings nicht, warum es das tut.

Answer 1

Der Unterschied zwischen den beiden Plänen ergibt sich aus der Verbindung mit einem Aggregat. Dies verhindert die Verwendung eines verschachtelten Schleifenplans. Wenn Sie das Aggregat in Ihrer Ansicht verwenden, versetzen Sie sich in dieses ungünstige Szenario.

Dies führt zum Beispiel fast immer zu einem Merge- oder Hash-Join-Plan für die beiden Tabellen, gefolgt von einer Top-n-Sortierung:

select foo.*
from foo
join (select bar.* from bar group by bar.field) as bar on foo.field = bar.field
where ...
order by bar.field
limit 10;

Answer 2

Vielleicht könnten Sie eine Gemeinsamer Tabellenausdruck (CTE) anstelle einer Ansicht. Ich kann helfen, die Abfrage klarer in ähnlicher Weise wie mit einem Blick, aber scheint nicht den Ausführungsplan in der gleichen Weise zu beeinflussen.

Ich hatte ein ähnliches Problem in diese Frage und die Verwendung einer CTE anstelle eines Views machte den Ausführungsplan viel effizienter.