From: | Dan Langille <dan(at)langille(dot)org> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | Running lots of inserts from selects on 9.4.5 |
Date: | 2016-02-10 00:09:25 |
Message-ID: | 69012280-1847-426A-8813-D51CA4CC9B02@langille.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
I have a wee database server which regularly tries to insert 1.5 million or even 15 million new rows into a 400 million row table. Sometimes these inserts take hours.
The actual query to produces the join is fast. It's the insert which is slow.
INSERT INTO File (FileIndex, JobId, PathId, FilenameId, LStat, MD5, DeltaSeq)
SELECT batch_testing.FileIndex, batch_testing.JobId, Path.PathId, Filename.FilenameId, batch_testing.LStat, batch_testing.MD5, batch_testing.DeltaSeq
FROM batch_testing JOIN Path ON (batch_testing.Path = Path.Path)
JOIN Filename ON (batch_testing.Name = Filename.Name);
This is part of the plan: http://img.ly/images/9374145/full <http://img.ly/images/9374145/full> created via http://tatiyants.com/pev/#/plans <http://tatiyants.com/pev/#/plans>
This gist contains postgresql.conf, zfs settings, slog, disk partitions.
https://gist.github.com/dlangille/33331a8c8cc62fa13b9f <https://gist.github.com/dlangille/33331a8c8cc62fa13b9f>
I'm tempted to move it to faster hardware, but in case I've missed something basic...
Thank you.
--
Dan Langille - BSDCan / PGCon
dan(at)langille(dot)org
From | Date | Subject | |
---|---|---|---|
Next Message | Jeff Janes | 2016-02-10 07:47:02 | Re: Running lots of inserts from selects on 9.4.5 |
Previous Message | Merlin Moncure | 2016-02-08 22:58:10 | Re: bad COPY performance with NOTIFY in a trigger |