我正在研究 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 对象的种类做出了毫无根据的假设。
错误解释:
运行示例时出现的错误不是确定性的;这取决于您示例中的随机数。我明白了,例如:
但原因是一样的。
观察您的执行计划使用Bitmap Index Scan。也就是说,PostgreSQL 在内存中构建了一个位图,指示表中的哪些行满足索引条件。第二步,位图堆扫描,然后访问实际的表行。
你可以想象这样的位图会消耗内存。现在位图的内存量受配置参数的限制
work_mem
。如果work_mem
太小而无法包含每个表行包含一位的位图,PostgreSQL 将部分降级为每 8 KB 块仅包含一位的“有损位图”,指示该块是否包含匹配的行。您可以在输出中看到这一点EXPLAIN (ANALYZE)
,但在您的情况下看不到,因为查询失败。如果您有一个有损位图,则必须重新检查位图指示的块中的所有行以过滤掉误报,因此您的函数将调用根本不在索引中的参数。
该错误是由
或者
这两行都假设参数是一个
jsonb
数组,第二行尝试将数组元素转换为integer
. 您得到的实际错误取决于恰好由jsonb_array_int_array
.此类问题与某个 PostgreSQL 版本无关,您在 9.5 上看到它是一个巧合。也许在处理
work_mem
限制时发生了一些变化,或者当位图变得有损时,也许随机数恰好不同。我的理论的证明:
增加
work_mem
,您会看到错误神奇地消失了,因为生成的位图不再有损。解决方案:
更改您的函数,使其不会因 JSON 值而不是整数数组而失败。