主页 / dba / 问题 / 317137
Accepted
Gismat Husein
Asked: 2022-09-21 03:50:15 +0800 CST

如何优化 PostgreSQL 中十亿行的 Where 子句？

  • 772

所以这是我第一次接触大数据。我有一张ticketing_system表，我在表中插入了一百万（十亿）个假数据。

CREATE TABLE ticketing_system (
    id BIGSERIAL,
    ticket_id UUID not null default uuid_generate_v4(),
    count int,
    created_at timestamptz NOT NULL
);

ticket_id搜索with子句时，我需要大约 10-30 毫秒的 Postgresql 响应。我where创建了 BRIN 索引 CREATE INDEX in_ticketing_system_brin ON ticketing_system USING brin(ticket_id);。但这对我没有帮助。

explain analyze select * from ticketing_system where ticket_id = '09830cb7-37f2-4951-8910-1661b1358b99';
                                                                        QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1217.39..1854198.38 rows=1 width=36) (actual time=997.056..84730.718 rows=1 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Bitmap Heap Scan on ticketing_system  (cost=217.39..1853198.28 rows=1 width=36) (actual time=56707.851..84601.029 rows=0 loops=3)
         Recheck Cond: (ticket_id = '09830cb7-37f2-4951-8910-1661b1358b99'::uuid)
         Rows Removed by Index Recheck: 46103039
         Heap Blocks: lossy=366097
         ->  Bitmap Index Scan on in_ticketing_system_brin  (cost=0.00..217.39 rows=3544816 width=0) (actual time=153.203..153.209 rows=11525760 loops=1)
               Index Cond: (ticket_id = '09830cb7-37f2-4951-8910-1661b1358b99'::uuid)
 Planning Time: 3.167 ms
 JIT:
   Functions: 6
   Options: Inlining true, Optimization true, Expressions true, Deforming true
   Timing: Generation 12.892 ms, Inlining 491.333 ms, Optimization 92.106 ms, Emission 91.551 ms, Total 687.882 ms
 Execution Time: 84833.538 ms
(15 rows)
postgresql postgresql-performance

2 个回答

  1. 索引通过BRIN记录块范围的最小值和最大值来工作。因此，除非数据具有某种趋势，例如被聚类，否则它无济于事。在您的情况下，两者HASH或都BTREE有效。我相信HASH当数据数量变大时，索引的内存占用会更小，但要注意它的局限性（例如 PostgreSQL 不支持多列HASH索引）。

    另一种方法是使用整数 ID 而不是 UUID 并按顺序创建它们。您SERIAL已经用作主键，但根据列的含义，选择仅筛选的数据可能有意义，也可能没有意义id。

    • 4
  2. Best Answer

    BRIN 索引不适合您的情况，您需要一个 B-tree 索引：

    CREATE INDEX ON ticketing_system (ticket_id);

    该索引将比 BRIN 索引大得多，但不会对您的 RAM 造成任何压力。无论索引的大小如何，索引扫描都只会使用可忽略不计的内存量。所有表和索引都以 8kB 为单位存储和缓存，索引扫描不必触及其中的许多内容。

    • 3

相关问题