gpt4 book ai didi

postgresql - 估计 Postgres 索引的大小

转载 作者:行者123 更新时间:2023-12-03 21:11:13 41 4
gpt4 key购买 nike

我试图更好地理解创建 Postgres 索引所涉及的权衡。作为其中的一部分,我很想了解通常使用多少空间索引。我已通读 the docs ,但找不到这方面的任何信息。我一直在做自己的小实验来创建表和索引,但如果有人能解释为什么大小是这样的,那就太棒了。假设像这样一个有 1M 行的公共(public)表,其中每一行都有一个唯一的 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

  • 您估计索引大小以字节为单位,更重要的是,为什么?

    最佳答案

    由于您没有指定 index type , 我假设默认 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 here
    关于 VARIADIC参数:
  • Return rows matching elements of input array in plpgsql function

  • 基本上 ,所有索引都使用通常为 8 kb block 大小(很少为​​ 4 kb)的数据页。 有一个数据页开销B树索引开始。每个额外的数据页都有 40 字节的固定开销(当前)。每个页面存储元组,如手册 here 中所述.每个元组都有一个元组头(通常为 8 字节,包括对齐填充),可能是空位图,数据(可能包括多列索引的列之间的对齐填充),以及可能对齐到 MAXALIGN 的下一个倍数的填充。 (通常为 8 个字节)。另外,还有一个 ItemId每个元组 4 个字节。一些空间最初可以通过 fillfactor 保留以供以后添加。 - B-tree 索引默认为 90%。
    重要说明和免责声明
    报告的大小是估计的最小大小。由于页面拆分的自然膨胀,实际索引通常会大 25% 左右。另外,无法计算 对齐填充 考虑到多个列之间。可以再增加几个百分点(在极端情况下甚至更多)。看:
  • Calculating and saving space in PostgreSQL

  • 估计基于 View 中的列统计信息 pg_stats 基于系统表 pg_statistics . (直接使用后者会更快,但只允许 super 用户使用。)特别是,计算基于 null_frac ,“为空的列条目的分数”和 avg_width ,“列条目的平均宽度(以字节为单位)”来计算平均数据宽度 - 忽略多列索引可能的额外对齐填充。
    默认 90 % fillfactor 被考虑在内。 (可能会指定一个不同的。)
    高达 50% 的膨胀通常对于 B-tree 索引来说是很自然的,无需担心。
    不适用于表达式索引。
    不提供部分索引。
    如果传递了除现有普通列名之外的任何内容,则函数会引发异常。区分大小写!
    如果表是新表(或者在任何情况下统计数据可能已过时),请务必运行 ANALYZE 在调用函数更新(甚至启动!)统计信息之前,在表上。
    由于主要优化, 中的 B-tree 索引Postgres 12 浪费更少的空间,通常更接近报告的最小尺寸。
    不占 deduplication 介绍Postgres 13 ,它可以压缩具有重复值的索引。
    部分代码取自 ioguix 的膨胀估计查询:
  • https://github.com/ioguix/pgsql-bloat-estimation

  • Postgres源代码在这里有更多血腥细节:
  • https://doxygen.postgresql.org/bufpage_8h_source.html
  • 关于postgresql - 估计 Postgres 索引的大小,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/63760689/

    41 4 0
    Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
    广告合作:1813099741@qq.com 6ren.com