SQL Performance tuning - random_page_cost
cf. effective_cache_size
same as effective_cache_size, random_page_cost parameter controls PostgreSQL sql
cost planning behavior...
a little experiment
As an experiment, temporarily decrease effective_cache_size value and rerun the same sql...
SET random_page_cost to 2;
-> Index Scan using users_pkey on control.users us (cost=0.29..1.31 rows=1 width=10) (actual time=0.006..0.006 rows=1 loops=24)
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=71 read=1
Now, restore to the default value = 4
SET random_page_cost to 4;
-> Seq Scan on control.users us (cost=0.00..571.08 rows=21708 width=10) (actual time=0.010..8.800 rows=21708 loops=1)
Output: us.user_name, us.user_id
Buffers: shared hit=354
index scan -> seq scan