Re: Running lots of inserts from selects on 9.4.5

> On Feb 10, 2016, at 5:13 AM, Dan Langille <dan@langille.org> wrote:
>
>> On Feb 10, 2016, at 2:47 AM, Jeff Janes <jeff.janes@gmail.com> wrote:
>>
>> On Tue, Feb 9, 2016 at 4:09 PM, Dan Langille <dan@langille.org> wrote:
>>> 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  created via
>>> http://tatiyants.com/pev/#/plans
>>>
>>> This gist contains postgresql.conf, zfs settings, slog, disk partitions.
>>>
>>>  https://gist.github.com/dlangille/33331a8c8cc62fa13b9f
>>
>> The table you are inserting into has 7 indexes, all of which have to
>> be maintained.  The index on the sequence column should be efficient
>> to maintain.  But for the rest, if the inserted rows are not naturally
>> ordered by any of the indexed columns then it would end up reading 6
>> random scattered leaf pages in order to insert row pointers.  If none
>> those pages are in memory, that is going to be slow to read off from
>> hdd in single-file.  Also, you are going dirty all of those scattered
>> pages, and they will be slow to write back to hdd because there
>> probably won't be much opportunity for write-combining.
>>
>> Do you really need all of those indexes?
>>
>> Won't the index on (jobid, pathid, filenameid) service any query that
>> (jobid) does, so you can get rid of the latter?
>>
>> And unless you have range queries on fileindex, like "where jobid = 12
>> and fileindex between 4 and 24" then you should be able to replace
>> (jobid, fileindex) with (fileindex,jobid) and then get rid of the
>> stand-alone index on (fileindex).
>>
>> If you add an "order by" to the select statement which order by the
>> fields of one of the remaining indexes, than you could make the
>> maintenance of that index become much cheaper.
>
> I will make these changes one-by-one and test each.  This will be interesting.

On a test server, the original insert takes about 45 minutes.  I removed all indexes.  25 minutes.

Thank you.

--
Dan Langille - BSDCan / PGCon
dan@langille.org