database - postgresql 9.6.4 : timestamp range query on large table takes forever

Question

我需要一些帮助来分析在包含 8366.014.2 亿行的大型表上执行的查询的不良性能，根据系统负载，计算需要长达 25 分钟到一个多小时的时间。

我创建了下表，其中包含一个组合键和 3 个索引:

CREATE TABLE IF NOT EXISTS ds1records(
 userid INT DEFAULT 0,
 clientid VARCHAR(255) DEFAULT '',
 ts TIMESTAMP,
 site VARCHAR(50) DEFAULT '',
 code VARCHAR(400) DEFAULT '');

CREATE UNIQUE INDEX IF NOT EXISTS primary_idx ON records (userid, clientid, ts, site, code);
CREATE INDEX IF NOT EXISTS userid_idx ON records (userid);
CREATE INDEX IF NOT EXISTS ts_idx ON records (ts);
CREATE INDEX IF NOT EXISTS userid_ts_idx ON records (userid ASC,ts DESC);   

在 spring 批处理应用程序中，我正在执行一个如下所示的查询:

SELECT * 
  FROM records 
 WHERE userid = ANY(VALUES (2), ..., (96158 more userids) ) 
  AND ( ts < '2017-09-02' AND ts >= '2017-09-01' 
        OR ts < '2017-08-26' AND ts >= '2017-08-25' 
        OR ts < '2017-08-19' AND ts >= '2017-08-18' 
        OR ts < '2017-08-12' AND ts >= '2017-08-11') 

用户 ID 在运行时确定(ID 的数量介于 95.000 和 110.000 之间)。对于每个用户，我需要提取当天和前三个工作日的页面浏览量。查询总是返回 3-4M 行之间的行。

使用 EXPLAIN ANALYZE 选项执行查询返回以下执行计划。

Nested Loop  (cost=1483.40..1246386.43 rows=3761735 width=70) (actual time=108.856..1465501.596 rows=3643240 loops=1)
   ->  HashAggregate  (cost=1442.38..1444.38 rows=200 width=4) (actual time=33.277..201.819 rows=96159 loops=1)
     Group Key: "*VALUES*".column1
     ->  Values Scan on "*VALUES*"  (cost=0.00..1201.99 rows=96159 width=4) (actual time=0.006..11.599 rows=96159 loops=1)
   ->  Bitmap Heap Scan on records  (cost=41.02..6224.01 rows=70 width=70) (actual time=8.865..15.218 rows=38 loops=96159)
     Recheck Cond: (userid = "*VALUES*".column1)
     Filter: (((ts < '2017-09-02 00:00:00'::timestamp without time zone) AND (ts >= '2017-09-01 00:00:00'::timestamp without time zone)) OR ((ts < '2017-08-26 00:00:00'::timestamp without time zone) AND (ts >= '2017-08-25 00:00:00'::timestamp without time zone)) OR ((ts < '2017-08-19 00:00:00'::timestamp without time zone) AND (ts >= '2017-08-18 00:00:00'::timestamp without time zone)) OR ((ts < '2017-08-12 00:00:00'::timestamp without time zone) AND (ts >= '2017-08-11 00:00:00'::timestamp without time zone)))
     Rows Removed by Filter: 792
     Heap Blocks: exact=77251145
     ->  Bitmap Index Scan on userid_ts_idx  (cost=0.00..41.00 rows=1660 width=0) (actual time=6.593..6.593 rows=830 loops=96159)
           Index Cond: (userid = "*VALUES*".column1)

我已经调整了一些 Postgres 调优参数的值(不幸的是没有成功):

• effective_cache_size=15GB(可能没用，因为查询只执行一次)
• shared_buffers=15GB
• work_mem=3GB

该应用程序运行计算量大的任务(例如数据融合/数据注入(inject))并消耗大约 100GB 内存，因此系统硬件足够大，具有 125GB RAM 和 16 个内核(操作系统:Debian)。

我想知道为什么 postgres 没有在其执行计划中使用组合索引 userid_ts_idx？由于索引中的时间戳列以相反的顺序排序，我希望 postgres 使用它来为查询的范围部分找到匹配的元组，因为它可以顺序地遍历索引直到条件 ts < '2017-09-02 00:00:00 成立并返回所有值直到条件满足 ts >= 2017-09-01 00:00:00。相反，postgres 使用昂贵的位图堆扫描，如果我理解正确的话，它会进行线性表扫描。是我错误地配置了数据库设置还是我有概念上的误解？

更新

不幸的是，评论中建议的 CTE 没有带来任何改进。位图堆扫描已被顺序扫描取代，但性能仍然很差。以下是更新后的执行计划:

Merge Join  (cost=20564929.37..20575876.60 rows=685277 width=106) (actual time=2218133.229..2222280.192 rows=3907472 loops=1)
  Merge Cond: (ids.id = r.userid)
  Buffers: shared hit=2408684 read=181785
  CTE ids
    ->  Values Scan on "*VALUES*"  (cost=0.00..1289.70 rows=103176 width=4) (actual time=0.002..28.670 rows=103176 loops=1)
  CTE ts
    ->  Values Scan on "*VALUES*_1"  (cost=0.00..0.05 rows=4 width=32) (actual time=0.002..0.004 rows=4 loops=1)
  ->  Sort  (cost=10655.37..10913.31 rows=103176 width=4) (actual time=68.476..83.312 rows=103176 loops=1)
    Sort Key: ids.id
    Sort Method: quicksort  Memory: 7909kB
    ->  CTE Scan on ids  (cost=0.00..2063.52 rows=103176 width=4) (actual time=0.007..47.868 rows=103176 loops=1)
  ->  Sort  (cost=20552984.25..20554773.54 rows=715717 width=102) (actual time=2218059.941..2221230.585 rows=8085760 loops=1)
    Sort Key: r.userid
    Sort Method: quicksort  Memory: 1410084kB
    Buffers: shared hit=2408684 read=181785
    ->  Nested Loop  (cost=0.00..20483384.24 rows=715717 width=102) (actual time=885849.043..2214665.723 rows=8085767 loops=1)
          Join Filter: (ts.r @> r.ts)
          Rows Removed by Join Filter: 707630821
          Buffers: shared hit=2408684 read=181785
          ->  Seq Scan on records r  (cost=0.00..4379760.52 rows=178929152 width=70) (actual time=0.024..645616.135 rows=178929147 loops=1)
                Buffers: shared hit=2408684 read=181785
          ->  CTE Scan on ts  (cost=0.00..0.08 rows=4 width=32) (actual time=0.000..0.000 rows=4 loops=178929147)
Planning time: 126.110 ms
Execution time: 2222514.566 ms

Answer 1

如果您将该时间戳转换为日期并改为按值列表过滤，您应该得到不同的计划。

CREATE INDEX IF NOT EXISTS userid_ts_idx ON records (userid ASC,cast(ts AS date) DESC);

SELECT *
  FROM records
 WHERE userid = ANY(VALUES (2), ..., (96158 more userids) )
  AND cast(ts AS date) IN('2017-09-01','2017-08-25','2017-08-18','2017-08-11');

它是否会表现更好取决于您的数据和日期范围，因为我发现在我的案例中，即使日期值覆盖了整个表，Postgres 也会继续使用该索引(因此 seq 扫描会更好)。

Demo