seq scan or index scan - postgres effective_cache_size

SQL Performance tuning - effective_cache_size mystery ?!

cf. random_page_cost
　cf. SET join_collapse_limit to 10;

• improper effective_cache_size setting causes table full scan even if target table has an index... <- Postgres Cost Estimation Error ???

• cf: http://www.cybertec.at/effective_cache_size-better-set-it-right/

Sets the planner's assumption about the effective size of the disk cache that is available to a single query. 
This is factored into estimates of the cost of using an index;
 a higher value makes it more likely index scans will be used,
 a lower value makes it more likely sequential scans will be used.

performance trouble happened

• With the exact same SQL, the plan behaves quite differently depending on PostgreSQL host
• all same Postgresql version 9.4.5
• cannot figure out what causes this ...???

effective_cache_size

host	effective_cache_size	scan plan
a	1048576 x 8Kb	seq scan
b	1048576 x 8Kb	seq scan
c	4194304 x 8Kb	index scan

　Let's confirm

As an experiment, temporarily decrease effective_cache_size value and rerun the same sql...

SET effective_cache_size TO '1000 MB';

 ->  Seq Scan on control.users us  (cost=0.00..581.03 rows=21803 width=10) (actual time=0.004..15.029 rows=21803 loops=1)
           Output: us.user_name, us.user_id
           Buffers: shared hit=363

Got cha ! index scan -> seq scan　

Now, restore to the original effective_cache_size

SET effective_cache_size TO '4194304';

->  Index Scan using users_pkey on control.users us  (cost=0.29..0.31 rows=1 width=10) (actual time=0.003..0.004 rows=1 loops=144)
    Output: us.user_id, us.group_id, us.description, us.password, us.person_id, us.department, us.regist_time, us.modified_time, us.password_expire_time, us.available_from, us.available_to, us.trace_queue, us.available, us.user_name, us.agency_id, us.location, us.last_login_time
　   Index Cond: (us.user_id = ht.operator)
    Buffers: shared hit=432

seq scan　-> index scan