Re: BUG #16504: Wrapping query in EXISTS() causes sequential scans of tables

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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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
> 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

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

In response to

Browse pgsql-bugs by date

  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?