> On Jan 11, 2016, at 8:35 AM, Eric Bollengier <e...@baculasystems.com
> <mailto:e...@baculasystems.com>> 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
------------------------------------------------------------------------------
Site24x7 APM Insight: Get Deep Visibility into Application Performance
APM + Mobile APM + RUM: Monitor 3 App instances at just $35/Month
Monitor end-to-end web transactions and take corrective actions now
Troubleshoot faster and improve end-user experience. Signup Now!
http://pubads.g.doubleclick.net/gampad/clk?id=267308311&iu=/4140
_______________________________________________
Bacula-devel mailing list
Bacula-devel@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-devel
