Thread: Bulk load data from one table to another
I have a partitioned table that uses monthly temporal partitions. Every night I want to load the last 10 weeks of data into a separate non-partitioned table. Currently, I truncate the destination table, drop the indexes, execute: "INSERT INTO destination SELECT * FROM source_partition" and recreate indexes. This loads about 66.5 million rows into the destination table in about 1 hour. Recreating of indexes (4 of them) takes additional 2 hours. Is there a more efficient way to perform this procedure? Thanks.
Nikola <XLPizza@gmail.com> writes: > Currently, I truncate the destination table, drop the indexes, > execute: > "INSERT INTO destination SELECT * FROM source_partition" > and recreate indexes. > This loads about 66.5 million rows into the destination table in about > 1 hour. Recreating of indexes (4 of them) takes additional 2 hours. Is > there a more efficient way to perform this procedure? Do you have maintenance_work_mem set to a suitable value for building the indexes? regards, tom lane