In the main postgres configuration file, postgresql.conf, you could set log_min_duration_statement to some value larger than zero. The value is expressed in milliseconds, so for instance setting it to 1000 makes the server log all SQL statements that take longer than 1 second. It may or may not fit your case. (You'd also need some process monitoring that log, I guess.)

The default setting of this parameter is -1 (=disabled): 

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

[download]

And, by the way, you don't need to run "EXPLAIN" queries manually. EXPLAIN has nice machine-readable output formats like json and yaml. For example: 

echo "
create table t(n integer primary key);
insert into t select * from generate_series(1,10000);
analyze t;  --> gather table statistics

explain (analyze, verbose, format yaml) select count(*) from t where n
+ between 10 and 20;
" | psql -qtAX

[download]

The output: 


- Plan:
    Node Type: "Aggregate"
    Strategy: "Plain"
    Partial Mode: "Simple"
    Parallel Aware: false
    Startup Cost: 8.51
    Total Cost: 8.52
    Plan Rows: 1
    Plan Width: 8
    Actual Startup Time: 0.040
    Actual Total Time: 0.040
    Actual Rows: 1
    Actual Loops: 1
    Output:
      - "count(*)"
    Plans:
      - Node Type: "Index Only Scan"
        Parent Relationship: "Outer"
        Parallel Aware: false
        Scan Direction: "Forward"
        Index Name: "t_pkey"
        Relation Name: "t"
        Schema: "public"
        Alias: "t"
        Startup Cost: 0.29
        Total Cost: 8.48
        Plan Rows: 10
        Plan Width: 0
        Actual Startup Time: 0.022
        Actual Total Time: 0.029
        Actual Rows: 11
        Actual Loops: 1
        Output:
          - "n"
        Index Cond: "((t.n >= 10) AND (t.n <= 20))"
        Rows Removed by Index Recheck: 0
        Heap Fetches: 11
  Planning Time: 0.481
  Triggers:
  Execution Time: 0.149

[download]

In reply to Re: DBI and keeping track of planner statistics by erix
in thread DBI and keeping track of planner statistics by mla12

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.