PostgreSQL 性能优化

发表于 更新于 分类于 ， 本文字数： 77 阅读时长 ≈ 1 分钟

创建索引

对大表进行分表

查询时使用with进行预处理（select in with）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
WITH regional_sales AS (
    SELECT region, SUM(amount) AS total_sales
    FROM orders
    GROUP BY region
), top_regions AS (
    SELECT region
    FROM regional_sales
    WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)
)
SELECT region,
       product,
       SUM(quantity) AS product_units,
       SUM(amount) AS product_sales
FROM orders
WHERE region IN (SELECT region FROM top_regions)
GROUP BY region, product;