intermediateSELECT Queries — WHERE, GROUP BY, HAVING, ORDER BY

A report query that aggregates 50 million orders is taking 30 seconds. How do you optimize it?

Step-by-step optimization: 1) EXPLAIN ANALYZE — identify the bottleneck (Seq Scan on orders? Sort taking 20s?). 2) Add partial index on frequently filtered column: CREATE INDEX ON orders(status, order_date) WHERE status IN ('COMPLETED', 'PENDING'). 3) Use materialized views: CREATE MATERIALIZED VIEW daily_order_summary AS SELECT date_trunc('day', order_date), COUNT(*), SUM(amount) FROM orders GROU

Ready to master this question?

Generate a complete walkthrough — background, the full answer in plain language, a working code example explained line by line, a real-world scenario, common mistakes, and how this same question gets asked in different ways.

Next Step

Continue to What is the difference between INNER JOIN, LEFT JOIN, and FULL OUTER JOIN? →← Back to all SQL questions