Hi,
Looking at the explained plans, it makes sense the seq scan is preferred as
it is expected to be cheaper.
Seq scan enabled:
Hash Join (cost=12.88..108087.13 rows=3992515 width=177)
The main costs (83%) of this plan are:
Seq Scan on shipment_order_sub_item this_ (cost=0.00..90,031.15
rows=3,992,515 width=125)
Seq scan disabled:
Nested Loop (cost=1.24..138607.34 rows=3992515 width=177)
The main costs (71%) of this plan are:
Index Scan using fk_fk_rr5k2n8n892ye3uposkh3xp6v_idx on
shipment_order_sub_item this_ (cost=0.43..98636.88 rows=3992515 width=125)
The expected costs for the seq scan enabled is 78% of that of the disable
seq scan.
Questions:
- What kind of disks do you have (ssd's?)
- Is the seq scan slow if you repeat it immediately after the first run?
- What is your current random_page_cost
- Could you try to reissue the query after lowering the value of
random_page_cost, like so:
SET random_page_cost TO 1.5;
- Could you redo the explain with
EXPLAIN (ANALYZE ON, BUFFERS ON)
regards,
Feike