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:
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
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 |