Re: Query planner chooses index scan backward instead of better index option
| От | Jeff Janes |
|---|---|
| Тема | Re: Query planner chooses index scan backward instead of better index option |
| Дата | |
| Msg-id | CAMkU=1yG6HnPuj0B4khnsDE4oFM8AWAQd8_xxjk03tYJz_CSBw@mail.gmail.com обсуждение исходный текст |
| Ответ на | Query planner chooses index scan backward instead of better index option (Seckin Pulatkan <seckinpulatkan@gmail.com>) |
| Ответы |
Re: Query planner chooses index scan backward instead of
better index option
|
| Список | pgsql-performance |
On Mon, Nov 14, 2016 at 4:01 AM, Seckin Pulatkan <seckinpulatkan@gmail.com> wrote:
where booking0_.customer_id in (
select customer1_.id
from customer customer1_
where lower((customer1_.first_name||
) select * from t order by booking0_.id desc limit 30;
The query isHi,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.
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||
) select * from t order by booking0_.id desc limit 30;
Cheers,
Jeff
В списке pgsql-performance по дате отправления: