Re: Query pattern tha Postgres doesn't handle well

Greg Stark <stark(at)mit(dot)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(at)BlueTreble(dot)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