postgresql - 为什么不使用索引？

Question

我有疑问:

EXPLAIN ANALYSE
SELECT * FROM "order_bt" o
LEFT JOIN agreement a ON a.id = o.agreement_id

两个表都有索引:

"order_idx_agreement_id" btree (agreement_id)
"agreement_pkey" PRIMARY KEY, btree (id)

但是 explain analyse 说 Seq Scan on agreement a。 为什么？

                                                       QUERY PLAN                                                        
-------------------------------------------------------------------------------------------------------------------------
 Hash Left Join  (cost=36.97..199.70 rows=3702 width=193) (actual time=0.961..8.422 rows=3702 loops=1)
   Hash Cond: (o.agreement_id = a.id)
   ->  Seq Scan on order_bt o  (cost=0.00..116.02 rows=3702 width=136) (actual time=0.025..3.566 rows=3702 loops=1)
   ->  Hash  (cost=22.54..22.54 rows=1154 width=57) (actual time=0.912..0.912 rows=1154 loops=1)
         Buckets: 2048  Batches: 1  Memory Usage: 104kB
         ->  Seq Scan on agreement a  (cost=0.00..22.54 rows=1154 width=57) (actual time=0.019..0.397 rows=1154 loops=1)
 Planning time: 0.785 ms
 Execution time: 8.886 ms
(8 rows)

Answer 1

只有大约 1000 行，您选择了所有列。如果它确实使用了索引，它就必须返回到表中以获取其余数据。对于这么小的表，扫描整个表会更快，尤其是因为看起来您无论如何都选择了协议(protocol)表的每一行和每一列。