I have a simple query

@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?>

When I run the query with an explain against the table of over 30 million rows directly

Index Scan using select_index on some_table  (cost=0.56..8.59 rows=1 width=86) (actual time=0.015..0.015 rows=0 loops=1)
     Index Cond: ((consumer_id = 1234) AND (store_id = 4) AND (cancelled_at IS NULL))
 Planning time: 0.130 ms
 Execution time: 0.042 ms

When I run the same query via a request using 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}}
Execution time: 9613 ms

The spring boot plan above is from new relic. As you can see it defaults to Seq scan for every query instead of an Index scan. I have vacuumed analyzed assuming it was the database (no dice), I have tried variations of the query, no dice. It always looks perfect in plsql, borks via spring.

Any advice would be highly appreciated.

• spring boot 2.0 M5

• kotlin

EDIT: Potential solution

We found out that by disabling prepared statements add ?preferQueryMode=simple to your connection url: jdbc:postgresql://localhost:5432/postgres?preferQueryMode=simple got the query to use the index scan.

We need to understand the How? Why? and Why now?