Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Extend PostgreSQL query planner (EXPLAIN) logs with relative costs and times
#!/usr/bin/php5
<?php
/**
* Simple script to weight PostgreSQL query planner costs (`analyze` times included!) in explain log file.
* Handy for analyzing large query plans with a lot of subqueries.
* Example output (this scan took ~21% of total query run time, worth optimizing):
-> Seq Scan on public.tablename (cost=0.00..3919.54 rows=1 width=0) (actual time=64.232..86.516 rows=1 loops=1)
Cost: 0.00..22.35%
Time: 15.53..20.92%
Output: tablename.id, tablename.name, tablename.another_id
Filter: (tablename.another_id = 247159::bigint)
Rows Removed by Filter: 95082
Buffers: shared hit=2731
* Just `chmod + x explain-weight.php`, then run `./explain-weight.php explain.log`, and see `weighted-explain.log`.
* You can even make an alias in your ~/.bash_aliases:
alias explain-weights='/home/hom3chuk/utils/postgres/explain-weight.php'
* FYI: to save query plan from psql-console just type `\o logfile.log`,
* run your `EXPLAIN` or `EXPLAIN (ANALYZE[, VERBOSE][, BUFFERS])`,
* then turn off logging with `\o`.
* @author hom3chuk@gmail.com
* Date: 29.04.14
*/
function fail($message){
print $message;
exit();
}
// Some init here
if ( 1 === $argc ){
fail("No file name passed. Usage example \n\n\t" . __FILE__ . " /path/to/explain.log\n\n");
}
$filename = $argv[1];
if ( false === file_exists($filename) || is_dir($filename) ){
fail("$filename is not a file.\n");
}
$file = fopen($filename, 'r');
if ( false === $file ){
fail ("Could not open $filename. Maybe you should check permissions.\n");
}
$outFilename = dirname($filename) . DIRECTORY_SEPARATOR . "weighted-" . basename($filename);
$outFile = fopen($outFilename, 'w+');
if ( false === $outFile ){
fail ("Could not open $outFilename. Maybe you should check permissions.\n");
}
// Here goes the magic
while ( $currentLine = fgets($file) ){
$costMatch = array();
$timeMatch = array();
$weightLine = '';
fputs($outFile, $currentLine);
if ( 1 === preg_match('#^(\s*).*?\(cost=([\d.]*?)\.\.([\d.]*)#ism', $currentLine, $costMatch) ){
if ( false === isset($totalCost) ){
$totalCost = $costMatch[3];
}
$costMinWeighted = ($costMatch[2] / $totalCost) * 100;
$costMaxWeighted = ($costMatch[3] / $totalCost) * 100;
$weightLine =
$costMatch[1]
. " Cost: "
. number_format($costMinWeighted, 2)
. ".."
. number_format($costMaxWeighted, 2)
."%\n";
if ( 1 === preg_match('#\(actual\s*time=([\d.]*?)\.\.([\d.]*).*?loops=([\d]*)#ism', $currentLine, $timeMatch) ){
if ( false === isset($totalTime) ){
$totalTime = $timeMatch[2];
}
$timeMinWeighted = ($timeMatch[1] / $totalTime) * 100;
$timeMaxWeighted = ($timeMatch[2] / $totalTime) * 100;
$weightLine .=
$costMatch[1]
. " Time: "
. number_format($timeMinWeighted, 2)
. ".."
. number_format($timeMaxWeighted, 2)
."%\n";
if ( (int)$timeMatch[3] > 1 ){
$weightLine .=
$costMatch[1]
. " Loop total: "
. number_format(((int)$timeMatch[3] * (float)$timeMatch[2] / $totalTime) * 100, 2)
. "%\n";
}
}
fputs($outFile, $weightLine);
}
}
@hom3chuk

This comment has been minimized.

Copy link
Owner Author

hom3chuk commented Apr 29, 2014

Теперь и залупленые запросы!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.