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

On Mon, Nov 14, 2016 at 4:01 AM, Seckin Pulatkan <seckinpulatkan(at)gmail(dot)com>
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