Postgres Pro
Downloads
Home   >   mailing lists

Re: BUG #15717: Index not used when ordering by left joined tablecolumn - Mailing list pgsql-bugs

From Alex
Subject Re: BUG #15717: Index not used when ordering by left joined tablecolumn
Date
Msg-id 765434487.12847357.1553762456299@mail.yahoo.com
Whole thread Raw
In response to Re: BUG #15717: Index not used when ordering by left joined table column  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-bugs
Tree view
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 by date:

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