PostgreSQL SELECT 太慢

Question

我正在寻找优化查询的想法。

目前，我有一个 4M 行的表，我只想检索引用的最后 1000 行:

SELECT * 
FROM customers_material_events 
WHERE reference = 'XXXXXX' 
ORDER BY date DESC 
LIMIT 1000;

这是执行计划:

Limit  (cost=12512155.48..12512272.15 rows=1000 width=6807) (actual time=8953.545..9013.658 rows=1000 loops=1)
   Buffers: shared hit=16153 read=30342
   ->  Gather Merge  (cost=12512155.48..12840015.90 rows=2810036 width=6807) (actual time=8953.543..9013.613 rows=1000 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         Buffers: shared hit=16153 read=30342
         ->  Sort  (cost=12511155.46..12514668.00 rows=1405018 width=6807) (actual time=8865.186..8865.208 rows=632 loops=3)
               Sort Key: date DESC
               Sort Method: top-N heapsort  Memory: 330kB
               Worker 0:  Sort Method: top-N heapsort  Memory: 328kB
               Worker 1:  Sort Method: top-N heapsort  Memory: 330kB
               Buffers: shared hit=16153 read=30342
               ->  Parallel Seq Scan on customers_material_events  (cost=0.00..64165.96 rows=1405018 width=6807) (actual time=0.064..944.029 rows=1117807 loops=3)
                     Filter: ((reference)::text = 'FFFEEE'::text)
                     Rows Removed by Filter: 17188
                     Buffers: shared hit=16091 read=30342
 Planning Time: 0.189 ms
 Execution Time: 9013.834 ms
(18 rows)

我看到执行时间非常非常慢...

Answer 1

这个查询的理想索引是:

CREATE INDEX ON customers_material_events (reference, date);

这将允许您快速找到特定 reference 的值，按 date 自动排序，因此不需要额外的排序步骤。