主页 / dba / 问题 / 82420
Accepted
Joishi Bodio
Asked: 2014-11-12 13:41:50 +0800 CST

在不使用索引的函数内执行？

  • 772

我有一个accounts包含 ~200k 行的表和这些列的索引：

  account_type_id BIGINT
, member_id BIGINT
, external_id VARCHAR(64)


CREATE INDEX account_full_qualifiers_idx
ON normal_object.account (account_type_id, member_id, external_id) TABLESPACE index_tbsp;

我有一个函数正在使用以下查询执行一些 ETL 工作：

EXECUTE '
   SELECT * FROM normal_object.account
   WHERE account_type_id = $1
   AND member_id = $2
   AND external_id = $3'
INTO e_row
USING r_row.account_type_id, r_row.member_id, r_row.external_id;

然而，该EXECUTE命令正在NOT使用索引，我不确定为什么。我唯一的猜测是数据类型没有对齐。然而r_row.account_type_id是一个BIGINT，r_row.member_id是一个BIGINT，而且r_row.external_id是一个VARCHAR(64)。

关于为什么不使用索引的任何建议？
我怎样才能让它使用索引？（我已经试过enable_seqscan出发了。）

postgresql index-tuning

1 个回答

  1. Best Answer

    EXECUTE每次使用传递给它的实际参数值重新规划执行的查询。由于您使用的是 Postgres 9.2，您甚至可能不需要EXECUTE：

    无论哪种方式，升级到 Postgres 9.3（或即将推出的 9.4）可能会有更多帮助。

    所以，除非我们有类型不匹配（就像你已经怀疑的那样）或排序规则不匹配，否则应该使用你的索引，如果参数值足够选择性（检索不到表的 ~ 5%），这很可能是这种情况.

    你写：

    r_row.account_type_id是一个BIGINT，r_row.member_id是一个BIGINT，而且 r_row.external_id是一个VARCHAR(64)。

    但你怎么知道的？如果r_row声明为record，则列的数据类型在实际行的赋值中定义...
    我们需要查看带有页眉和页脚的完整 plpgsql 函数。以及确切的表定义。

    COLLATE "C"为您的字符型索引

    在任何情况下，类型列上的索引都比索引integer高效得多。varchar(64)如果您必须使用字符类型但不需要根据您的排序规则设置对列进行排序（正如我所推测的那样），那么使用COLLATE "C" 进行索引和查询会更有效。

    CREATE INDEX account_full_qualifiers_idx
ON normal_object.account (account_type_id, member_id, external_id COLLATE "C")
TABLESPACE index_tbsp;

    那么您的查询将是：

    EXECUTE '
   SELECT *
   FROM   normal_object.account
   WHERE  account_type_id = $1
   AND    member_id = $2
   AND    external_id COLLATE "C" = $3
   LIMIT  1'
INTO  e_row
USING r_row.account_type_id, r_row.member_id, r_row.external_id;

    LIMIT 1可能需要也可能不需要。你的指数不是UNIQUE，虽然......

    或者您首先定义表列COLLATE "C"。索引和查询将默认为列的排序规则。

    这可能会或可能不会解决手头的问题 - 无论如何，这对您的情况来说都是个好主意。

    此相关答案中的详细信息（“字符串和排序规则”和“索引”章节：

    • 2

相关问题