Re: [PERFORM] performance problem on big tables

2017-08-15 Thread Pavel Stehule
2017-08-15 18:13 GMT+02:00 Jeff Janes : > On Tue, Aug 15, 2017 at 3:06 AM, Mariel Cherkassky < > mariel.cherkas...@gmail.com> wrote: > >> Hi, >> So I I run the cheks that jeff mentioned : >> \copy (select * from oracle_remote_table) to /tmp/tmp with binary - 1 >> hour and 35

Re: [PERFORM] performance problem on big tables

2017-08-15 Thread Scott Marlowe
On Tue, Aug 15, 2017 at 4:06 AM, Mariel Cherkassky wrote: > Hi, > So I I run the cheks that jeff mentioned : > \copy (select * from oracle_remote_table) to /tmp/tmp with binary - 1 hour > and 35 minutes So 26G takes 95 minutes, or 27 MB/minute or 456k/second? Sound

Re: [PERFORM] Odd sudden performance degradation related to temp object churn

2017-08-15 Thread Scott Marlowe
On Mon, Aug 14, 2017 at 5:10 PM, Jerry Sievers wrote: > Peter Geoghegan writes: > >> On Mon, Aug 14, 2017 at 12:53 PM, Jeremy Finzel wrote: >> >>> This particular db is on 9.3.15. Recently we had a serious performance >>> degradation

Re: [PERFORM] Odd sudden performance degradation related to temp object churn

2017-08-15 Thread Scott Marlowe
Oh yeah, sorry. Was looking at a different system where we were using a tablespace for temp tables. On Tue, Aug 15, 2017 at 10:00 AM, Jeremy Finzel wrote: >> > Not so. >> > >> > This system has no defined temp_tablespace however spillage due to >> > sorting/hashing that

Re: [PERFORM] performance problem on big tables

2017-08-15 Thread Jeff Janes
On Tue, Aug 15, 2017 at 3:06 AM, Mariel Cherkassky < mariel.cherkas...@gmail.com> wrote: > Hi, > So I I run the cheks that jeff mentioned : > \copy (select * from oracle_remote_table) to /tmp/tmp with binary - 1 hour > and 35 minutes > \copy local_postresql_table from /tmp/tmp with binary - Didnt

Re: [PERFORM] Odd sudden performance degradation related to temp object churn

2017-08-15 Thread Jeremy Finzel
> > > Not so. > > > > This system has no defined temp_tablespace however spillage due to > > sorting/hashing that exceeds work_mem goes to base/pgsql_tmp which we > > have symlinked out to a local SSD drive. > > Which is also where temp tables are created. > This isn't true, at least in our

Re: [PERFORM] Odd sudden performance degradation related to temp object churn

2017-08-15 Thread Scott Marlowe
On Mon, Aug 14, 2017 at 4:16 PM, Jerry Sievers wrote: > Scott Marlowe writes: > >> You do know that temp tables go into the default temp table space, >> just like sorts, right? > > Not so. > > This system has no defined temp_tablespace however

Re: [PERFORM] performance problem on big tables

2017-08-15 Thread Mariel Cherkassky
Hi, So I I run the cheks that jeff mentioned : \copy (select * from oracle_remote_table) to /tmp/tmp with binary - 1 hour and 35 minutes \copy local_postresql_table from /tmp/tmp with binary - Didnt run because the remote oracle database is currently under maintenance work. So I decided to follow