q scan or index scan - PostgreSQL random_page_cost

2413 ワード
effective_cache_size random_page_cost PostgreSQL PostgreSQL テキストリンク

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