PG_索引

发表于 本文字数： 2.1k 阅读时长 ≈ 2 分钟

  • 查询某张表的索引

    1
    2
    3
    SELECT * 
    FROM pg_stat_user_indexes
    WHERE relname = 'stock_move';

  • 查询索引大小

    1
    SELECT pg_size_pretty(pg_indexes_size('stock_move'));

  • 查询某张表的所有索引大小

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    SELECT 
        c.relname AS `表名`,
        i.relname AS `索引名`,
        pg_size_pretty(pg_indexes_size(c.oid)) AS `索引大小`
    FROM 
        pg_class c
    JOIN 
        pg_class i ON i.relparent = c.oid
    WHERE 
        c.relname = 'stock_move'  -- 替换为具体表名
        AND c.relkind = 'r'  -- 'r'表示普通表
    ORDER BY 
        pg_indexes_size(c.oid) DESC;

  • orderby id与其它索引

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    --===========================================================
    explain (analyze on, buffers on)
    select id 
    from stock_move 
    where 1 =1 
    and company_id = 48
    AND date BETWEEN '2023-08-01 00:00:00' AND '2023-12-01 00:00:00'
    order by id+0
    limit 8000;
    --===========================================================

    --===========================================================
    explain (analyze on, buffers on)
    select id 
    from stock_move 
    where 1 =1 
    and company_id = 48
    AND date BETWEEN '2023-08-01 00:00:00' AND '2023-12-01 00:00:00'
    order by id
    limit 80;
    --===========================================================

    --===========================================================
    explain (analyze on, buffers on)
    select id 
    from stock_move 
    where 1 =1 
    and company_id = 48
    AND date BETWEEN '2023-08-01 00:00:00' AND '2023-12-01 00:00:00'
    order by id
    limit 8000;
    --===========================================================

    --===========================================================
    create index stock_move_id_company_date_index on stock_move(id, company_id, date);
    -- drop index stock_move_id_company_date_index;

    explain (analyze on, buffers on)
    select id 
    from stock_move 
    where 1 =1 
    and company_id = 48
    AND date BETWEEN '2023-08-01 00:00:00' AND '2023-12-01 00:00:00'
    order by id
    limit 80;
    --===========================================================

Btree

Hash

GIN

GIN是缩写的广义倒排索引（Generalized Inverted Index）

GiST

SP GiST