PostgreSQL のオプティマイザ統計情報を確認する
- pg_stat_user_tables
- n_live_tup: 行数
- n_dead_tup: 不要な(ガベージとなっている)行数
- last_analyze: ANALYZE された日時
aurora-postgres107 awsuser 13:44 => select * from pg_stat_user_tables; relid | schemaname | relname | seq_scan | seq_tup_read | idx_scan | idx_tup_fetch | n_tup_ins | n_tup_upd | n_tup_del | n_tup_hot_upd | n_live_tup | n_dead_tup | n_mod_since_analyze | last_vacuu m | last_autovacuum | last_analyze | last_autoanalyze | vacuum_count | autovacuum_count | analyze_count | autoanalyze_count -------+------------+------------------+----------+--------------+----------+---------------+-----------+-----------+-----------+---------------+------------+------------+---------------------+----------- --+-----------------+--------------+------------------+--------------+------------------+---------------+------------------- 20499 | public | pgbench_accounts | 3982 | 636618770 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | | 0 | 0 | 0 | 0 20502 | public | pgbench_history | 0 | 0 | | | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | | 0 | 0 | 0 | 0 20496 | public | pgbench_tellers | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | | 0 | 0 | 0 | 0 20493 | public | pgbench_branches | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | | 0 | 0 | 0 | 0 (4 rows) Time: 11.534 ms aurora-postgres107 awsuser 13:45 => analyze pgbench_accounts; ANALYZE Time: 51023.555 ms aurora-postgres107 awsuser 13:49 => analyze pgbench_history; ANALYZE Time: 4.838 ms aurora-postgres107 awsuser 13:49 => analyze pgbench_tellers; ANALYZE Time: 861.631 ms aurora-postgres107 awsuser 13:49 => analyze pgbench_branches; ANALYZE Time: 97.672 ms aurora-postgres107 awsuser 13:49 => select * from pg_stat_user_tables; relid | schemaname | relname | seq_scan | seq_tup_read | idx_scan | idx_tup_fetch | n_tup_ins | n_tup_upd | n_tup_del | n_tup_hot_upd | n_live_tup | n_dead_tup | n_mod_since_analyze | last_vacuu m | last_autovacuum | last_analyze | last_autoanalyze | vacuum_count | autovacuum_count | analyze_count | autoanalyze_count -------+------------+------------------+----------+--------------+----------+---------------+-----------+-----------+-----------+---------------+------------+------------+---------------------+----------- --+-----------------+-------------------------------+------------------+--------------+------------------+---------------+------------------- 20499 | public | pgbench_accounts | 3982 | 636618770 | 0 | 0 | 0 | 0 | 0 | 0 | 1000000023 | 0 | 0 | | | 2019-10-13 13:48:29.577544+00 | | 0 | 0 | 1 | 0 20502 | public | pgbench_history | 0 | 0 | | | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | 2019-10-13 13:49:25.797489+00 | | 0 | 0 | 1 | 0 20496 | public | pgbench_tellers | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 100000 | 0 | 0 | | | 2019-10-13 13:49:34.146215+00 | | 0 | 0 | 1 | 0 20493 | public | pgbench_branches | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 10000 | 0 | 0 | | | 2019-10-13 13:49:39.786877+00 | | 0 | 0 | 1 | 0 (4 rows) Time: 11.658 ms
- pg_stats で列統計を参照できる。
aurora-postgres107 awsuser 13:53 => ¥x
aurora-postgres107 awsuser 13:53 => select * from pg_stats where tablename = 'pgbench_accounts' and attname = 'aid';
-[ RECORD 1 ]----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
schemaname | public
tablename | pgbench_accounts
attname | aid
inherited | f
null_frac | 0
avg_width | 4
n_distinct | -1
most_common_vals |
most_common_freqs |
histogram_bounds | {60814,10795715,21463216,30907670,42117763,52106907,61207613,70576084,80914430,91131376,100753925,110990754,121482625,132236843,142541838,151836882,161107926,169972558,180409363,189444879,199185870,209956656,219778236,230635774,240069437,250570290,259938531,269520728,279549323,289115868,299506440,308917267,318976934,328960455,338130309,348795979,358585995,368564674,379147261,389401241,398876550,408622750,419746424,430218119,441160716,451956639,462649650,473153342,482273474,491900892,501782995,512139033,522341040,531138199,541530720,551181427,561493083,571169975,581023579,591554462,599739834,609944740,621003108,631427193,641984893,651345974,661666172,671714772,682136209,691457107,701453860,710621601,720645788,729302265,739518578,749859090,759000386,769988872,779889944,790362142,800606393,810024605,819391048,829388826,839126779,849413480,860658902,869786870,880113980,889940523,900308333,910800021,919503190,928953277,938412306,949723183,959391357,970382364,979643943,990236561,999965815}
correlation | 1
most_common_elems |
most_common_elem_freqs |
elem_count_histogram |
Time: 1.774 ms
