使用文本范围集过滤WHERE子句中的行

Question

我有一张有上百万行10 s的桌子。各种复杂的筛选查询产生行集以支持应用程序。这些行集具有任意大小，从单个行一直到包含完整的表。然而，由于特定领域的原因，它们总是保持高度的连续性，沿着特定的键.

我需要双向地在数据库和应用程序之间传递这些行集，最好以某种方式压缩它。您中的许多人可能熟悉UNIX，它接受类似于cut的字段规范：cut -f 2-6,7,9-21，并返回相应的列。目前，我正在使用一个稍微有限的剪切字段规范版本(例如，没有17-)来表示行集。因此，例如，24-923817,2827711-8471362,99188271表示一组独特的6567445行，同时占用34个字节。

我已经编写了以下过程，使用中间语法将它们转换为SQL过滤器

CREATE OR REPLACE FUNCTION cut_string_to_sql_filter( TEXT, TEXT ) RETURNS TEXT AS $$
SELECT
    CASE $1
        WHEN '' THEN 'FALSE'
        ELSE
            (SELECT
                '(' || STRING_AGG( REGEXP_REPLACE( REGEXP_REPLACE( str, '(\d+)-(\d+)', QUOTE_IDENT( $2 ) || ' BETWEEN \1 AND \2' ), '^(\d+)$', QUOTE_IDENT( $2 ) || '=\1' ), ' OR ' ) || ')' AS sql
                FROM
                    REGEXP_SPLIT_TO_TABLE( $1, ',' ) AS t(str))
        END;
$$ LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE;

第一个参数是行集规范，第二个参数是表的键字段名。对于上面的示例，SELECT cut_string_to_sql_filter( '24-923817,2827711-8471362,99188271', 'some_key' )返回：

(some_key BETWEEN 24 AND 923817 OR some_key BETWEEN 2827711 AND 8471362 OR some_key=99188271)

这方面的问题是，目前任何使用此类行集规范的查询都必须使用动态SQL，因为我想不出使用自定义运算符或任何其他语法特性将此效果嵌入普通SQL查询的方法。

我还为行规范编写了一个集返回函数：

CREATE OR REPLACE FUNCTION cut_string_to_set( TEXT ) RETURNS SETOF INTEGER AS $$
DECLARE
    _i TEXT;
    _j TEXT;
    _pos INTEGER;
    _start INTEGER;
    _end INTEGER;
BEGIN
    IF $1 <> '' THEN
        FOR _i IN SELECT REGEXP_SPLIT_TO_TABLE( $1, ',' ) LOOP
            _pos := POSITION( '-' IN _i );
            IF _pos > 0 THEN
                _start := SUBSTRING( _i FROM 1 FOR _pos - 1 )::INTEGER;
                _end := SUBSTRING( _i FROM _pos + 1 )::INTEGER;
                FOR _j IN _start.._end LOOP
                    RETURN NEXT _j;
                END LOOP;
            ELSE
                RETURN NEXT _i;
            END IF;
        END LOOP;
    END IF;
END
$$ LANGUAGE PLPGSQL IMMUTABLE STRICT PARALLEL SAFE;

这在使用WHERE some_key IN (SELECT cut_string_to_set(...))的普通SQL中是可行的。当然，在将最好表示给规划师的内容解压缩为一组范围时，效率相对较低，会产生噩梦般的详细查询计划，并且可能或不可能阻止计划者使用索引，而如果不是这样的话，它可以也不应该使用索引。

有谁能为上述难题提供任何解决方案，将其封装为自己的类型，并可能使用自定义操作符，从而允许在涉及范围更广的查询中不使用动态SQL对列进行语法上合理的索引过滤？难道这根本不可能吗？

如果你看到任何机会，可以随时提出改进程序的建议。还有谢谢！

编辑1

下面的好答案建议使用一系列范围类型。不幸的是，查询规划者似乎不愿意在这样的查询中使用索引。下面的计划输出从一个小测试表上运行。

Gather  (cost=1000.00..34587.33 rows=38326 width=45) (actual time=0.395..112.334 rows=1018 loops=1)
Workers Planned: 6
Workers Launched: 6
->  Parallel Seq Scan on test  (cost=0.00..29754.73 rows=6388 width=45) (actual time=91.525..107.354 rows=145 loops=7)
        Filter: (test_ref <@ ANY ('{"[24,28)","[29,51)","[999,1991)"}'::int4range[]))
        Rows Removed by Filter: 366695
