估算Postgres索引的大小

Question

我试图更好地了解创建Postgres索引所涉及的权衡利弊。作为其中一部分，我很想了解索引通常使用多少空间。我已经阅读了文档，但找不到任何相关信息。我一直在自己进行小实验，创建表和索引，但如果有人能够解释为什么大小为什么是这样的，那将是非常棒的。假设有一个像这样的常规表，其中包含1M行，每行都有唯一的id和唯一的outstanding。

CREATE TABLE account (
    id integer,
    active boolean NOT NULL,
    outstanding double precision NOT NULL,
);

并且创建的索引包括：

• CREATE INDEX id_idx ON account(id)
• CREATE INDEX outstanding_idx ON account(outstanding)
• CREATE INDEX id_outstanding_idx ON account(id, outstanding)
• CREATE INDEX active_idx ON account(active)
• CREATE INDEX partial_id_idx ON account(id) WHERE active

你认为这些索引的大小将会是多少字节？更重要的是，为什么？

- pir

---

更多的列通常意味着更大的索引大小。请注意，在您的最后一个（第五个）示例中，“WHERE active”并不意味着更大的大小，因为“active”实际上不是一个关键字。相反，它可能意味着一个更小的索引，因为由于where子句，较少的id值必须被索引。 - Tim Biegeleisen

Answer 1

你可以自行计算。每个索引条目有8字节的开销，加上你索引的数据平均大小（以内部二进制格式表示）。

还有一些额外的开销，比如页面头和页脚以及内部索引页，但这不会占用太多空间，除非你的索引行非常宽。

- Laurenz Albe

---

好的，那么无论数据类型如何，它都是8个字节吗？当索引多列时，这在索引每行会增加16个字节吗？例如：CREATE INDEX id_outstanding_idx ON account(id, outstanding)。 - pir

抱歉有误导。如果索引中有一个 bigint 和一个 smallint，则索引条目的大小将为 8 + 8 + 2。 - Laurenz Albe

Answer 2

由于您没有指定索引类型，我将默认使用B树索引。其他类型可能会有很大的不同。

这里是一个简单的函数，用于计算给定表和给定列上的索引的估计最小字节数：

CREATE OR REPLACE FUNCTION f_index_minimum_size(_tbl regclass, _cols VARIADIC text[], OUT estimated_minimum_size bigint)
  LANGUAGE plpgsql AS
$func$
DECLARE
   _missing_column text;
BEGIN

-- assert
SELECT i.attname
FROM   unnest(_cols) AS i(attname)
LEFT   JOIN pg_catalog.pg_attribute a ON a.attname = i.attname
                                     AND a.attrelid = _tbl
WHERE  a.attname IS NULL
INTO   _missing_column;

IF FOUND THEN
   RAISE EXCEPTION 'Table % has no column named %', _tbl, quote_ident(_missing_column);
END IF;


SELECT INTO estimated_minimum_size
       COALESCE(1 + ceil(reltuples/trunc((blocksize-page_overhead)/(4+tuple_size)))::int, 0) * blocksize -- AS estimated_minimum_size
FROM  (
   SELECT maxalign, blocksize, reltuples, fillfactor, page_overhead
        , (maxalign  -- up to 16 columns, else nullbitmap may force another maxalign step
         + CASE WHEN datawidth <= maxalign  THEN maxalign
                WHEN datawidth%maxalign = 0 THEN datawidth
                ELSE                            (datawidth + maxalign) - datawidth%maxalign END  -- add padding to the data to align on MAXALIGN
          ) AS tuple_size
   FROM  (
      SELECT c.reltuples, count(*)
           , 90 AS fillfactor
           , current_setting('block_size')::bigint AS blocksize
           , CASE WHEN version() ~ '64-bit|x86_64|ppc64|ia64|amd64|mingw32'  -- MAXALIGN: 4 on 32bits, 8 on 64bits
                  THEN 8 ELSE 4 END AS maxalign
           , 40 AS page_overhead  -- 24 bytes page header + 16 bytes "special space"
           -- avg data width without null values
           , sum(ceil((1-COALESCE(s.null_frac, 0)) * COALESCE(s.avg_width, 1024))::int) AS datawidth  -- ceil() because avg width has a low bias
      FROM   pg_catalog.pg_class     c
      JOIN   pg_catalog.pg_attribute a ON a.attrelid = c.oid
      JOIN   pg_catalog.pg_stats     s ON s.schemaname = c.relnamespace::regnamespace::text
                                      AND s.tablename  = c.relname
                                      AND s.attname    = a.attname
      WHERE  c.oid = _tbl
      AND    a.attname = ANY(_cols) --  all exist, verified above
      GROUP  BY 1
      ) sub1
   ) sub2;
END
$func$;

调用示例：

SELECT f_index_minimum_size('my_table', 'col1', 'col2', 'col3');

SELECT f_index_minimum_size('public.my_table', VARIADIC '{col1, col2, col3}');

db<>fiddle 这里

关于 VARIADIC 参数：

• 在 plpgsql 函数中返回与输入数组元素匹配的行

基本上，所有索引都使用通常为8 kb块大小（很少为4 kb）的数据页。对于B树索引，有一个数据页开销。每个附加的数据页具有40字节的固定开销（目前）。每个页面都存储如手册here所示的元组。每个元组都有元组头（通常包括8个字节的对齐填充）、可能是空位图、数据（可能包括多列索引之间的对齐填充）以及可能对齐到下一个MAXALIGN倍数（通常为8个字节）。此外，每个元组还有一个4个字节的ItemId。对于B树索引，默认情况下会预留一些空间以供稍后添加使用90％的fillfactor。

重要说明和免责声明

报告的大小是估计的最小大小。实际索引通常会因页面分裂而增加约25％的自然膨胀。此外，该计算不考虑多个列之间可能存在的对齐填充。在极端情况下，可以增加另外几个百分点或更多。请参见：

• 在PostgreSQL中计算和保存空间

估计基于视图pg_stats中的列统计信息，该视图基于系统表pg_statistics。特别地，计算基于null_frac，即“空列条目的分数”和avg_width，即“列条目的平均宽度（以字节为单位）”，以计算平均数据宽度-忽略多列索引的可能的附加对齐填充。

默认考虑了 90％ 的 fillfactor。 （可以指定不同的值。）

B 树索引通常会自然膨胀50％，无需担心。

不适用于表达式索引。

不支持部分索引。

如果传递了除现有纯列名称之外的任何内容，则该函数将引发异常。区分大小写！

如果表是新表（或在任何情况下统计信息可能已过时），请务必在调用函数之前对表运行ANALYZE以更新（甚至启动！）统计信息。

由于主要优化，Postgres 12中的B树索引浪费的空间更少，通常更接近报告的最小大小。

不考虑 去重，它是使用Postgres 13引入的，可以压缩具有重复值的索引。

代码的部分内容取自ioguix的膨胀估算查询，链接在此处：

• https://github.com/ioguix/pgsql-bloat-estimation

更多关于Postgres源代码的细节在这里：

• https://doxygen.postgresql.org/bufpage_8h_source.html

- Erwin Brandstetter