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

[Jeff Janes <jeff.janes@xxxxxxxxx>]

On Mon, Nov 14, 2016 at 4:01 AM, Seckin Pulatkan <seckinpulatkan@xxxxxxxxx> wrote:

Hi,

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;

Cheers,

Jeff