通过QUERY PLAN进行解析以返回特定的结果

Question

我正在尝试创建一个函数来估计给定超表中的行数。

select * from items where id = 'Xa4511' and "timestamp" >= date_trunc('day', now());

Custom Scan (ChunkAppend) on items    (cost=0.42..150.97 rows=76 width=160)
Chunks excluded during startup: 75
->  Index Scan using _hyper_2_2128_chunk_items_id_timestamp_idx on _hyper_2_2128_chunk  (cost=0.42..2.44 rows=1 width=136)
Index Cond: (((id)::text = 'Xa4511'::text) AND ("timestamp" >= date_trunc('day'::text, now())))

我可以在Custom Scan (ChunkAppend) on items (cost=0.42..150.97 rows=76 width=160)中得到76

但是对于这个特定的表，我更愿意循环遍历每一个扫描:-> Index Scan using _hyper_2_2128_chunk_items_id_timestamp_idx on _hyper_2_2128_chunk (cost=0.42..2.44 rows=1 width=136)，拉出每一行，因为它更准确。但是，我使用的查询总是返回null。

所以例如:

Custom Scan (ChunkAppend) on items (cost=0.29..4751.10 rows=51440 width=196)
Chunks excluded during startup: 54
->  Index Scan using _hyper_5_2121_chunk_items_id_timestamp_idx on _hyper_5_2121_chunk  (cost=0.42..1722.80 rows=19017 width=196)
Index Cond: (((id)::text = 'xb1a'::text) AND ("timestamp" >= date_trunc('month'::text, now())))
->  Index Scan using _hyper_5_2097_chunk_items_timestamp_idx on _hyper_5_2097_chunk  (cost=0.29..2944.74 rows=32369 width=196)
Index Cond: ("timestamp" >= date_trunc('month'::text, now()))
Filter: ((id)::text = 'xb1a'::text)

上面应该返回51,386 (19017 + 32369)

我的当前功能在

CREATE or replace FUNCTION count_estimate(query text) RETURNS integer AS $$
declare
counter integer := 0;
rec   record;
rows  integer;
BEGIN
FOR rec IN EXECUTE 'EXPLAIN ' || query loop
counter := counter + 1;
IF counter >= 1 THEN
rows := substring(rec."QUERY PLAN" FROM ' rows=([[:digit:]]+)');
end IF;
END LOOP;
RETURN rows;
END;
$$ LANGUAGE plpgsql VOLATILE STRICT;

Answer 1

因此，使用EXPLAIN的JSON格式要容易得多。估计行数的函数如下所示:

CREATE FUNCTION estimate_rows(query text) RETURNS numeric
LANGUAGE plpgsql STRICT AS
$$DECLARE
j json;
BEGIN
EXECUTE 'EXPLAIN (FORMAT JSON) ' || query
INTO j;
RETURN CAST(j -> 0 -> 'Plan' ->> 'Plan Rows' AS numeric);
END;$$;