Postgresql

過濾數組文本並按時間戳排序

  • May 3, 2020

描述

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 ;

一般注意事項

索引優化總是取決於完整的畫面。表大小、行大小、基數、值頻率、典型查詢的選擇性、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. 如何使分頁有效地工作：

引用自：https://dba.stackexchange.com/questions/154681

相關問答

Postgresql

為什麼這個帶有 WHERE、ORDER BY 和 LIMIT 的查詢這麼慢？

  • January 29, 2021
檢視問答
Postgresql

使用 GIN 索引位串

  • March 22, 2022
檢視問答
Postgresql

為什麼 ASC 比 DESC 快 100 倍，我該怎麼辦？

  • April 27, 2021
檢視問答
Postgresql

Postgres中索引列的查詢非常慢

  • March 29, 2021
檢視問答
Postgresql

如何加快查詢時間序列中的最後一個值？

  • February 28, 2021
檢視問答
Postgresql

PostgreSQL 性能 (col = value or col is NULL)

  • January 7, 2019
檢視問答