Does PostgreSQL cache execution plan of a view

Question

Does PostgreSQL cache execution plan of a view, as it does for stored procedures?

yes, I have and haven't find anything, so I'm asking here – Mariola Bączek Sep 16 '14 at 09:22 — Mariola Bączek, Sep 16 '14 at 09:22

score 8 · Accepted Answer · answered Sep 16 '14 at 12:35

8

No.

A view is basically a macro - your view definition gets merged with the query against it and then executed.

So:

CREATE VIEW v1 AS SELECT * FROM customers WHERE active;
SELECT * FROM v1 WHERE name LIKE 'A%';

becomes:

SELECT * FROM customers WHERE active AND name LIKE 'A%';

answered Sep 16 '14 at 12:35

Richard Huxton

21,516
3
39
51

1

Indeed, caching the execution plan would make little sense. Maybe today you're selecting every row of the view, and tomorrow you're just selecting one. Those queries are going to require entirely different query plans. – Kevin Feb 18 '15 at 21:19
1

If someone would find a link to support this answer, that would be nice. May it be different for different versions of PostreSQL? – Yevgeniy Afanasyev Oct 10 '17 at 04:41
Agreed @YevgeniyAfanasyev, without evidence to support it this is just an opinion – jcollum Jun 12 '23 at 21:16
If anyone feels like not trusting me they could, you know, bother to read the official docs instead. It's not like all this isn't covered there. – Richard Huxton Jun 13 '23 at 06:46

score 4 · Answer 2 · answered Jun 29 '22 at 17:29

Postgresql 9.3 introduced Materialised View, which cache the result of the query. You can invalidate that cache by refreshing it.

Example:

CREATE MATERIALIZED VIEW customers_with_revenue AS
  SELECT
    customers.*,
    SUM(invoice_items.amount_in_cents) AS revenue
  FROM customers
    INNER JOIN invoices
      ON customers.id = invoices.customer_id
    INNER JOIN invoice_items
      ON invoices.id = invoice_items.invoice_id
  GROUP BY customers.id ORDER BY revenue DESC;

REFRESH MATERIALIZED VIEW customers_with_revenue;

Source: https://bugfactory.io/blog/caching-expensive-queries-with-materialized-views-in-postgresql/

There are limits to how often you can refresh a mat view though. — jcollum, Jun 12 '23 at 21:15

Does PostgreSQL cache execution plan of a view

2 Answers2