Extend PostgreSQL query planner (EXPLAIN) logs with relative costs and times

explain-weight.php

#!/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 [email protected]
 * 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);
    }
}

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