q scan or index scan - postgres effective_cache_size

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

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