Re: BUG #15717: Index not used when ordering by left joined tablecolumn

Поиск
Список
Период
Сортировка
От Alex
Тема Re: BUG #15717: Index not used when ordering by left joined tablecolumn
Дата
Msg-id 765434487.12847357.1553762456299@mail.yahoo.com
обсуждение исходный текст
Ответ на Re: BUG #15717: Index not used when ordering by left joined table column  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-bugs
Дерево обсуждения
Indeed, with an inner join the index is used.
Thanks for the assistance.



On Thursday, March 28, 2019, 3:19:30 AM GMT+2, Tom Lane <tgl@sss.pgh.pa.us> wrote:


PG Bug reporting form <noreply@postgresql.org> writes:
> The following query runs slow, as it seq scans tables, without using any
> index:
>  select
>      wp.id as id
>  from
>      web_page wp
>      left join web_url_path url on wp.id=url.page
>  order by
>      url.priority asc
>  limit 1

> I was expecting the "priority" column index would be used to retrieve first
> url then do a reverse join to get the corresponding page, and continue if no
> page is found.

It would probably do that if you used a plain join, or a right join.
But a nestloop plan cannot be driven from the inside of an outer join,
and that's the case this query presents.

            regards, tom lane

В списке pgsql-bugs по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: BUG #15717: Index not used when ordering by left joined table column
Следующее
От: PG Bug reporting form
Дата:
Сообщение: BUG #15718: DROP TABLE fails if it's partitioned by a key of a deleted enum