From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | postgresql(dot)20(dot)drkshadow(at)spamgourmet(dot)com |
Cc: | pgsql-bugs(at)lists(dot)postgresql(dot)org |
Subject: | Re: BUG #16504: Wrapping query in EXISTS() causes sequential scans of tables |
Date: | 2020-06-19 14:09:06 |
Message-ID: | 1647563.1592575746@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
PG Bug reporting form <noreply(at)postgresql(dot)org> writes:
> In this case, I have a query that selects from a table, joins a table, joins
> that same table, joins the first table, performs a NOT
> firsttable.value=lasttable.value to make sure that I'm not joining an item
> with itself. There are indexes for the conditions in each join. When I run
> this as a select query, the EXPLAIN output uses index scans the whole way
> through. When I SELECT EXISTS(thatquery) FROM firsttable WHERE firsttable.id
> IN (1,2,3), the EXPLAIN output switches to using sequential scans. This
> destroys performance on this 3.8m row table.
Are you claiming that 9.6 did better? I'm not aware that we changed
anything significant about how a sub-select in a query's select list
works.
The table/view definitions alone are not enough to investigate this,
since it's unlikely that we'd get the same plans on an empty table
as a populated one. Please see if you can make a self-contained test
case with some dummy data that reproduces the problem (ie better
plan on 9.6 than later).
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | Euler Taveira | 2020-06-20 18:22:17 | Re: BUG #16502: EXPLAIN JSON format adds extra quotes around index names |
Previous Message | PG Bug reporting form | 2020-06-19 11:58:35 | BUG #16505: ssl_crl_file bug? |