Postgresql

jsonb 數組上的 Postgres btree 部分索引 - > 數組表達式似乎在 9.5.x 版本中因較大的表而損壞

  • March 5, 2020

我正在研究 jsonb 屬性的索引，我發現 Postgres 9.5.x 有一些可疑之處，但在更高版本中沒有。以下是我所做的觸發了奇怪的查詢錯誤的操作。可能是我做錯了什麼，但是在較新的 Postgres 版本中看到這項工作讓我認為這是 9.5.x 中的一個錯誤（我已經嘗試了 9.5.21 版本）。

我一直看到這一點，表大小約為 100 萬行或更高。

jsonb 列中的 json 包含代表不同 json 類型單值和數組的屬性。我有字元串、布爾值、數字整數、數字浮點數和日期格式的字元串。我看到的錯誤是<整數數組的數組運算符（我沒有全部嘗試過）。從錯誤看來，column -> 'attribute'表達式的一部分似乎無法檢索到 jsonb 值的正確部分，並說 int 數組獲取附近的字元串數組等。這實際上在執行中發生變化，因為數據是隨機的。

列中 json 的結構對於columnproperties的每個值都是固定的（確定性的）type。所以每一行type = 8 總是有一個整數數組properties -> 'r'。type = 7具有數組 at properties -> 'q'，type = 9具有數組 atproperties -> 's'等。換句話說type，就 json in 的結構（或“模式”）而言，它是一種邏輯類型，properties並且所有具有相同值的行都type具有同質 json 結構，就節點名稱和值類型而言（值本身是隨機的）。同樣，現在數組的長度始終為 3。

這是一個錯誤嗎？還是我做錯了什麼？

CREATE TABLE test1 (
 id SERIAL PRIMARY KEY,
 type INTEGER NOT NULL,
 properties jsonb
);

-- generates test data wherein the json structure of "properties" column varies by "type" column
INSERT INTO test1 (type, properties)
SELECT
 s.type AS type,
 json_build_object(CHR(s.type + 100), md5(random() :: TEXT),
                   CHR(s.type + 101), (random() * 100)::INTEGER,
                   CHR(s.type + 102), (random() * 10)::DOUBLE PRECISION,
                   CHR(s.type + 103), random()::INTEGER::BOOLEAN ,
                   CHR(s.type + 104), to_char(to_timestamp((random() * 1500000000)::DOUBLE PRECISION), 'YYYY-MM-DD"T"HH24:MI:SS.MS"Z"'),
                   CHR(s.type + 105), ARRAY[md5(random() :: TEXT), md5(random() :: TEXT), md5(random() :: TEXT)],
                   CHR(s.type + 106), ARRAY[(random() * 100)::INTEGER, (random() * 100)::INTEGER, (random() * 100)::INTEGER],
                   CHR(s.type + 107), ARRAY[(random() * 10)::DOUBLE PRECISION, (random() * 10)::DOUBLE PRECISION, (random() * 10)::DOUBLE PRECISION],
                   CHR(s.type + 108), ARRAY[random()::INTEGER::BOOLEAN, random()::INTEGER::BOOLEAN, random()::INTEGER::BOOLEAN],
                   CHR(s.type + 109), ARRAY[
                     to_char(to_timestamp((random() * 1500000000)::DOUBLE PRECISION), 'YYYY-MM-DD"T"HH24:MI:SS.MS"Z"'),
                     to_char(to_timestamp((random() * 1500000000)::DOUBLE PRECISION), 'YYYY-MM-DD"T"HH24:MI:SS.MS"Z"'),
                     to_char(to_timestamp((random() * 1500000000)::DOUBLE PRECISION), 'YYYY-MM-DD"T"HH24:MI:SS.MS"Z"')
                   ]
 ) AS properties
FROM (SELECT (random() * 10) :: INT AS type
     FROM generate_series(1, 1000000)) s;

CREATE OR REPLACE FUNCTION jsonb_array_int_array(JSONB)
 RETURNS INTEGER[] AS
$$
DECLARE
 result INTEGER[];
BEGIN
 IF $1 ISNULL
 THEN
   result := NULL;
 ELSEIF jsonb_array_length($1) = 0
 THEN
   result := ARRAY [] :: INTEGER[];
 ELSE
   SELECT array_agg(x::INTEGER) FROM jsonb_array_elements_text($1) t(x) INTO result;
 END IF;
 RETURN result;
