測量 PostgreSQL 表行的大小

我有一個 PostgreSQL 表。select *非常慢，但又select id好又快。我認為可能是行的大小非常大並且需要一段時間才能傳輸，或者可能是其他一些因素。

我需要所有欄位（或幾乎所有欄位），因此僅選擇一個子集並不是快速解決方法。選擇我想要的欄位仍然很慢。

這是我的表架構減去名稱：

integer                  | not null default nextval('core_page_id_seq'::regclass)
character varying(255)   | not null
character varying(64)    | not null
text                     | default '{}'::text
character varying(255)   | 
integer                  | not null default 0
text                     | default '{}'::text
text                     | 
timestamp with time zone | 
integer                  | 
timestamp with time zone | 
integer                  | 

文本欄位的大小可以是任意大小。但是，在最壞的情況下，不超過幾千字節。

問題

1. 這有什麼叫“瘋狂低效”的嗎？
2. 有沒有辦法在 Postgres 命令行中測量頁面大小來幫助我調試它？

Q2：way to measure page size

PostgreSQL 提供了許多數據庫對像大小函式。我在這個查詢中打包了最有趣的，並在底部添加了一些統計訪問​​函式。（附加模組pgstattuple提供了更多有用的功能。）

這將表明測量“行大小”的不同方法會導致非常不同的結果。這完全取決於您想要測量的內容。

此查詢需要Postgres 9.3 或更高版本。對於舊版本，請參見下文。

在子查詢中使用VALUES表達式LATERAL，以避免拼寫每一行的計算。

替換public.tbl為可選的模式限定表名，以獲得收集的行大小統計資訊的緊湊視圖。您可以將其包裝到 plpgsql 函式中以重複使用，將表名作為參數送出並使用EXECUTE…

SELECT l.metric, l.nr AS bytes
    , CASE WHEN is_size THEN pg_size_pretty(nr) END AS bytes_pretty
    , CASE WHEN is_size THEN nr / NULLIF(x.ct, 0) END AS bytes_per_row
FROM  (
  SELECT min(tableoid)        AS tbl      -- = 'public.tbl'::regclass::oid
       , count(*)             AS ct
       , sum(length(t::text)) AS txt_len  -- length in characters
  FROM   **public.tbl** t                     -- provide table name *once*
  ) x
CROSS  JOIN LATERAL (
  VALUES
    (true , 'core_relation_size'               , pg_relation_size(tbl))
  , (true , 'visibility_map'                   , pg_relation_size(tbl, 'vm'))
  , (true , 'free_space_map'                   , pg_relation_size(tbl, 'fsm'))
  , (true , 'table_size_incl_toast'            , pg_table_size(tbl))
  , (true , 'indexes_size'                     , pg_indexes_size(tbl))
  , (true , 'total_size_incl_toast_and_indexes', pg_total_relation_size(tbl))
  , (true , 'live_rows_in_text_representation' , txt_len)
  , (false, '------------------------------'   , NULL)
  , (false, 'row_count'                        , ct)
  , (false, 'live_tuples'                      , pg_stat_get_live_tuples(tbl))
  , (false, 'dead_tuples'                      , pg_stat_get_dead_tuples(tbl))
  ) l(is_size, metric, nr);

結果：

公制 | 字節 | 字節漂亮 | bytes_per_row
-----------------------------------+----------+--------------+---------------
core_relation_size | 44138496 | 42 MB | 91
可見性地圖 | 0 | 0 字節 | 0
free_space_map | 32768 | 32 KB | 0
table_size_incl_toast | 44179456 | 42 MB | 91
索引大小 | 33128448 | 32 MB | 68
total_size_incl_toast_and_indexes | 77307904 | 74 MB | 159
live_rows_in_text_representation | 29987360 | 29 MB | 62
------------------------------ | | |
行數 | 483424 | |
live_tuples | 483424 | |
死元組 | 第2677章 |

對於舊版本（Postgres 9.2 或更早版本）：

WITH x AS (
  SELECT count(*)               AS ct
       , sum(length(t::text))   AS txt_len  -- length in characters
       , '**public.tbl**'::regclass AS tbl      -- provide table name as string
  FROM   **public.tbl** t                       -- provide table name as name
  ), y AS (
  SELECT ARRAY [pg_relation_size(tbl)
              , pg_relation_size(tbl, 'vm')
              , pg_relation_size(tbl, 'fsm')
              , pg_table_size(tbl)
              , pg_indexes_size(tbl)
              , pg_total_relation_size(tbl)
              , txt_len
            ] AS val
       , ARRAY ['core_relation_size'
              , 'visibility_map'
              , 'free_space_map'
              , 'table_size_incl_toast'
              , 'indexes_size'
              , 'total_size_incl_toast_and_indexes'
              , 'live_rows_in_text_representation'
            ] AS name
  FROM   x
  )
SELECT unnest(name)                AS metric
    , unnest(val)                 AS bytes
    , pg_size_pretty(unnest(val)) AS bytes_pretty
    , unnest(val) / NULLIF(ct, 0) AS bytes_per_row
FROM   x, y

UNION ALL SELECT '------------------------------', NULL, NULL, NULL
UNION ALL SELECT 'row_count', ct, NULL, NULL FROM x
UNION ALL SELECT 'live_tuples', pg_stat_get_live_tuples(tbl), NULL, NULL FROM x
UNION ALL SELECT 'dead_tuples', pg_stat_get_dead_tuples(tbl), NULL, NULL FROM x;

結果相同。

Q1：anything inefficient?

您可以優化列順序以節省每行的一些字節，目前浪費在對齊填充中：

integer                  | not null default nextval('core_page_id_seq'::regclass)
integer                  | not null default 0
character varying(255)   | not null
character varying(64)    | not null
text                     | default '{}'::text
character varying(255)   | 
text                     | default '{}'::text
text                     |
timestamp with time zone |
timestamp with time zone |
integer                  |
integer                  |

這樣每行可以節省 8 到 18 個字節。我稱之為列俄羅斯方塊。看：

• 為讀取性能配置 PostgreSQL
• 在 PostgreSQL 中計算和節省空間

還要考慮：

• 當所有值都是 36 個字元時，使用 char 與 varchar 進行索引查找會明顯更快嗎

引用自：https://dba.stackexchange.com/questions/23879