Re: [PERFORM] Multiple-Table-Spanning Joins with ORs in WHERE Clause

Now I found time to investigate all proposed queries side by side. Here are the results (warmup + multiple executions). TL;DR - Jeff's proposed answer performs significantly faster with our data than any other solution (both planning and execution time). I have no real idea how PostgreSQL doe

Re: [PERFORM] Multiple-Table-Spanning Joins with ORs in WHERE Clause

On 29.09.2016 22:26, Jeff Janes wrote: Well, I don't recall seeing this issue on this list before (or a few other forums I read) while I see several other issues over and over again. So that is why I think it is a niche issue. Perhaps I've have seen it before and just forgotten, or have not r

Re: [PERFORM] Multiple-Table-Spanning Joins with ORs in WHERE Clause

On Thu, Sep 29, 2016 at 11:12 AM, Pavel Stehule wrote: > > > 2016-09-29 14:20 GMT+02:00 Sven R. Kunze : > >> On 23.09.2016 11:00, Pavel Stehule wrote: >> >> 2016-09-23 8:35 GMT+02:00 Sven R. Kunze : >> >>> I was wondering: would it be possible for PostgreSQL to rewrite the >>> query to generate t

Re: [PERFORM] Multiple-Table-Spanning Joins with ORs in WHERE Clause

On Thu, Sep 29, 2016 at 11:48 AM, Sven R. Kunze wrote: > On 29.09.2016 20:03, Jeff Janes wrote: > > Perhaps some future version of PostgreSQL could do so, but my gut feeling > is that that is not very likely. It would take a lot of work, would risk > breaking or slowing down other things, and is

Re: [PERFORM] Multiple-Table-Spanning Joins with ORs in WHERE Clause

2016-09-29 20:49 GMT+02:00 Sven R. Kunze : > On 29.09.2016 20:12, Pavel Stehule wrote: > >> In ideal world then plan should be independent on used form. The most >> difficult is safe estimation of OR predicates. With correct estimation the >> transformation to UNION form should not be necessary I

Re: [PERFORM] Multiple-Table-Spanning Joins with ORs in WHERE Clause

On 29.09.2016 20:12, Pavel Stehule wrote: In ideal world then plan should be independent on used form. The most difficult is safe estimation of OR predicates. With correct estimation the transformation to UNION form should not be necessary I am think. Ah, okay. That's interesting. So how can

Re: [PERFORM] Multiple-Table-Spanning Joins with ORs in WHERE Clause

Hi Jeff, On 29.09.2016 20:03, Jeff Janes wrote: I don't know what the subquery plan is, I don't see references to that in the email chain. Lutz posted the following solution: SELECT * FROM big_table WHERE id in (SELECT big_table_id FROM table_a WHERE "table_a"."item_id" IN ()) OR

Re: [PERFORM] Multiple-Table-Spanning Joins with ORs in WHERE Clause

2016-09-29 14:20 GMT+02:00 Sven R. Kunze : > On 23.09.2016 11:00, Pavel Stehule wrote: > > 2016-09-23 8:35 GMT+02:00 Sven R. Kunze : > >> I was wondering: would it be possible for PostgreSQL to rewrite the query >> to generate the UNION (or subquery plan if it's also fast) on it's own? >> > > It d

Re: [PERFORM] Multiple-Table-Spanning Joins with ORs in WHERE Clause

On Thu, Sep 22, 2016 at 11:35 PM, Sven R. Kunze wrote: > Thanks a lot Madusudanan, Igor, Lutz and Jeff for your suggestions. > > What I can confirm is that the UNION ideas runs extremely fast (don't have > access to the db right now to test the subquery idea, but will check next > week as I trave

Re: [PERFORM] Multiple-Table-Spanning Joins with ORs in WHERE Clause

On 23.09.2016 11:00, Pavel Stehule wrote: 2016-09-23 8:35 GMT+02:00 Sven R. Kunze >: I was wondering: would it be possible for PostgreSQL to rewrite the query to generate the UNION (or subquery plan if it's also fast) on it's own? It depends on real data. On

Re: [PERFORM] Multiple-Table-Spanning Joins with ORs in WHERE Clause

Thanks a lot Madusudanan, Igor, Lutz and Jeff for your suggestions. What I can confirm is that the UNION ideas runs extremely fast (don't have access to the db right now to test the subquery idea, but will check next week as I travel right now). Thanks again! :) I was wondering: would it be

Re: [PERFORM] Multiple-Table-Spanning Joins with ORs in WHERE Clause

On Thu, Sep 22, 2016 at 6:37 AM, Madusudanan.B.N wrote: > > However, this results in an awful slow plan (requiring to scan the > complete big_table which obviously isn't optimal) > > You mean to say there is a sequential scan ? An explain would be helpful. > Are there indexes on the provided wher

Re: [PERFORM] Multiple-Table-Spanning Joins with ORs in WHERE Clause

with ORs in WHERE Clause -Original Message- From: Igor Neyman Sent: Thursday, September 22, 2016 10:33 AM To: 'Sven R. Kunze' ; pgsql-performance@postgresql.org Subject: RE: [PERFORM] Multiple-Table-Spanning Joins with ORs in WHERE Clause -Original Message- F

Re: [PERFORM] Multiple-Table-Spanning Joins with ORs in WHERE Clause

-Original Message- From: Igor Neyman Sent: Thursday, September 22, 2016 10:33 AM To: 'Sven R. Kunze' ; pgsql-performance@postgresql.org Subject: RE: [PERFORM] Multiple-Table-Spanning Joins with ORs in WHERE Clause -Original Message- From: pgsql-performance-ow...@post

Re: [PERFORM] Multiple-Table-Spanning Joins with ORs in WHERE Clause

-Original Message- From: pgsql-performance-ow...@postgresql.org [mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of Sven R. Kunze Sent: Thursday, September 22, 2016 9:25 AM To: pgsql-performance@postgresql.org Subject: [PERFORM] Multiple-Table-Spanning Joins with ORs in WHERE

Re: [PERFORM] Multiple-Table-Spanning Joins with ORs in WHERE Clause

> However, this results in an awful slow plan (requiring to scan the complete big_table which obviously isn't optimal) You mean to say there is a sequential scan ? An explain would be helpful. Are there indexes on the provided where clauses. Postgres can do a Bitmap heap scan to combine indexes,

[PERFORM] Multiple-Table-Spanning Joins with ORs in WHERE Clause

Hi pgsql-performance list, what is the recommended way of doing **multiple-table-spanning joins with ORs in the WHERE-clause**? Until now, we've used the LEFT OUTER JOIN to filter big_table like so: SELECT DISTINCT FROM "big_table" LEFT OUTER JOIN "table_a" ON ("big_table"."id" =