Planning time: 0.214 ms
Execution time: 116.779 ms

CPU成本(在小型测试表上并行处理超过100 ms的6个工作人员)太高。我看不出有什么额外的索引能在这里有所帮助。

相反，下面是使用中间过滤器的计划器输出。

Bitmap Heap Scan on test  (cost=22.37..1860.39 rows=1031 width=45) (actual time=0.134..0.430 rows=1018 loops=1)
Recheck Cond: (((test_ref >= 24) AND (test_ref <= 27)) OR ((test_ref >= 29) AND (test_ref <= 50)) OR ((test_ref >= 999) AND (test_ref <= 1990)))
Heap Blocks: exact=10
->  BitmapOr  (cost=22.37..22.37 rows=1031 width=0) (actual time=0.126..0.126 rows=0 loops=1)
        ->  Bitmap Index Scan on test_test_ref_index  (cost=0.00..2.46 rows=3 width=0) (actual time=0.010..0.010 rows=4 loops=1)
            Index Cond: ((test_ref >= 24) AND (test_ref <= 27))
        ->  Bitmap Index Scan on test_test_ref_index  (cost=0.00..2.64 rows=21 width=0) (actual time=0.004..0.004 rows=22 loops=1)
            Index Cond: ((test_ref >= 29) AND (test_ref <= 50))
        ->  Bitmap Index Scan on test_test_ref_index  (cost=0.00..16.50 rows=1007 width=0) (actual time=0.111..0.111 rows=992 loops=1)
            Index Cond: ((test_ref >= 999) AND (test_ref <= 1990))
Planning time: 0.389 ms
Execution time: 0.660 ms

终端编辑1

编辑2

下面的答案建议使用范围索引。据我所知，问题在于我不需要索引范围类型。好的，也许键列被转换成一个操作范围，所以我可以对它应用一个GIST索引，规划者将使用它。

CREATE INDEX test_test_ref_gist_index ON test USING GIST (test_ref);
ERROR:  data type integer has no default operator class for access method "gist"
HINT:  You must specify an operator class for the index or define a default operator class for the data type.

这里没什么好惊讶的。因此，让我们将键列转换为一个范围，并对其进行索引。

CREATE INDEX test_test_ref_gist_index ON test USING GIST (INT4RANGE( test_ref, test_ref ));

一个110 MB的索引。好大啊。但它有用吗。

Gather  (cost=1000.00..34587.33 rows=38326 width=45) (actual time=0.419..111.009 rows=1018 loops=1)
Workers Planned: 6
Workers Launched: 6
->  Parallel Seq Scan on test_mv  (cost=0.00..29754.73 rows=6388 width=45) (actual time=90.229..105.866 rows=145 loops=7)
        Filter: (test_ref <@ ANY ('{"[24,28)","[29,51)","[999,1991)"}'::int4range[]))
        Rows Removed by Filter: 366695
Planning time: 0.237 ms
Execution time: 114.795 ms

不是的。我一点也不惊讶。我希望此索引适用于“包含”操作，而不是“包含”操作。不过，我在这里没有经验。

终端编辑2

Answer 1

传递一个范围数组：

select *
from t
where
    k <@ any (array[
        '[24,923817]','[2827711,8471362]','[99188271,99188271]'
    ]::int4range[])

检查范围类型的索引：https://www.postgresql.org/docs/current/static/rangetypes.html#RANGETYPES-INDEXING

如果无法使用适当的范围索引，则对物化范围进行连接：

select *
from
    t
    inner join
    (
        select generate_series(lower(a),upper(a) - 1) as k
        from unnest(array[ 
            '[24,27]','[29,50]','[999,1990]'
        ]::int4range[]) a(a)
    ) s using (k)

可以避免加入所有范围值。与范围的下界和上限相比：

select *
from
    t
    cross join
    (
        select lower(a) as l, upper(a) - 1 as u
        from unnest(array[
            '[24,27]','[29,50]','[999,1990]'
        ]::int4range[]) a(a)
    ) s
where k between l and u

Answer 2

根本不可能。操作员不会那样做的。他们称其为函数。如果他们在这里调用一个函数，那么函数就必须使用动态SQL。

若要不使用动态SQL，则必须破解PostgreSQL词法器。PostgreSQL是一个SQL数据库。您的语法不是SQL。你可以做两件事

1. 使用SQL。
2. 编译SQL。

如果可能，我更喜欢第一种选择。如果我需要做一个DSL，我不做它在PostgreSQL。我在应用程序里做的。