-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 2 posts ] 
Author Message
 Post subject: Poor query performance
PostPosted: Wed Nov 22, 2017 11:02 pm 
Newbie

Joined: Wed Nov 22, 2017 10:47 pm
Posts: 1
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)


Top
 Profile  
 
 Post subject: Re: Poor query performance
PostPosted: Thu Nov 23, 2017 2:54 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1630
Location: Romania
Check out my answer on StackOverflow.


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 2 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.