我正在通过EXPLAIN ANALYZE运行以下查询,但运行起来需要很长时间。基于EXPLAIN ANALYZE的输出,我可以对我的表做些什么来使这个查询更快地执行?
select count(amount), sum(amount)
from mytable
where color_code = 5 and shade_type = 'Light';EXPLAIN ANALYZE输出:
Finalize Aggregate (cost=946640.31..946640.32 rows=1 width=40) (actual time=4799.256..4799.257 rows=1 loops=1)
-> Gather (cost=946640.09..946640.30 rows=2 width=40) (actual time=4799.191..4800.566 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial Aggregate (cost=945640.09..945640.10 rows=1 width=40) (actual time=4797.002..4797.003 rows=1 loops=3)
-> Parallel Seq Scan on mytable (cost=0.00..945573.39 rows=13338 width=6) (actual time=656.722..4791.404 rows=10103 loops=3)
Filter: ((color_code = 5) AND ((shade_type)::text = 'Light'::text))
Rows Removed by Filter: 4888180
Planning time: 0.257 ms
Execution time: 4800.661 ms
(10 rows)发布于 2019-05-21 05:50:00
您需要此索引才能使查询快速:
CREATE INDEX ON mytable (color_code, shade_type);如果希望仅扫描索引,则可以使用
CREATE INDEX ON mytable (color_code, shade_type) INCLUDE (amount);但是,只有当表经常被清空,并且您不需要对amount列进行热更新时,这才会有用。
https://dba.stackexchange.com/questions/238643
复制相似问题