主页 / dba / 问题 / 154681
Accepted
Luan Huynh
Asked: 2016-11-10 00:47:58 +0800 CST

过滤数组 text[] 并按时间戳排序

  • 772

描述

Linux 上的 PostgreSQL 9.6，tags_tmp表大小 ~ 30 GB（1000 万行），tags是一个text[]并且只有 6 个值。 

tags_tmp(id int, tags text[], maker_date timestamp, value text)

id  tags        maker_date      value
1   {a,b,c}     2016-11-09      This is test 
2   {a}         2016-11-08      This is test 
3   {b,c}       2016-11-07      This is test 
4   {c}         2016-11-06      This is test 
5   {d}         2016-11-05      This is test

我需要使用 filter ontags和order byon检索数据maker_date desc。我可以在两tags & maker_date desc列上创建索引吗？

如果没有，您能否提出其他想法？

查询示例

select id, tags, maker_date, value
from tags_tmp
where  tags && array['a','b']
order by maker_date desc
limit 5 offset 0

SQL 代码：

create index idx1 on tags_tmp using gin (tags);
create index idx2 on tags_tmp using btree(maker_date desc);

explain (analyse on, costs on, verbose)
select id, tags, maker_date, value
from tags_tmp
where tags && array['funny','inspiration']
order by maker_date desc
limit 5 offset 0 ;

解释结果：

Limit  (cost=233469.63..233469.65 rows=5 width=116) (actual time=801.482..801.483 rows=5 loops=1)
  Output: id, tags, maker_date, value
  ->  Sort  (cost=233469.63..234714.22 rows=497833 width=116) (actual time=801.481..801.481 rows=5 loops=1)
        Output: id, tags, maker_date, value
        Sort Key: tags_tmp.maker_date DESC
        Sort Method: top-N heapsort  Memory: 25kB
        ->  Bitmap Heap Scan on public.tags_tmp  (cost=6486.58..225200.81 rows=497833 width=116) (actual time=212.982..696.650 rows=366392 loops=1)
              Output: id, tags, maker_date, value
              Recheck Cond: (tags_tmp.tags && '{funny,inspiration}'::text[])
              Heap Blocks: exact=120034
              ->  Bitmap Index Scan on idx1  (cost=0.00..6362.12 rows=497882 width=0) (actual time=171.742..171.742 rows=722612 loops=1)
                    Index Cond: (tags_tmp.tags && '{funny,inspiration}'::text[])
Planning time: 0.185 ms
Execution time: 802.128 ms

更多信息

