Re: [GENERAL] Creating table with data from a join

> Marc, I am using postgres 9.4. I didn't benchmark, but intuitively the modulo > operator will force traversing every record in table "a" 4 times, as it can't > use an index. Not necessarily. seq scans can be synchronized: "This allows sequential scans of large tables to synchronize with each

Re: [GENERAL] Creating table with data from a join

David, I did something like this: psql -f /dev/fd/3 3 << IN1 & psql -f /dev/fd/4 4 << IN2 ... INSERT INTO c SELECT * FRO a JOIN b ON a.ad=b.id WHERE a.id < 0.25th quantile IN1 INSERT INTO c SELECT * FRO a JOIN b ON a.ad=b.id WHERE a.id < 0.5th quantile AND a.id >= 0.25th quantile IN2 ... IN3 ...

Re: [GENERAL] Creating table with data from a join

On 14/07/2015 18:50, Igor Stassiy wrote: > Julien, I would gladly provide more information, I am just not sure what > to add. > Well, was your concern about why option #2 is the quickest, or is this runtime with option #2 still too slow for you ? > I would be willing to leave the server compromi

Re: [GENERAL] Creating table with data from a join

>Hello, > >I am benchmarking different ways of putting data into table on table creation: > >1. INSERT INTO c SELECT * FROM a JOIN b on a.id = b.id; >2. CREATE TABLE c AS SELECT * FROM a JOIN b on a.id = b.id; >3. psql -c "COPY (SELECT * FROM a JOIN b on a.id = b.id) TO STDOUT" | >parallel --block

Re: [GENERAL] Creating table with data from a join

Julien, I would gladly provide more information, I am just not sure what to add. I would be willing to leave the server compromised for things like corrupts or data losses during the time of this import, but the server has to be up and running before and after the import, if it is successful (so I

Re: [GENERAL] Creating table with data from a join

On 14/07/2015 18:21, Igor Stassiy wrote: > Julien, I have the following setting for WAL level: #wal_level = minimal > (which defaults to minimal anyway) > Sorry, I sent my mail too early :/ So, option #2 is winner by design. You didn't say anything about your needs, so it's hard to help you much

Re: [GENERAL] Creating table with data from a join

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 14/07/2015 18:21, Igor Stassiy wrote: > Julien, I have the following setting for WAL level: #wal_level = > minimal (which defaults to minimal anyway) > > On Tue, Jul 14, 2015 at 6:19 PM Julien Rouhaud > mailto:julien.rouh...@dalibo.com>> > wrote:

Re: [GENERAL] Creating table with data from a join

Julien, I have the following setting for WAL level: #wal_level = minimal (which defaults to minimal anyway) On Tue, Jul 14, 2015 at 6:19 PM Julien Rouhaud wrote: > On 14/07/2015 11:12, Igor Stassiy wrote: > > Hello, > > > > I am benchmarking different ways of putting data into table on table > >

Re: [GENERAL] Creating table with data from a join

On 14/07/2015 11:12, Igor Stassiy wrote: > Hello, > > I am benchmarking different ways of putting data into table on table > creation: > > 1. INSERT INTO c SELECT * FROM a JOIN b on a.id = b.id > ; > 2. CREATE TABLE c AS SELECT * FROM a JOIN b on a.id = b.

Re: [GENERAL] Creating table with data from a join

Thank you David. I tried to measure the time of COPY (SELECT * FROM a JOIN b ON a.id = b.id) TO '/tmp/dump.sql' and it took an order of magnitude time less (~10x) than the complete command (together with INSERT), so conversion is probably not the main factor of slowdown (unless conversion from text

Re: [GENERAL] Creating table with data from a join

On 14 July 2015 at 21:12, Igor Stassiy wrote: > Hello, > > I am benchmarking different ways of putting data into table on table > creation: > > 1. INSERT INTO c SELECT * FROM a JOIN b on a.id = b.id; > 2. CREATE TABLE c AS SELECT * FROM a JOIN b on a.id = b.id; > 3. psql -c "COPY (SELECT * FROM a

[GENERAL] Creating table with data from a join

Hello, I am benchmarking different ways of putting data into table on table creation: 1. INSERT INTO c SELECT * FROM a JOIN b on a.id = b.id; 2. CREATE TABLE c AS SELECT * FROM a JOIN b on a.id = b.id; 3. psql -c "COPY (SELECT * FROM a JOIN b on a.id = b.id) TO STDOUT" | parallel --block 128M --j