PostgreSQL是否缓存视图的执行计划,就像它为存储过程做的那样?
编号。
视图基本上是一个宏——您的视图定义与查询合并,然后执行。
因此:
CREATE VIEW v1 AS SELECT * FROM customers WHERE active;
SELECT * FROM v1 WHERE name LIKE 'A%';
变成:
SELECT * FROM customers WHERE active AND name LIKE 'A%';
Postgresql 9.3引入了Materialised View
,可以缓存查询结果。您可以通过刷新来使缓存失效。
示例:
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;
来源:https://bugfactory.io/blog/caching-expensive-queries-with-materialized-views-in-postgresql/
本文介绍如何使用 PostgreSQL 中的材料化视图缓存开销高昂的查询。通过创建一个材料化视图来保存经常需要进行计算或连接表的查询结果,可以大幅提高查询性能。当查询需要重新执行时,可以直接从材料化视图中获取结果,避免重复计算和连接表操作,从而节省时间和资源。