ablog

不器用で落着きのない技術者のメモ

PostgreSQLのオプティマイザ統計情報を確認する

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