主页 / dba / 问题 / 252173
Accepted
gavenkoa
Asked: 2019-10-30 14:07:37 +0800 CST

如果查询使用 JDBC 准备语句，是否应该使用 WHERE NOT NULL 的部分索引？

  • 772

假设我们在 Postgres 中创建一个部分索引来避免无用的 NULL 数据：

 CREATE INDEX my_ix ON my (col1) WHERE col1 IS NOT NULL;

我应该将 SELECT 查询写为：

SELECT * FROM my WHERE col1 = 'abc';

或作为：

SELECT * FROM my WHERE col1 = 'abc' AND col1 IS NOT NULL;

对于 JDBC 连接库，它可能如下所示：

SELECT * FROM my WHERE col1 = ?;

而且我不确定 Postgres 是否可以在没有以下情况的情况下推断索引适用性：

 AND col1 IS NOT NULL

如果 Postgres 可以为索引推断传递性，这也很有趣：

 CREATE INDEX my_ix ON my (col1) WHERE col1 > 0;

我希望我不需要写：

SELECT * FROM my WHERE col1 > ? AND col1 > 0;

如果参数是 10、23 等 (> 0)。

postgresql index

1 个回答

  1. Best Answer

    您可以使用以下脚本轻松尝试：

    CREATE TABLE my (col1) AS
   SELECT * FROM generate_series(1, 100000);

ANALYZE my;

CREATE INDEX my_null ON my (col1) WHERE col1 IS NOT NULL;

/* use a prepared statement similar to JDBC */
PREPARE stmt1(integer) AS SELECT * FROM my WHERE col1 = $1;

/* the first five executions use custom plans */
EXPLAIN (COSTS off) EXECUTE stmt1(1);
EXPLAIN (COSTS off) EXECUTE stmt1(2);
EXPLAIN (COSTS off) EXECUTE stmt1(NULL);
EXPLAIN (COSTS off) EXECUTE stmt1(4);
EXPLAIN (COSTS off) EXECUTE stmt1(5);
/* from the sixth execution on, a generic plan is used */
EXPLAIN (COSTS off) EXECUTE stmt1(6);
EXPLAIN (COSTS off) EXECUTE stmt1(NULL);

DROP INDEX my_null;

CREATE INDEX my_pos ON my (col1) WHERE col1 > 0;

/* use a prepared statement similar to JDBC */
PREPARE stmt2(integer) AS SELECT * FROM my WHERE col1 > $1;

/* the first five executions use custom plans */
EXPLAIN (COSTS off) EXECUTE stmt2(99997);
EXPLAIN (COSTS off) EXECUTE stmt2(99997);
EXPLAIN (COSTS off) EXECUTE stmt2(1);
EXPLAIN (COSTS off) EXECUTE stmt2(NULL);
EXPLAIN (COSTS off) EXECUTE stmt2(99998);
/* PostgreSQL continues to use a custom plan! */
EXPLAIN (COSTS off) EXECUTE stmt2(0);
EXPLAIN (COSTS off) EXECUTE stmt2(NULL);
EXPLAIN (COSTS off) EXECUTE stmt2(99997);

DROP TABLE my;
DEALLOCATE ALL;

    这是一个调整代码的 SQL 小提琴。

    说明：

    使用准备好的语句，PostgreSQL 可以缓存数据库会话的查询计划。使用这种不管参数值如何都保持不变的通用计划具有节省计划时间的优点。

    但 PostgreSQL 并不总是使用通用计划，因为有时生成尊重参数值的自定义计划可能会更好。

    为了决定做什么，PostgreSQL 使用以下启发式方法：

    • 前五次执行将始终使用自定义计划。

    • 如果自定义计划的估计成本不低于通用计划的估计成本，PostgreSQL 将从第六次执行开始使用通用计划。

    从 PostgreSQL v12 开始，您可以使用plan_cache_mode配置参数配置行为。

    为什么第一条语句使用通用计划？

    对于第一条语句，始终可以使用索引扫描，即使参数是NULL（因为在这种情况下无需执行任何操作）。所以 PostgreSQL 将在第五次执行后使用通用计划。

    您可以从输出$1中识别通用计划。EXPLAIN

    为什么第二个语句继续使用自定义计划？

    使用通用计划，PostgreSQL 只能使用顺序扫描，因为索引只能用于某些参数值。对于前五次执行中的一些，自定义计划比通用计划便宜得多，因此 PostgreSQL 继续使用自定义计划。

    要回答您的问题：

    如果有必要添加一个额外的WHERE条件来确保 PostgreSQL 知道它可以使用索引取决于具体情况。

    您可以添加额外的条件而不会造成不利影响，它可能会帮助优化器并且不会造成伤害。

    但即使有额外的条件，您也不能确定该索引是否会被使用。

    • 4

相关问题