👀 Views: 118 💬 Answers: 1 📅 Created: 2025-06-14

postgresql performance materialized-views ctes sql

I'm performance testing and I've been banging my head against this for hours. I'm sure I'm missing something obvious here, but I'm trying to debug I'm facing significant performance issues when querying a materialized view that utilizes complex subqueries defined in Common Table Expressions (CTEs)... The view is designed to aggregate data across multiple tables, and I'm concerned that the structure of my CTEs is causing the slowdown. The materialized view is defined as follows: ```sql CREATE MATERIALIZED VIEW aggregated_data AS WITH cte_one AS ( SELECT user_id, COUNT(*) AS total_orders FROM orders GROUP BY user_id ), cte_two AS ( SELECT user_id, SUM(amount) AS total_spent FROM payments GROUP BY user_id ) SELECT cte_one.user_id, cte_one.total_orders, cte_two.total_spent FROM cte_one JOIN cte_two ON cte_one.user_id = cte_two.user_id; ``` When I query this materialized view with: ```sql SELECT * FROM aggregated_data WHERE total_orders > 10; ``` The execution time is noticeably high, often taking several seconds, which is unacceptable for my application. I have tried creating indexes on the underlying tables (orders and payments) but haven't seen much improvement. The PostgreSQL version I'm using is 14.1. I even attempted to refresh the materialized view using: ```sql REFRESH MATERIALIZED VIEW aggregated_data; ``` However, the refresh operation also takes an extensive amount of time. Is there a more optimal way to structure my CTEs, or should I consider using a different approach for the materialized view? Any insights on improving performance or best practices for using CTEs within materialized views would be greatly appreciated! This issue appeared after updating to Sql 3.9. This is my first time working with Sql LTS. I've been using Sql for about a year now.