Re: Make the qual cost on index Filter slightly higher than qual coston index Cond.
| От | Ashutosh Bapat |
|---|---|
| Тема | Re: Make the qual cost on index Filter slightly higher than qual coston index Cond. |
| Дата | |
| Msg-id | CAG-ACPWjm1cKjSyU_kicBDMJ-LcWe=_0oGPKyodfygB6axD_4g@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: Make the qual cost on index Filter slightly higher than qual coston index Cond. (Andy Fan <zhihui.fan1213@gmail.com>) |
| Ответы |
Re: Make the qual cost on index Filter slightly higher than qual coston index Cond.
|
| Список | pgsql-hackers |
On Wed, 27 May 2020 at 04:43, Andy Fan <zhihui.fan1213@gmail.com> wrote:
You can use the attached sql to reproduce this issue, but I'm not sure you canget the above result at the first time that is because when optimizer think the2 index scan have the same cost, it will choose the first one it found, the orderdepends on RelationGetIndexList. If so, you may try drop and create j1_i_im5 index.The sense behind this patch is we still use the cost based optimizer, just when wewe find out the 2 index scans have the same cost, we prefer to use the index whichhave more qual filter on Index Cond. This is implemented by adjust the qual coston index filter slightly higher.
Thanks for the example and the explanation.
The execution time difference in your example is pretty high to account for executing the filter on so many rows. My guess is this has to do with the heap access. For applying the filter the entire row needs to be fetched from the heap. So we should investigate this case from that angle. Another guess I have is the statistics is not correct and hence the cost is wrong.
Best Wishes,
Ashutosh
В списке pgsql-hackers по дате отправления: