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

Yes, we tested with CTE as well but we are using Hibernate to generate the query and there are some more conditions that can be added if certain parameters supplied. For my knowledge, Hibernate is still not supporting CTE structures yet. That's why I will keep this as last resort to convert it to native query but much appreciated for the info you gave how query planner is thinking.

explain (analyze, buffers)
with cte 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 '%sahby%')))
)
select * from cte
order by cte.id desc limit 30

 QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=34171.73..34171.80 rows=30 width=1237) (actual time=321.370..321.371 rows=4 loops=1)
   Buffers: shared hit=18 read=1680
   CTE cte
     ->  Nested Loop  (cost=3384.39..33967.93 rows=5155 width=241) (actual time=309.167..321.312 rows=4 loops=1)
           Buffers: shared hit=15 read=1680
           ->  Bitmap Heap Scan on customer customer1_  (cost=3383.96..11612.18 rows=5155 width=4) (actual time=302.196..310.625 rows=4 loops=1)
                 Recheck Cond: (lower((((first_name)::text || ' '::text) || (last_name)::text)) ~~ '%sahby%'::text)
                 Heap Blocks: exact=3
                 Buffers: shared hit=5 read=1674
                 ->  Bitmap Index Scan on idx_customer_name_lower  (cost=0.00..3382.67 rows=5155 width=0) (actual time=300.142..300.142 rows=4 loops=1)
                       Index Cond: (lower((((first_name)::text || ' '::text) || (last_name)::text)) ~~ '%sahby%'::text)
                       Buffers: shared hit=5 read=1671
           ->  Index Scan using idx_booking_customer_id on booking booking0_  (cost=0.43..4.33 rows=1 width=241) (actual time=2.666..2.667 rows=1 loops=4)
                 Index Cond: (customer_id = customer1_.id)
                 Buffers: shared hit=10 read=6
   ->  Sort  (cost=203.80..216.69 rows=5155 width=1237) (actual time=321.368..321.369 rows=4 loops=1)
         Sort Key: cte.id
         Sort Method: quicksort  Memory: 25kB
         Buffers: shared hit=18 read=1680
         ->  CTE Scan on cte  (cost=0.00..51.55 rows=5155 width=1237) (actual time=309.173..321.327 rows=4 loops=1)
               Buffers: shared hit=15 read=1680
 Planning time: 92.501 ms
 Execution time: 321.521 ms

Regards,