CODEWITHSUNDEEP
postgresql
Eugen Konkov
Eugen Konkov

Reputation: 25113

Why index is not used?

I have query:

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

Both tables have index:

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

but explain analyse says Seq Scan on agreement a. Why?

                                                       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)

Upvotes: 0

Views: 41

Answers (2)

Laurenz Albe
Laurenz Albe

Reputation: 246063

Hash joins don't take advantage of indexes, they scan both relations sequentially.

Only nested loop joins and merge joins can use indexes on the join columns.

PostgreSQL just decided that it isn't efficient to use the indexes, because a hash join is the fastest way. Since the estimates are accurate, it probably made the correct decision.

You can drop the indexes if the tables stay that small and you don't need them for other purposes.

Upvotes: 2

Jeremy
Jeremy

Reputation: 6713

There are only ~1000 rows and you are selecting all columns. If it did use the index, it would have to go back to the table to get the rest of the data. For such a small table, it's faster to just scan the whole thing, especially since it looks like you were selecting every single row and column of the agreement table anyway.

Upvotes: 3

Related Questions