Re: BUG #13817: Query planner strange choose while select/count small part of big table - complete

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