Statistics on Query Time (PostgreSQL)

You need to change your PostgreSQL configuration file.

Do enable this property:

log_min_duration_statement = -1        # -1 is disabled, 0 logs all statements                                    
                                       # and their durations, > 0 logs only                                       
                                       # statements running at least this number                                  
                                       # of milliseconds             

After that, execution time will be logged and you will be able to figure out exactly how bad (or good) are performing your queries.

You can also use some LOG PARSING utilities to provide awesome HTML output for further analysis such as pgfouine.

Directly, no, there's not. But you can do an indirect and pretty close estimate by checking the time right before and right after the query you're interested in timing.

$sql = "Your Query";
$bm = "SELECT extract(epoch FROM clock_timestamp())";
$query = "{$bm}; {$sql}; {$bm};";

Function clock_timestamp() gives you the server actual time when the statement starts. Since that SELECT involves no tables, we can expect it to be almost instantaneous. I guess any Pg driver offers support for multiple queries; it is important that these 3 queries (the real one and the 2 extras) go together, else you'd be measuring data transport times as well...

For PHP I have a function to handle this. In summary it goes like:

<?php

function pgquery($sql, $conn)
{
    // Prepend and append benchmarking queries
    $bm = "SELECT extract(epoch FROM clock_timestamp())";
    $query = "{$bm}; {$sql}; {$bm};";

    // Execute the query, and time it (data transport included)
    $ini = microtime(true);

    pg_send_query($conn, $query);

    while ($resource = pg_get_result($conn))
    {
        $resources[] = $resource;
    }

    $end = microtime(true);

    // "Extract" the benchmarking results
    $q_ini = pg_fetch_row(array_shift($resources));
    $q_end = pg_fetch_row(array_pop($resources));

    // Compute times
    $time = round($end - $ini, 4);             # Total time (inc. transport)
    $q_time = round($q_end[0] - $q_ini[0], 4); # Query time (Pg server only)

    return $resources;
}

?>

I just left the basics there. $conn holds a link to a Pg connection, and $resources is an array of returned pg resources (in case you sent multiple queries in your $sql).

$time holds total time since the query left for the Pg server until the result arrives. $q-time only holds the actual query time you wanted (or a very good approximation).

Add error handling and other processing to your liking, I have plenty but it's irrelevant to your question.

Here is a sql version of @user832146 proposal:

CREATE OR REPLACE FUNCTION "get_sql_runtime"(
  PAR_sql TEXT
, OUT sql_runtime REAL
)
AS $$
DECLARE
  run_time_start TIMESTAMP WITH TIME ZONE;
  run_time_end TIMESTAMP WITH TIME ZONE;
BEGIN
  SELECT clock_timestamp() INTO run_time_start;
  EXECUTE PAR_sql;
  SELECT clock_timestamp() INTO run_time_end;
  SELECT EXTRACT(EPOCH FROM (run_time_end - run_time_start)) INTO sql_runtime;
END; $$
LANGUAGE plpgsql
VOLATILE;

You use it as:

SELECT * FROM get_sql_runtime('SELECT * FROM "my_table_name"');

And it will yield a result like:

NOTE: the returned value is in seconds.

You CANNOT do this in SQL, because even if you would be able to call each of this statements in a loop, each call to now() would return the same result, because you are in a single transaction.

It is just possible by creating a own volatile now() function, returning another value on each invocation.