sql - 空间优化查询运行速度比预期慢

Question

我有一个包含日级数据的表。像下面这样的东西

CREATE TABLE test  
(
    id_A INTEGER, -- from 1 to 1k
    id_B INTEGER, -- from 1 to 1k
    nu_B DOUBLE PRECISION,  -- b/w 1 to 8 
    dt_date DATE -- date of activity
);

在尝试优化此表上选择查询的性能时
(我们称之为 aggregation_query )

SELECT id_A, dt_date, count(*), sum(nu_B), count(DISTINCT (id_A, dt_date)) 
FROM test
GROUP BY id_A, dt_date;

我观察到一种有助于减小 table 尺寸的模式。也就是说，在给定的月份中，有一天或多天的 id_A、id_B 和 nu_B 具有相同的值。

例如

id_A, id_B, nu_B, dt_date
23, 24, 5, 2019-12-12
23, 24, 5, 2019-12-15
23, 24, 5, 2019-12-18
29, 27, 2, 2019-12-03 
29, 27, 2, 2019-12-09 
29, 27, 2, 2019-12-11 
29, 27, 2, 2019-12-29 

可以使用按位表示一个月中的天数并仅存储此类重复事件的月份并通过使用整数存储实际发生事件的天数(通过设置该月事件发生的天数)来简化，例如压缩后上表将如下所示

id_A, id_B, nu_B, dt_month, bit_days
23,  24,   5,  2019-12-01,   149504     #000000000000010100100000000000
29,  27,   2,  2019-12-01,   268436740  #0010000000000000000010100000100

通过这种方法，我们能够将表的大小减少大约 50-55%，并且可以通过应用具有一系列 1-31 个整数的连接来扩展以这种压缩格式存储的数据，然后检查实际进行了哪一天的事件或不。类似于以下查询-:

SELECT  *, (dt_month + INTERVAL '1 day' * a.n)::DATE as dt_date
FROM test_compressed tc 
INNER JOIN generate_series(1, 31) a(n) ON (tc.nu_bit_days & (1 << a.n)) > 0

但是当我们尝试重写上面的 时聚合查询使用此表的选择查询开始花费的时间是原始查询的 3 倍，原始查询由于表的整体空间减少而预计花费的时间更少。

这是一个 sqlfiddle快速测试一下。我检查了这两种情况下的查询计划，但我没有得到太多关于为什么第二个查询比前一个查询慢的线索。

你能检查一下并建议这里出了什么问题吗？有没有办法让它比以前更快？

提前致谢!

Edit1:不知何故，sqlfiddle 中的查询无法正常进行，因此在此处提供它们。

您可以使用- 构建架构:

DROP TABLE IF EXISTS test;

CREATE TABLE test 
(
    id_A INTEGER, -- from 1 to 1k
    id_B INTEGER, -- from 1 to 1k
    nu_B DOUBLE PRECISION,  -- num b/w 1 to 8 
    dt_date DATE -- date of activity
);

INSERT INTO test (id_A, id_B, nu_B, dt_date)
  SELECT greatest(1, random()*50)::INT, -- select a num b/w 1 and 100
    greatest(1, random()*50)::INT,   -- select a num between 1 and 100
    (greatest(1, round(8*random()) )),  -- select a num between 1 and 20
    now()::DATE - (a.n%500)   -- assign a date as per loop counter module within last 500 days
  FROM generate_series(1, 500000) as a(n);


 drop table if EXISTS test_compressed;

SELECT
    id_A,
    id_B,
    nu_B,
    date_trunc('month', dt_date) :: DATE as dt_month,
    sum( DISTINCT (1 << (date_part('day', dt_date)::INT) -1) ) as nu_bit_days 
INTO 
    test_compressed
FROM 
    test
GROUP BY 
    id_A, id_B, nu_B, date_trunc('month', dt_date) :: DATE ;

然后执行这些查询作为测试以验证时间。

SELECT count(*) FROM test; 
SELECT count(*) from test_compressed;

EXPLAIN ANALYSE SELECT id_A, dt_date, count(*), sum(nu_B), count(DISTINCT (id_A, dt_date)) from test
GROUP BY id_A, dt_date;

EXPLAIN ANALYSE
SELECT id_A, dt_date, count(*), sum(nu_B), count(DISTINCT (id_A, dt_date))
FROM (SELECT  *, (dt_month + INTERVAL '1 day' * a.n)::DATE as dt_date
      FROM test_compressed tc INNER JOIN generate_series(1, 31) a(n)
          ON (tc.nu_bit_days & (1 << a.n)) > 0) as V1
GROUP BY id_A, dt_date;;

Answer 1

我在架构构建脚本的末尾添加了“VACUUM ANALYZE”。然后，在这个查询中:

EXPLAIN ANALYZE SELECT id_A, dt_date, count(*), sum(nu_B), 
    count(DISTINCT (id_A, dt_date)) from test