我测试了只对一个标签使用部分索引，当然，它更快。但是我有很多标签，例如：create index idx_tmp on tags_tmp using btree (maker_date desc) where (tags && array['tag1') or tags && array['tag2'] or ... or tags && array['tag6']. 我在tags && array['tag1']and之间进行了测试'tag1' = any(tags)，性能是一样的。

  1. text[]只有 6 个值 = a, b, c, d, e, f。例如：tags={a,b,c}, tags={a}, tags={a,c}, tags={a,b,c,d,e,f}, tags={b,f}等等。但它不能具有价值g->z, A-Z等。

  2. create table tags_tmp(id int primary key not null, tags text[] not null, maker_date timestamp not null, value text)

  3. 在distinct数组值方面，tags其中包含a20% 行的 table where 'a' = any(tags)， b=20% where 'b' = any(tags)， c=20% where 'c' = any(tags)， d=20% where 'd' = any(tags)， e=10% where 'e' = any(tags)，f=10% where 'f' = any(tags)。

  4. 另外，(tags, maker_date)也不是唯一的。

  5. 此表不是只读的。

  6. 是sort on timestamp，但我的示例显示了日期，对此感到抱歉。

现状：tags = 'a' or tags = 'b' or tags = 'c'还有更多

(1) with GIN indexor converttext[] to int[]以及 converttext[] to int等，它将在多标签上使用位图索引。最后，经过测试，我决定用老方案，OR改成很多UNION子句，每个子句UNION都会限制数据的数量。当然，我将为partial index每个标签值创建，并且可以与上面的 (1) 结合使用。就 而言OFFSET，它将使用一个或多个条件 inWHERE子句。

例子

EXPLAIN (ANALYSE ON, costs ON, VERBOSE)
SELECT rs.*
FROM (
        (SELECT tags,
                id,
                maker_date
         FROM tags_tmp
         WHERE 'a' = any(tags)
           AND maker_date <= '2016-03-28 05:43:57.779528'::TIMESTAMP
         ORDER BY maker_date DESC LIMIT 5)
      UNION
        (SELECT tags,
                id,
                maker_date
         FROM tags_tmp
         WHERE 'b' = any(tags)
           AND maker_date <= '2016-03-28 05:43:57.779528'::TIMESTAMP
         ORDER BY maker_date DESC LIMIT 5)
      UNION
        (SELECT tags,
                id,
                maker_date
         FROM tags_tmp
         WHERE 'c' = any(tags)
           AND maker_date <= '2016-03-28 05:43:57.779528'::TIMESTAMP
         ORDER BY maker_date DESC LIMIT 5)) rs
ORDER BY rs.maker_date DESC LIMIT 5 ;
postgresql index-tuning

2 个回答

  1. Best Answer

    一般注意事项

    索引优化总是取决于完整的画面。表大小、行大小、基数、值频率、典型查询的选择性、Postgres 版本、典型访问模式等。

    您的情况特别困难，原因有两个：

    1. WHERE和中使用的不同列ORDER BY。

    2. 使用 GIN 或 GiST 索引对数组进行过滤是最有效的，但是这两种索引类型都不会产生排序输出。手册：

      在 PostgreSQL 当前支持的索引类型中，只有 B-tree 可以产生排序输出——其他索引类型以未指定的、依赖于实现的顺序返回匹配的行。

    您可以(tags, maker_date)在甚至更多列上创建多列 GIN 索引（索引列的顺序与 GIN 索引无关）。但是您需要btree_gin安装附加模块。指示：

    ORDER BY它对你的问题的组成部分没有帮助。

    再澄清一点：OFFSET m LIMIT n通常几乎与LIMIT m+n.

    增加规格的解决方案

    你澄清了：只有 6 个不同的标签可能。这很关键。

    你的桌子很大，你的桌子定义留下了改进的空间。大小对于大桌子很重要。您的数字（30 GB，1000 万行）也表明了一个很大的平均值。行大小约为 3 KB。要么你的列比你显示的多，要么表膨胀并且需要VACUUM FULL运行（或类似的），或者你的value列很大并且 TOASTed，这将使我的改进更加有效，因为主关系被缩减到它的一半或更少：

    CREATE TABLE tags_tmp (
  id         int PRIMARY KEY -- assuming PK
, tags       int NOT NULL    -- also assuming NOT NULL
, value      text
, maker_date timestamp NOT NULL  -- NOT NULL!
);

    由于对齐填充，列的顺序是相关的。细节：

    更重要的是，这个：tags int. 为什么？

    数组有相当大的 24 字节开销（类似于一行），加上实际项目。

    因此text[]，像您演示的 1-6 个项目 ('funny', 'inspiration', ...)在 avg 上占用约 56 个字节。并且 6 个不同的值只能由 6 位信息表示（假设数组的排序顺序无关紧要）。我们可以压缩更多，但我选择了方便的integer类型（占用4 个字节），它为多达 31 个不同的标签提供空间。这为以后添加而不更改表模式留下了空间。详细理由：

    您的标签映射到位图中的位，'a'是最低有效位（右侧）：

    tag:       a | b | c | d |  e |  f
position:  0 | 1 | 2 | 3 |  4 |  5
int value: 1 | 2 | 4 | 8 | 16 | 32

    所以标签数组'{a,d,f}'映射到41. 你可以使用任意字符串代替'a'-'f'，没关系。

    为了封装逻辑，我建议使用两个辅助功能，易于扩展：

    标签->整数：

    CREATE OR REPLACE FUNCTION f_tags2int(text[])
  RETURNS int AS
$func$
SELECT bit_or(CASE x
            WHEN 'a' THEN  1
            WHEN 'b' THEN  2
            WHEN 'c' THEN  4
            WHEN 'd' THEN  8
            WHEN 'e' THEN 16
            WHEN 'f' THEN 32
            -- more?
           END)
FROM    unnest ($1) x
$func$  LANGUAGE SQL IMMUTABLE;

    整数 -> 标签：

    CREATE OR REPLACE FUNCTION f_int2tags(int)
  RETURNS text[] AS
$func$
SELECT array_remove(ARRAY [CASE WHEN $1 &  1 > 0 THEN 'a' END
                         , CASE WHEN $1 &  2 > 0 THEN 'b' END
                         , CASE WHEN $1 &  4 > 0 THEN 'c' END
                         , CASE WHEN $1 &  8 > 0 THEN 'd' END
                         , CASE WHEN $1 & 16 > 0 THEN 'e' END
                         , CASE WHEN $1 & 32 > 0 THEN 'f' END], NULL)
                         -- more? 
$func$  LANGUAGE SQL IMMUTABLE;

    这里的基础：

    为方便起见，您可以添加一个视图以将标签显示为文本数组，就像您拥有它一样：

    CREATE VIEW tags_tmp_pretty AS
SELECT id, tags
     , f_int2tags(tags) AS tags_pretty
     , maker_date, value
FROM   tags_tmp;

    现在您的基本查询可以是：

    SELECT id, tags, maker_date, value
FROM   tags_tmp
WHERE  tags & f_tags2int('{a,b}') > 0  -- any of the tags matched
ORDER  by maker_date DESC
LIMIT  5;

    使用二元 AND 运算符&。有更多的运算符来操作列。get_bit()并且set_bit()从二进制字符串操作符也很方便。

    上面的查询应该已经更快了，仅对于更小尺寸和更便宜的运营商来说，但还没有什么革命性的。为了让它更快，我们需要索引，上面还不能使用索引。

    每个标签都有一个部分索引：

    CREATE INDEX foo_tag_a ON tags_tmp(maker_date DESC) WHERE tags & 1 > 0;
CREATE INDEX foo_tag_b ON tags_tmp(maker_date DESC) WHERE tags & 2 > 0;
...
CREATE INDEX foo_tag_f ON tags_tmp(maker_date DESC) WHERE tags & 32 > 0;

    此查询与上述等效，但可以利用索引：

    SELECT *
FROM   tags_tmp_pretty
WHERE (tags & f_tags2int('{a}') > 0   -- same as tags & 1
    OR tags & f_tags2int('{e}') > 0)  -- same as tags & 32
ORDER  BY maker_date DESC
LIMIT  10;

    Postgres 可以非常有效地在一个步骤中组合多个位图索引扫描BitmapOr- 如此SQL Fiddle所示。

    您可以添加另一个索引条件以将索引限制为maker_date> 某个恒定时间戳（并在查询中重复逐字条件）以减少它们的大小（大量）。相关示例：

    更复杂：

    其他相关答案：

    或者只有 6boolean列...

    简单的 6 个布尔列可能是更好的选择。两种解决方案都有一些优点和缺点......

    CREATE TABLE tags_tmp (
  id         int PRIMARY KEY -- assuming PK
, tag_a      bool 
, tag_b      bool 
  ...
, tag_f      bool 
, value      text
, maker_date timestamp NOT NULL  -- NOT NULL!
);

    您可以定义 flags NOT NULL，具体取决于您的完整用例。

    考虑：

    部分索引很简单：

    CREATE INDEX foo_tag_a ON tags_tmp(maker_date DESC) WHERE tag_a;
CREATE INDEX foo_tag_b ON tags_tmp(maker_date DESC) WHERE tag_b;

    等等。

    您的特殊情况的替代方案

    再想一想，由于您所有的几个标签都很常见，并且将多个标签与 OR 组合起来的选择性更小，因此仅在maker_date DESC. Postgres 可以遍历索引并过滤标签上的合格行。这将与单独的布尔列而不是数组或编码整数结合使用，因为 Postgres 对单独的列有更有用的列统计信息。

    CREATE INDEX tags_tmp_date ON tags_tmp(maker_date DESC);

    接着：

    SELECT *
FROM   tags_tmp_pretty
WHERE  tag_a
   OR  tag_b
ORDER  BY maker_date DESC
LIMIT  10;

    寻呼

    您需要一个明确的结果集排序顺序，以使分页工作。我没有在这个答案中打扰，它已经太长了。通常，您会向ORDER BY. 如何使分页有效地工作：

    • 6

  2. 您的测试用例的各种问题：

    1. id现在是int8。您在最初的问题中声明它int没有很大的不同，但是为什么要开始混淆呢？它对行大小和对齐填充很重要。请记住在问题中声明您的实际、准确和完整的表定义。

    2. 测试数据中的数据分布是不现实的。您只有 6 个不同的标签组合，并且 *所有行都有 tag '1'。我假设，您的实时表格中有所有 63 种可能的组合，标签分布与您在问题中添加的一样。

    3. 您的测试表包括新旧标签列，这抵消了我所追求的对存储大小的影响。现在行大小更大。您的行大小为 124 - 164 字节，而我的测试中只有68字节（包括填充和项目标识符）。超过两倍的尺寸会有所不同。

    4. 你写size = 4163 MB。什么尺寸？

    5. 你有order by random()测试数据。你的高效餐桌真的那么随意吗？通常，您会根据时间戳对数据进行粗略排序。你的实际情况是什么？

    6. 要查看将选择哪个计划，EXPLAIN请在实际运行查询之前仅查看查询计划进行测试。使用大桌子节省大量时间。但始终提供EXPLAIN (ANALYZE, BUFFERS)此处的输出。在您的回答中（与问题相反），cost=缺少估计值。这使得很难猜出问题所在。

    但是这些问题都不能解释为什么即使使用enable_seqscan = off;也会看到顺序扫描。使用 Postgres 9.5 对我的笔记本电脑进行快速测试。第 9.6 页也应如此。

    CREATE TABLE tags_tmp(
   id         bigserial PRIMARY KEY, 
   maker_date timestamp NOT NULL,
   tags       int NOT NULL,
   value      text
);

INSERT INTO tags_tmp (tags, maker_date, value)
SELECT EXTRACT('minute' FROM ts)::int    -- int between 1 and 60 (no 61,62,63), pretty good.
     , ts + random() * interval '5 min'  -- some limited randomness
     , 'This is test on ' || EXTRACT('minute' FROM ts)
FROM   generate_series(timestamp '2016-01-01 00:00'
                     , timestamp '2016-01-13 00:00', '10 second') ts;
-- 103681 rows affected, 836 msec execution time.

-- create adapted function f_tags2int
-- create adapted function f_int2tags

CREATE INDEX tags_tmp_1 ON tags_tmp(maker_date DESC) WHERE tags &  1 > 0;
CREATE INDEX tags_tmp_2 ON tags_tmp(maker_date DESC) WHERE tags &  2 > 0;
CREATE INDEX tags_tmp_3 ON tags_tmp(maker_date DESC) WHERE tags &  4 > 0;
CREATE INDEX tags_tmp_4 ON tags_tmp(maker_date DESC) WHERE tags &  8 > 0;
CREATE INDEX tags_tmp_5 ON tags_tmp(maker_date DESC) WHERE tags & 16 > 0;
CREATE INDEX tags_tmp_6 ON tags_tmp(maker_date DESC) WHERE tags & 32 > 0;

SELECT id, tags, maker_date, value
FROM   tags_tmp
WHERE (tags & f_tags2int(array['5']) > 0 OR
       tags & f_tags2int(array['6']) > 0)
ORDER  BY maker_date DESC
LIMIT  5;

    QUERY PLAN
Limit  (cost=3811.93..3811.94 rows=5 width=38) (actual time=46.586..46.586 rows=5 loops=1)
  Buffers: shared hit=1132
  ->  Sort  (cost=3811.93..3955.93 rows=57601 width=38) (actual time=46.584..46.585 rows=5 loops=1)
        Sort Key: maker_date DESC
        Sort Method: top-N heapsort  Memory: 25kB
        Buffers: shared hit=1132
        ->  Bitmap Heap Scan on tags_tmp  (cost=607.78..2855.20 rows=57601 width=38) (actual time=13.699..27.674 rows=76032 loops=1)
              Recheck Cond: (((tags & 16) > 0) OR ((tags & 32) > 0))
              Heap Blocks: exact=864
              Buffers: shared hit=1132
              ->  BitmapOr  (cost=607.78..607.78 rows=69121 width=0) (actual time=13.549..13.549 rows=0 loops=1)
                    Buffers: shared hit=268
                    ->  Bitmap Index Scan on tags_tmp_5 cost=0.00..289.49 rows=34560 width=0) (actual time=8.745..8.745 rows=48384 loops=1)
                          Buffers: shared hit=134
                    ->  Bitmap Index Scan on tags_tmp_6 (cost=0.00..289.49 rows=34560 width=0) (actual time=4.800..4.800 rows=48384 loops=1)
                          Buffers: shared hit=134
Planning time: 3.976 ms
Execution time: 46.653 ms

    就像我已经在SQL Fiddle中演示的一样。

    您确定您正确创建了所有索引吗？

    • 1

相关问题