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

From: Alex <cdalxndr(at)yahoo(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-bugs(at)lists(dot)postgresql(dot)org
Subject: Re: BUG #15717: Index not used when ordering by left joined table column
Date: 2019-03-28 08:40:56
Message-ID: 765434487.12847357.1553762456299@mail.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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(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

In response to

Browse pgsql-bugs by date

  From Date Subject
Next Message PG Bug reporting form 2019-03-28 13:19:02 BUG #15718: DROP TABLE fails if it's partitioned by a key of a deleted enum
Previous Message Tom Lane 2019-03-28 01:19:22 Re: BUG #15717: Index not used when ordering by left joined table column