After Jeff Janes' reply, I have tried a couple of limit values and found at
the current state of data, 90 was a change on the query planner.
explain (analyze, buffers)
select booking0_.*
from booking booking0_
where (booking0_.customer_id in (select customer1_.id from
customer customer1_ where (lower((customer1_.first_name||'
'||customer1_.last_name)) like '%sahby%')))
order by booking0_.id desc limit 90;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=34267.44..34267.66 rows=90 width=241) (actual
time=20.140..20.141 rows=4 loops=1)
Buffers: shared hit=1742
-> Sort (cost=34267.44..34280.33 rows=5157 width=241) (actual
time=20.139..20.140 rows=4 loops=1)
Sort Key: booking0_.id
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=1742
-> Nested Loop (cost=3478.41..34074.26 rows=5157
width=241) (actual time=20.079..20.117 rows=4 loops=1)
Buffers: shared hit=1742
-> Bitmap Heap Scan on customer customer1_
(cost=3477.98..11709.61 rows=5157 width=4) (actual time=20.055..20.063
rows=4 loops=1)
Recheck Cond: (lower((((first_name)::text ||
' '::text) || (last_name)::text)) ~~ '%sahby%'::text)
Heap Blocks: exact=3
Buffers: shared hit=1726
-> Bitmap Index Scan on
idx_customer_name_lower (cost=0.00..3476.69 rows=5157 width=0)
(actual time=20.024..20.024 rows=4 loops=1)
Index Cond: (lower((((first_name)::text
|| ' '::text) || (last_name)::text)) ~~ '%sahby%'::text)
Buffers: shared hit=1723
-> Index Scan using idx_booking_customer_id on
booking booking0_ (cost=0.43..4.33 rows=1 width=241) (actual
time=0.008..0.008 rows=1 loops=4)
Index Cond: (customer_id = customer1_.id)
Buffers: shared hit=16
Planning time: 0.431 ms
Execution time: 20.187 ms
So instead of converting Criteria api query into Native query to use CTE as
suggested by Jeff :
{quote}
with t as
(select booking0_.*
from booking booking0_
where booking0_.customer_id in (
select customer1_.id
from customer customer1_
where lower((customer1_.first_name||'
'||customer1_.last_name)) like '%gatef%'
) select * from t order by booking0_.id desc limit 30;
{quote}
I have used a limit of 500 (just to be far away from 90 when table size is
increased) and then take top 30 on Java layer.
Thanks,
Seckin
