On our production environment (PostgreSQL 9.4.5 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-4), 64-bit), one of our queries runs very slow, about 5 minutes . We noticed that it does not use an index that we anticapited it would.
The query is
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%' ) order by booking0_.id desc limit 30;
It thinks it is going to find 30 rows which meet your condition very quickly, so by walking the index backwards it can avoid needing to do a sort. But, the rows which meet your sub-select conditions are biased towards the front of the index, so in fact it was to walk backwards through most of your index before finding 30 eligible rows.
Your best bet is probably to force it into the plan you want by using a CTE:
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;