END;
$$
 LANGUAGE plpgsql
 IMMUTABLE;

--  properties -> 'r' field of type 8 is always an array of integers
CREATE INDEX test1_properties_r_int_array_index ON test1 USING btree (jsonb_array_int_array(properties -> 'r')) WHERE type = 8;

-- this works
SELECT count(*) FROM test1 WHERE type = 8 AND jsonb_array_int_array(properties -> 'r') < ARRAY[50];

-- this fails
SELECT count(*) FROM test1 WHERE type = 8 AND jsonb_array_int_array(properties -> 'r') < ARRAY[100];

-- but
DROP INDEX test1_properties_r_int_array_index;
-- now it works
SELECT count(*) FROM test1 WHERE type = 8 AND jsonb_array_int_array(properties -> 'r') < ARRAY[100];

-- also
CREATE INDEX test1_properties_r_int_array_index ON test1 USING gin (jsonb_array_int_array(properties -> 'r')) WHERE type = 8;

-- works here too
SELECT count(*) FROM test1 WHERE type = 8 AND jsonb_array_int_array(properties -> 'r') < ARRAY[100];

感謝您的幫助。

編輯：

這是有關它如何失敗的一些說明。我剛剛重新執行了上面的，查詢失敗如下

sql> SELECT count(*) FROM test1 WHERE type = 8 AND jsonb_array_int_array(properties -> 'r') < ARRAY[100]
[2020-03-04 00:46:20] [22P02] ERROR: invalid input syntax for integer: "1.73782130237668753"
[2020-03-04 00:46:20] Where: SQL statement "SELECT array_agg(x::INTEGER) FROM jsonb_array_elements_text($1) t(x)"
[2020-03-04 00:46:20] PL/pgSQL function jsonb_array_int_array(jsonb) line 12 at SQL statement

我從錯誤消息中掃描了隨機值

SELECT id AS txt FROM test1 WHERE position('1.73782130237668753' IN properties::text) > 0;

並發現導致錯誤的行實際上type等於 7 而不是 8，如查詢的 where 子句。因此，似乎在返回的行中不滿足索引條件。

這是失敗查詢的計劃

Aggregate  (cost=69293.65..69293.66 rows=1 width=0)
 ->  Bitmap Heap Scan on test1  (cost=1228.78..69208.38 rows=34111 width=0)
       Recheck Cond: ((jsonb_array_int_array((properties -> 'r'::text)) < '{100}'::integer[]) AND (type = 8))
       ->  Bitmap Index Scan on test1_properties_r_int_array_index  (cost=0.00..1220.25 rows=34111 width=0)
             Index Cond: (jsonb_array_int_array((properties -> 'r'::text)) < '{100}'::integer[])

編輯2：

在 Laurenz Albe 的回復之後，我進行了以下測試。我定義了一個新功能

CREATE OR REPLACE FUNCTION jsonb_array_int_array2(json_value JSONB, actual_type INTEGER, expected_type INTEGER)
 RETURNS INTEGER[] AS
$$
DECLARE
 result INTEGER[];
BEGIN
 IF actual_type <> expected_type THEN
   RAISE EXCEPTION 'unexpected type % instead of %', actual_type, expected_type;
 END IF;

 IF $1 ISNULL OR actual_type <> expected_type
 THEN
   result := NULL;
 ELSEIF jsonb_array_length(json_value) = 0
 THEN
   result := ARRAY [] :: INTEGER[];
 ELSE
   SELECT array_agg(x::INTEGER) FROM jsonb_array_elements_text(json_value) t(x) INTO result;
 END IF;
 RETURN result;
END;
$$
 LANGUAGE plpgsql
 IMMUTABLE;

我重新定義了索引並重組了查詢如下

CREATE INDEX test1_properties_r_int_array_index ON test1 USING btree (jsonb_array_int_array2(properties -> 'r', type, 8)) WHERE type = 8;

SELECT count(*) FROM test1 WHERE type = 8 AND jsonb_array_int_array2(properties -> 'r', type, 8) < ARRAY[100];

現在我得到了

[2020-03-04 09:47:34] [P0001] ERROR: unexpected type 7 instead of 8

這表明對所有行執行了一個步驟，而不僅僅是 where type = 8。這可能是計劃中的嗎

Recheck Cond: ((jsonb_array_int_array((properties -> 'r'::text)) < '{50}'::integer[]) AND (type = 8))

