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

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(at)sss(dot)pgh(dot)pa(dot)us> wrote:

PG Bug reporting form <noreply(at)postgresql(dot)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