postgresql - 数组搜索不使用索引

Question

我有一个带有下表的 Postgres 9.2 服务器:

表 A 有一个单列 code 和一个 B 树索引:

db=> \d A
Table "public.A"
Column  |  Type  | Modifiers 
--------+--------+-----------
 code   | bigint | not null

 Indexes:
 "A_pkey" PRIMARY KEY, btree (code)

我有一个简单的 PLPGSQL 函数，如下所示，为了便于理解而进行了简化:

create or replace function list (bigint)
   RETURNS bigint[] AS '
    DECLARE
            arr        bigint[];
            c          ALIAS FOR $1;
    begin
            arr[0] = c * 1;
            arr[1] = c * 2;
            ...
            ...
            arr[10] = c * 1024;
            return arr;
END;
' language plpgsql;

我注意到如果调用该函数则不会使用索引:

db => explain select * from A where code = ANY(list(3234234234));
                           QUERY PLAN                               
------------------------------------------------------------------------
Seq Scan on A (cost=0.00..1440291398.32 rows=10 width=219)
Filter: (code = ANY (list(3234234234::bigint)))

即使我将函数的输出类型转换为 ANY(list(3234234234)::bigint[])

也不走运

当然，如果我手动创建一个列表，它会完美地工作。

db=> explain select * from A where code = ANY( '{21312,13123,1312312,1231312,123213231}' :: bigint[]); 
                                   QUERY PLAN                                       
----------------------------------------------------------------------------------------
Bitmap Heap Scan on A  (cost=538.09..558.17 rows=5 width=219)
Recheck Cond: (code = ANY ('{21312,13123,1312312,1231312,123213231}'::bigint[]))
    ->  Bitmap Index Scan on A_pkey  (cost=0.00..538.09 rows=5 width=0)
        Index Cond: (code = ANY ('{21312,13123,1312312,1231312,123213231}'::bigint[]))

为什么Postgres在使用该函数时会进行顺序扫描？
如何让它使用该函数进行索引扫描？

Answer 1

为什么没有使用索引？

该函数返回 11 个 bigint 数字，但 Postgres 并不知道，因为 PL/pgSQL 函数是查询计划器的黑匣子，并且它是VOLATILE ， 也。这告诉 Postgres 期望每次调用都有一个不同的数组。 Postgres 认为用数组中可能大量的元素直接访问表会更便宜。对于只有 5 个元素的固定数量的替代方案，期望值有所不同。

改进的功能

改用这个简单的 IMMUTABLE SQL 函数:

CREATE OR REPLACE function list (c bigint)
  RETURNS bigint[] LANGUAGE sql IMMUTABLE AS
'SELECT array_agg($1 * (2^g)::int) FROM generate_series(0, 10) g';

在几个方面更简单、更快速，而且更允许使用索引。

• 在 PL/pgSQL 中，许多赋值的成本相对较高，对于组装长数组更是如此，因为整个数组必须用每个赋值重写。顺便说一句，PL/pgSQL 中正确的赋值运算符是 := 而不是 =:

  • The forgotten assignment operator "=" and the commonplace ":="

• 默认函数波动率是VOLATILE。您的函数可以是IMMUTABLE:始终保证相同输入的相同输出。可以帮助优化重复调用。

• 你的整个语法都过时了，你根本不需要 PL/pgSQL。可以在外部查询的上下文中内联简单的 SQL 函数。

• 最后，对于嵌套在更大查询上下文中的函数，PL/pgSQL 并不总是一个好的选择。它们是查询规划器的黑匣子，充当优化障碍。详情:

  • PostgreSQL Stored Procedure Performance
  • How to get query runtime in PL/pgSQL to be as fast as console query runtime?

SQL Fiddle.

Postgres 数组索引默认从 1 开始。如果您确实需要您的数组以索引 0 开头，您可以使用此相关答案中的技术来移动它(但我不希望您需要它):

• Normalize array subscripts for 1-dimensional array so they start with 1