I have been a hibernate/spring user for over 7 years, this problem has me baffled.
I have a simple query
Code:
@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 Code:
Index Scan using composite_select_index on select_table (cost=0.56..8.58 rows=1 width=86) (actual time=0.025..0.026 rows=1 loops=1)
Output: id, created_at, updated_at, cancelled_at, consumer_id, cart_id, bool_field, bool_field2, some_uuid, store_id, subtotal
Index Cond: ((storeorder0_.consumer_id = '1234'::bigint) AND (storeorder0_.store_id = '1234'::bigint))
Execution time: 0.056 ms
When I run the same query via a request using spring boot using JPA/Hibernate: Code:
{"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 Postgres terminal, borks via spring.
Why does querying via hibernate consistently use a Seq Scan? When I copy the exact query to run against the DB directly it uses an index scan. Any advice would be highly appreciated.
- spring boot 2.0 M5 (with all its native hibernate libraries)
- spring boot 1.5.8 (with all its native hibernate libraries)
- kotlin
- indexed all search columns + a composite column (store_id & consumer_id)
Login
FAQ
Search