如果這是評估的順序，是否可以將其反轉並type = 8在之前檢查jsonb_array_int_array((properties -> 'r'::text)？

同樣從性能來看（一旦我刪除了異常檢查並重新執行），似乎整個表都被掃描了。

這是預期的嗎？

編輯3：

我意識到這現在已成為一個不同的問題，而 Laurenz Albe 出色而詳細的回答解決了“為什麼它不起作用”的原始問題。現在的問題是如何最好地開始我所追求的原始計劃。我想我將不得不將其提煉成一個單獨的問題。

謝謝！

順便說一句，正如 Laurenz 預測的那樣，我能夠使用更多數據在 Postgres 10.x 上重現該問題。

編輯4：

作為記錄，這並不特定於數組。在這種情況下，任何值的轉換最終都會因大表而失敗。因此，當這也不安全時，properties ->> 'm'它始終是整數type = 8

CREATE INDEX test1_properties_m_int_index ON test1 (((properties ->> 'm')::INTEGER)) WHERE type = 8;

和查詢

SELECT count(*) FROM test1 WHERE type = 8 AND (properties ->> 'm')::INTEGER < 50;

失敗了

[2020-03-05 09:35:24] [22P02] ERROR: invalid input syntax for integer: "["a1c815126aa058706476b21f37f60038", "450513bd0f25abf8bd39b1b4645a1427", "e51acc579414985eaa59d9bdc3dc8187"]"

這裡的教訓是，如果 json 模式未固定在跨表的列中，則無論進行什麼轉換，它都必須在不加選擇地掃描表的各個部分期間預測任何 jsonb 輸入。

這是一個有趣的問題，所以我會盡力給出一個好的答案。

簡而言之，問題在於您的函式定義，它對它必須處理的 JSON 對象的種類做出了毫無根據的假設。

錯誤解釋：

執行範例時出現的錯誤不是確定性的；這取決於您範例中的隨機數。我明白了，例如：

ERROR:  cannot get array length of a non-array
CONTEXT:  PL/pgSQL function jsonb_array_int_array(jsonb) line 6 at IF

但原因是一樣的。

觀察您的執行計劃使用Bitmap Index Scan。也就是說，PostgreSQL 在記憶體中建構了一個點陣圖，指示表中的哪些行滿足索引條件。第二步，點陣圖堆掃描，然後訪問實際的表行。

你可以想像這樣的點陣圖會消耗記憶體。現在點陣圖的記憶體量受配置參數的限制work_mem。如果work_mem太小而無法包含每個表行包含一位的點陣圖，PostgreSQL 將部分降級為每 8 KB 塊僅包含一位的“有損點陣圖”，指示該塊是否包含匹配的行。您可以在輸出中看到這一點EXPLAIN (ANALYZE)，但在您的情況下看不到，因為查詢失敗。

如果您有一個有損點陣圖，則必須重新檢查點陣圖指示的塊中的所有行以過濾掉誤報，因此您的函式將呼叫根本不在索引中的參數。

該錯誤是由

ELSEIF jsonb_array_length($1) = 0

或者

SELECT array_agg(x::INTEGER) FROM jsonb_array_elements_text($1) t(x) INTO result;

這兩行都假設參數是一個jsonb數組，第二行嘗試將數組元素轉換為integer. 您得到的實際錯誤取決於恰好由jsonb_array_int_array.

此類問題與某個 PostgreSQL 版本無關，您在 9.5 上看到它是一個巧合。也許在處理work_mem限制時發生了一些變化，或者當點陣圖變得有損時，也許隨機數恰好不同。

我的理論的證明：

增加work_mem，您會看到錯誤神奇地消失了，因為生成的點陣圖不再有損。

解決方案：

更改您的函式，使其不會因 JSON 值而不是整數數組而失敗。

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

相關問答

Postgresql

JSONB內行中數組鍵值的索引範圍比較

  • February 15, 2022
檢視問答
Postgresql

在 JSONB 記錄數組中查找包含鍵的行

  • March 30, 2021
檢視問答
Postgresql

查詢索引的 jsonb 列

  • June 4, 2020
檢視問答
Postgresql

自定義涉及數組的 jsonb 鍵排序順序

  • March 28, 2020
檢視問答
Postgresql

如何在 postgres 中的整數 json 屬性上創建索引

  • September 20, 2019
檢視問答
Postgresql

添加 JSONB 索引是否會使數據庫膨脹？

  • May 12, 2019
檢視問答