Greg Stark <stark@mit.edu> writes:
> At my day job I've been doing a fair amount of routine query and
> schema optimization and I've noticed on particular query shape that
> has repeatedly caused problems, and it's one we've talked about
> before.
> select * from table where simple-restriction 0 OR (complex-subquery)
> For example something like:
> SELECT * FROM projects WHERE ispublic OR project_id IN (SELECT
> project_id FROM project_members WHERE userid = ?)
> Either half of this clause can easily be executed using indexes but
> the combination forces Postgres to do a full sequential table scan.
Yeah. This is at least related to, if not the exact same as,
what I was fooling with a year ago:
https://www.postgresql.org/message-id/flat/7f70bd5a-5d16-e05c-f0b4-2fdfc8873489@BlueTreble.com
The single-relation-scan case is possibly a bit easier to deal with
than what we were looking at there, in that it's clear that you
can use the rel's CTID to de-duplicate, and that that will give
the right answer.
regards, tom lane