We found (2017/06) that on PostgreSQL 9.6 sometimes some session goes into kind of “drowsy” state. It is running – just incredibly slowly. When we kill this session and start again it runs with normal speed. Therefore we use golang solution with context timeout. Context timeout in procedure kills session after given amount of minutes and main program restarts it. Here are two simple examples:

package pgutils

import (
	"context"
	"database/sql"
	"log"
	"time"
)

//PgRunQueryRow runs pg query which returns only one int value - with timeout
func PgRunQueryRow(pgDB *sql.DB, q string, defaultPGTimeOutMinutes time.Duration) (ret int64, err error) {
	ctx, cancel := context.WithTimeout(context.Background(), defaultPGTimeOutMinutes*time.Minute)
	defer cancel()
	err = pgDB.QueryRowContext(ctx, q).Scan(&ret)
	if ctx.Err() == context.DeadlineExceeded {
		err = fmt.Errorf("pg query timed out: ", err)
	}
	if err != nil {
		err = fmt.Errorf("cannot run pg query:", err)
	}
	return ret, err
}

//PgExecQuery execute pg query - with timeout - respond is discarded (used for example for aggregation procedures)
func PgExecQuery(pgDB *sql.DB, q string, defaultPGTimeOutMinutes time.Duration) error {
	ctx, cancel := context.WithTimeout(context.Background(), defaultPGTimeOutMinutes*time.Minute)
	defer cancel()
	res, err := pgDB.ExecContext(ctx, q)
	if ctx.Err() == context.DeadlineExceeded {
		err = fmt.Errorf("pg query timed out: ", err)
	}
	if err != nil {
		err = fmt.Errorf("cannot exec pg query:", err)
	}
	DebugMsg(2, "pg exec result: ", res)
        return err
}