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
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 |