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); | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
This comment has been minimized.
Теперь и залупленые запросы!