主页 / dba / 问题 / 238643
Accepted
Anthony
Asked: 2019-05-21 18:17:24 +0800 CST

如何基于PostgreSQL中的解释分析优化查询？

  • 772

我正在通过以下查询运行，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)
postgresql explain

1 个回答

  1. Best Answer

    您需要此索引来加快查询速度：

    CREATE INDEX ON mytable (color_code, shade_type);

    如果你想只进行索引扫描，你可以使用

    CREATE INDEX ON mytable (color_code, shade_type) INCLUDE (amount);

    但这仅在表经常被清空时才有用，并且您不需要对amount列进行热更新。

    • 1

相关问题