postgresql - Postgres ltree 查询中的 Cstring

Question

我们使用 Postgres 中的 sql using ltree 来处理一些分层数据。

然而，当我们使用 cast 语法时，查询会非常慢，查询计划显示它实际上首先转换为 cstring，然后是 lquery。

explain analyse SELECT DISTINCT
                subltree(metric, 0, 6) metric,
                FROM demo
                WHERE
                    metric ~ ('s.a.b' || '.*')::lquery;

查询计划:

Unique  (cost=144235.79..144273.81 rows=3802 width=100) (actual time=11822.107..11822.107 rows=1 loops=1)
   ->  Sort  (cost=144235.79..144245.29 rows=3802 width=100) (actual time=11822.107..11822.107 rows=1 loops=1)
         Sort Key: (subltree(metric, 0, 6))
         Sort Method: quicksort  Memory: 25kB
         ->  Seq Scan on demo  (cost=0.00..144009.71 rows=3802 width=100) (actual time=1940.149..11822.093 rows=1 loops=1)
               Filter: (metric ~ ('s.a.b.*'::cstring)::lquery)
               Rows Removed by Filter: 3714258
 Total runtime: 11822.139 ms

但是，当我们在下面使用sql时，一切似乎都很好:

explain analyse SELECT DISTINCT
                subltree(metric, 0, 6) metric,
                FROM demo
                WHERE
                    metric_name ~ (select ('s.a.b' || '.*')::lquery);

查询计划:

Unique  (cost=13294.81..13313.85 rows=3809 width=76) (actual time=0.122..0.126 rows=6 loops=1)
InitPlan 1 (returns $0)
 ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.007..0.007 rows=1 loops=1)
->  Sort  (cost=13294.79..13304.32 rows=3809 width=76) (actual time=0.121..0.122 rows=6 loops=1)
     Sort Key: metric
     Sort Method: quicksort  Memory: 26kB
     ->  Bitmap Heap Scan on demo  (cost=589.93..13068.25 rows=3809 width=76) (actual time=0.103..0.109 rows=6 loops=1)
           Recheck Cond: (metric ~ $0)
           ->  Bitmap Index Scan on metric_gist_idx  (cost=0.00..588.98 rows=3809 width=0) (actual time=0.097..0.097 rows=6 loops=1)
                 Index Cond: (metric ~ $0)
Total runtime: 0.153 ms

Answer 1

基本上，这是 ltree 扩展中的错误。

问题的核心是 I/O 例程 - 负责在 lquery 和字符串之间转换的函数 - 被错误地标记为 VOLATILE .因为这样的函数可能有副作用，Postgres 不能通过使用索引优化掉任何比较；为了保证可预测的行为，规划者需要确保在每一行都调用类型转换。

子查询则不同。在可能的情况下，Postgres 只会评估一次子查询，而不管波动性如何。例如，比较

的输出

SELECT random()
FROM generate_series(1,10);

与

SELECT (SELECT random())
FROM generate_series(1,10);

无论如何，这个bug有already been fixed在所有受支持的 Postgres 版本中，但修复不会影响现有数据库。转储/恢复应该更新扩展。或者，这应该具有相同的效果:

ALTER FUNCTION ltree_in(cstring) IMMUTABLE;
ALTER FUNCTION ltree_out(ltree) IMMUTABLE;
ALTER FUNCTION lquery_in(cstring) IMMUTABLE;
ALTER FUNCTION lquery_out(lquery) IMMUTABLE;
ALTER FUNCTION ltxtq_in(cstring) IMMUTABLE;
ALTER FUNCTION ltxtq_out(ltxtquery) IMMUTABLE;
ALTER FUNCTION ltree_gist_in(cstring) IMMUTABLE;
ALTER FUNCTION ltree_gist_out(ltree_gist) IMMUTABLE;