Re: [Bacula-devel] Faster Batch table processing - PostgreSQL

[Dan L. <da...@la...>]

> On Jan 11, 2016, at 8:35 AM, Eric Bollengier <er...@ba... <mailto:er...@ba...>> wrote:
> 
> Hello Dan,
> 
>> 
>> I have a test job with 15,764,45 rows in the Batch table.
>> 
>> With a change in SQL, I went from 45 minutes to 15 minutes.
>> 
>> Old SQL:
>> 
>>  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);
>> 
>> 
>> New SQL:
>> 
>> explain analyze
>> INSERT INTO File (FileIndex, JobId, PathId, FilenameId, LStat, MD5,
>> DeltaSeq) 
>>  SELECT BT.FileIndex,
>>         BT.JobId, 
>>         (SELECT Path.PathId         FROM Path     WHERE Path.path    
>> = BT.Path), 
>>         (SELECT Filename.FilenameId FROM Filename WHERE Filename.name
>> = BT.Name) ,
>>         BT.LStat, 
>>         BT.MD5,
>>         BT.DeltaSeq 
>>    FROM batch_testing BT;
>> 
>> NOTE: I have used the table Batch_Testing... because I was testing. :)
>> 
> 
> Thanks for your feedback.
> 
> The difference makes me think that it might be a cache issue. Can you
> give us the detailed execution plan for both queries ? explain (analyze
> and buffers) and your server settings? That might be probably done with
> pg_autoexplain I think.

The explain analyze output is in the URL:  https://gist.github.com/dlangille/58a414a260f4c6706d7e <https://gist.github.com/dlangille/58a414a260f4c6706d7e>

I also just added postgresql.conf