GROUP BY id_A, dt_date;

GROUP BY id_A, dt_date意味着 count(DISTINCT (id_A, dt_date))将始终为 1 ，因此我将从查询中删除它。现在，让我们试试...

CREATE INDEX test_idx_1 ON test( id_a, dt_date, nu_B );

这会在您的大表测试中启用仅索引扫描并加快查询速度(但它不会回答您的问题，并且它将使用磁盘空间作为索引，因此如果您想压缩表以使用更少的磁盘空间，也许不是一个好主意)。

一种简单的方法是创建一个物化 View 并按月汇总所有过去的数据，包括 sum()。但是，这仍然不会压缩表格，如果您的问题是真实表格的简化版本并且不起作用，那么让我们继续...

我修改了架构创建脚本以使用 100k 行而不是 500k 行，因为 sqlfiddle 不想运行长查询。

SELECT sum(nu_b) FROM test;

我只是运行它来检查扫描表格需要多长时间:14ms。理想情况下，我们希望压缩表在相似的时间内解压缩和扫描，可能更慢但不要太多。让我们试试吧。

SELECT sum(nu_b) FROM (
  SELECT  *, (dt_month + INTERVAL '1 day' * a.n)::DATE as dt_date
  FROM test_compressed tc INNER JOIN generate_series(1, 31) a(n)
      ON (tc.nu_bit_days & (1 << a.n)) > 0) as V1;

500ms，慢得多!这是个问题。

另一个问题是它不会给出相同的结果，这意味着查询或数据是错误的。这是因为 “test”中的数据包含相同(id_a、id_b、nu_b、dt_date)的重复行它们被压缩成一个比特，因此丢失了重复信息。所以我修改了模式脚本以删除重复项。

这意味着我会尝试 用数组替换位域 ，如果您需要它们，它可以处理重复项。压缩表中的位域列替换为:

array_agg( DISTINCT ( date_part('day', dt_date)::SMALLINT) ) as nu_days 

然后我会计算打开 table 需要多长时间，再次通过在它周围包裹一个聚合...

select sum(nu_b) from (
SELECT id_a, id_b, nu_b, dt_month, unnest( nu_days ) as nu_days
  FROM test_compressed_inta )

55 毫秒，比之前的位域解决方案快 10 倍。请注意设置返回函数 unnest() 完成所有工作。

解包位域压缩表可以通过两种方法完成:

SELECT  id_a, id_b, nu_b, dt_month, a.n as nu_days
FROM test_compressed tc INNER JOIN generate_series(1, 31) a(n)
ON (tc.nu_bit_days & (1 << (a.n-1))) > 0 )

注意“a.n-1”修复了原始查询中的 1 个错误。或者:

SELECT * FROM (
  SELECT id_a, id_b, nu_b, dt_month, generate_series( 1,32 ) as nu_days
  FROM test_compressed ) q1
WHERE nu_bit_days & (1 << (nu_days-1))::INT > 0

在这种情况下，必须为一个月中的每一天生成并过滤掉一行，而数组已经包含所需的结果。因此，如果每个月中的几天包含每个(id_a、id_b、nu_b、dt_month)的数据，则此方法要慢得多(大约 10 倍)。如果几个月相当“满”，那问题就不大了。表大小是相反方向的折衷方案，因为存储包含许多天数的长数组将比位域占用更多空间。您可以对实际数据使用 pg_relation_size() 进行调查。

如果您希望位域与数组一样快，您需要一个类似于 unnest() 的快速设置返回函数，该函数返回设置为 1 的位的位置。我将暂时保留该数组。

现在这只是关于解压缩表。这不包括您的聚合查询。

您的聚合查询的问题在于它生成一个完整的排序然后一个 GroupAggregate(检查 EXPLAIN ANALYZE 结果)。在原始表中，这可以通过仅索引扫描来避免，但这在压缩表上是不可能的，因为月份中的某一天包含在 GROUP BY 中(因此也包含在排序中)。

幸运的是，将 work_mem 增加到 32 MB 可以说服 Postgres 使用散列聚合而不是排序。没关系，没有那么多带有 1k id_a 和日期的桶。

SELECT id_A, dt_month, nu_days, count(*) c, sum(nu_B) b FROM (
SELECT id_a, id_b, nu_b, dt_month, unnest( nu_days ) as nu_days
  FROM test_compressed_inta ) q1
GROUP BY id_A, dt_month, nu_days;

Original table using index-only scan      55ms
Arrays, with hash (high work_mem)       ~100ms
Arrays, with sort (low work_mem)        ~300ms

位域比较慢，用上面的查询解包，需要600-700ms，其中大部分时间都花在了每天生成行并过滤掉...

注意我删除了 (dt_month + INTERVAL '1 day' * a.n)::DATE as dt_date从内部循环开始，因为在 (dt_month,nu_days) 上聚合更快，然后再重建日期。