在PostgreSQL的物化视图慢查询

Question

创建物化视图 “mviews_price2”

id     - Integer 
price    - Integer 
price2   - Integer 
unit_price  - Integer 
location_site_id - Integer 
leave_site_id - Integer 
web    - Boolean 
category_id  - Integer 

创建索引：

CREATE INDEX "location_site_id_idx" ON "mviews_price2" ("location_site_id") 
CREATE INDEX "leave_site_id_idx" ON "mviews_price2" ("leave_site_id") 
CREATE INDEX "web_idx" ON "mviews_price2" ("web") 

获取查询：

SELECT DISTINCT "mviews_price2"."category_id" FROM "mviews_price2" WHERE ("mviews_price2"."location_site_id" = 1 OR "mviews_price2"."leave_site_id" = 1 OR "mviews_price2"."web" = true) 

解释：

HashAggregate (cost=42757.45..42757.82 rows=37 width=4) (actual time=596.252..596.257 rows=37 loops=1)' 
Group Key: category_id' 
-> Seq Scan on mviews_price2 (cost=0.00..39614.90 rows=1257021 width=4) (actual time=0.038..362.877 rows=1341021 loops=1)' 
     Filter: ((location_site_id = 1) OR (leave_site_id = 1) OR web)' 
     Rows Removed by Filter: 423439' 
Planning time: 0.954 ms' 
Execution time: 596.311 ms' 

条

在View - 1764460线

问题：

1. 为什么它不使用索引？
2. 为什么查询速度很慢？

Answer 1

OR很难优化。尝试使用UNION代替编写查询：

SELECT "mviews_price2"."category_id" 
FROM "mviews_price2" 
WHERE "mviews_price2"."location_site_id" = 1 
UNION -- on purpose to remove duplicates 
SELECT "mviews_price2"."category_id" 
FROM "mviews_price2" 
WHERE "mviews_price2"."leave_site_id" = 1 
UNION -- on purpose to remove duplicates 
SELECT "mviews_price2"."category_id" 
FROM "mviews_price2" 
WHERE "mviews_price2"."web" = true; 

每个子查询应该能够采取的指标之一的优势。

注意：这仍然可能无法加快查询速度。 web上的索引 - 特别是 - 只有一个只有2个值的字段（如果可以采用NULL，则为3）。除非web非常罕见，否则结果可能仍然是全表扫描。