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] Failing Multi-Job Restores, Missing Indexes on Restore

Cea Stapleton writes: > We’re using pg_restore (PostgreSQL) 9.5.4 for the restores. We’ve used > variations on the job number: > /usr/bin/pg_restore -j 6 -Fc -O -c -d DBNAME RESTORE_FILE” OK ... do you actually need the -c, and if so why? > We’ll take a look at the memory overcommit - w

Re: [PERFORM] Failing Multi-Job Restores, Missing Indexes on Restore

Thanks Tom! We’re using pg_restore (PostgreSQL) 9.5.4 for the restores. We’ve used variations on the job number: /usr/bin/pg_restore -j 6 -Fc -O -c -d DBNAME RESTORE_FILE” We’ll take a look at the memory overcommit - would that also explain the index issues we were seeing before we were seeing

Re: [PERFORM] Failing Multi-Job Restores, Missing Indexes on Restore

Cea Stapleton writes: > We are having a baffling problem we hope you might be able to help with. We > were hoping to speed up postgres restores to our reporting server. First, we > were seeing missing indexes with pg_restore to our reporting server for one > of our databases when we did pg_rest

[PERFORM] Failing Multi-Job Restores, Missing Indexes on Restore

Hi! We are having a baffling problem we hope you might be able to help with. We were hoping to speed up postgres restores to our reporting server. First, we were seeing missing indexes with pg_restore to our reporting server for one of our databases when we did pg_restore with multiple jobs (a

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] Millions of tables

From: pgsql-performance-ow...@postgresql.org [mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of Greg Spiegelberg Sent: Tuesday, September 27, 2016 7:28 PM To: Terry Schmitt Cc: pgsql-performa. Subject: Re: [PERFORM] Millions of tables On Tue, Sep 27, 2016 at 10:15 AM, Terr

Re: [PERFORM] Millions of tables

On 26 September 2016 at 05:19, Greg Spiegelberg wrote: > I did look at PostgresXL and CitusDB. Both are admirable however neither > could support the need to read a random record consistently under 30ms. > It's a similar problem Cassandra and others have: network latency. At this > scale, to pro