sql - Spring JPA 查询总是使用序列扫描而不是索引扫描

Question

我有一个简单的查询

@Query(value = "select * from some_table where consumer_id=:consumerId and store_id=:storeId and cancelled_at is null", nativeQuery = true)
fun checkIfNewConsumer(consumerId: BigInteger, storeId: BigInteger): List<SomeClass?>

当我直接针对超过 3000 万行的表运行带有解释的查询时

在 some_table 上使用 select_index 进行索引扫描(成本=0.56..8.59 行=1 宽度=86)(实际时间=0.015..0.015 行=0 循环=1)
索引条件:((consumer_id = 1234) AND (store_id = 4) AND (cancelled_at IS NULL))
规划时间:0.130 毫秒
执行时间:0.042 毫秒

当我使用 spring boot 通过请求运行相同的查询时:

{"Plan"=>{"Total Cost"=>1317517.92, "Relation Name"=>"some_table", "Parallel Aware"=>"?", "Filter"=>"?", "Alias"=>"some_table", "Node Type"=>"Seq Scan", "Plan Width"=>86, "Startup Cost"=>0.0, "Plan Rows"=>912}}
执行时间:9613 毫秒

上面的 spring boot 计划来自 new relic。如您所见，它默认为每个查询Seq 扫描，而不是索引扫描。我已经假设它是数据库(没有骰子)进行了吸尘分析，我已经尝试了查询的变体，没有骰子。它在 plsql 中总是看起来很完美，通过 spring 失败。

如有任何建议，我们将不胜感激。

编辑 2:潜在解决方案

我们发现通过禁用准备好的语句将 ?preferQueryMode=simple 添加到您的连接 url:jdbc:postgresql://localhost:5432/postgres?preferQueryMode=simple获得使用索引扫描的查询。

我们需要了解 How?为什么？为什么是现在？

编辑 1:技术堆栈

• Spring 启动 2.0M5
• Kotlin
• PostgreSQL 9.6.2

编辑:解决方案@Vlad Mihalcea

please don't use preferQueryMode=simple unless you are absolutely sure what it means. Apparently, your problem is described in https://gist.github.com/vlsi/df08cbef370b2e86a5c1. I guess you have BigInt in the database and BigInteger in the Kotlin code. Can you use Long in Kotlin?

–Vladimir Sitnikov

Answer 1

由于 PostgreSQL 不包含任何执行计划缓存并且 PreparedStatement(s) 实际上是模拟的，直到达到给定的执行阈值(例如 5)，我认为这是一个索引选择性问题面对这里。

如果此查询仅返回少量记录，数据库将使用索引。

如果此查询将返回大量记录，数据库将不会使用索引，因为随机访问页面读取的成本将高于顺序扫描的成本。

因此，您可能在此处使用了不同的绑定(bind)参数值集。

1. 你在 pgsql 控制台中给出的那些是高度选择性的，因此你得到了索引扫描。
2. 您在运行时发送的那些可能不同，因此您会得到顺序扫描。

此外，在 pgsql 上，解释计划不会考虑将所有记录发送到 JDBC 驱动程序的网络开销。但是，这是对您的问题的补充，而不是真正的根本原因。

现在，要真正确定实际的执行计划，请尝试在 PostgreSQL 中启用 auto_explain 模式。

或者，您可以编写一个运行查询的测试方法，如下所示:

List<Object[]> executionPlanLines = doInJPA(entityManager -> {
    try(Stream<Object[]> postStream = entityManager
        .createNativeQuery(
            "EXPLAIN ANALYZE " +
            "select * from some_table where consumer_id=:consumerId and store_id=:storeId and cancelled_at is null ")
        .setParameter("consumerId", consumerId)
        .setParameter("storeId", storeId)
        .unwrap(Query.class)
        .stream()
    ) {
        return postStream.collect( Collectors.toList() );
    }
});

LOGGER.info( "Execution plan: {}",
             executionPlanLines
             .stream()
             .map( line -> (String) line[0] )
             .collect( Collectors.joining( "\n" ) )
);

这样，您将看到在生产环境中运行的实际执行计划。