Re: Query pattern tha Postgres doesn't handle well

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Greg Stark <stark(at)mit(dot)edu>
Cc: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Query pattern tha Postgres doesn't handle well
Date: 2018-02-24 20:33:49
Message-ID: 21400.1519504429@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Peter Geoghegan 2018-02-24 21:00:32 Re: [HACKERS] Re: Improve OR conditions on joined columns (common star schema problem)
Previous Message Greg Stark 2018-02-24 20:26:19 Query pattern tha Postgres doesn't handle well