Re: Query planner chooses index scan backward instead of better index option

[Seckin Pulatkan <seckinpulatkan@xxxxxxxxx>]

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