|
>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 --jobs 4 --pipe psql -c "COPY c FROM STDIN"; > >(the parallel command is available as part of parallel deb package in Ubuntu for example, it splits the stdin by newline character and feeds it to the corresponding command) > >Both tables a and b have ~16M records and one of the columns in a is geometry (ranging from several KB in size to several MB). Columns in b are mostly integers. > >The machine that I am running these commands on has the following parameters: > >default_statistics_target = 50 # pgtune wizard 2012-06-06 maintenance_work_mem = 1GB # pgtune wizard 2012-06-06 constraint_exclusion = on # pgtune wizard 2012-06-06 checkpoint_completion_target = 0.9 # pgtune wizard 2012-06-06 effective_cache_size = 48GB # pgtune wizard 2012-06-06 >work_mem = 80MB # pgtune wizard 2012-06-06 >wal_buffers = 8MB # pgtune wizard 2012-06-06 >checkpoint_segments = 16 # pgtune wizard 2012-06-06 >shared_buffers = 16GB # pgtune wizard 2012-06-06 >max_connections = 400 # pgtune wizard 2012-06-06 > >One would expect the 3rd option to be faster than 1 and 2, however 2 outperforms both by a large margin (sometimes x2). This is especially surprising taking into account that COPY doesn't acquire a global lock on the table, only a RowExclusiveLock which PG Version ? I find interesting, that 2 outperforms 1. The only explanation I can imagine is that "CREATE TABLE AS" freezes the data on the fly, as possible with "COPY FROM" (http://www.postgresql.org/docs/9.4/interactive/sql-copy.html) You may try parallel insert without using STDIN using modulo. Just start these 4 queries simultaneously: INSERT INTO c SELECT * FROM a JOIN b on a.id = b.id WHERE a.id%4 = [0,1,2,3} I usually avoid parallel INSERTS to avoid I/O contention and random distribution within the target tables. Are you monitoring the I/O activity in your tests ? Have you tried to use only 2 parallel processes? regards, Marc Mamin >(according to http://www.postgresql.org/message-id/10611.1014867684@xxxxxxxxxxxxx) > >So is option 2 a winner by design? Could you please suggest other alternatives to try (if there are any)? And what might be the reason that 3 is not outperforming the other 2? > >Thank you, >Igor > > > |
