Re: auto_explain sample rate

From: Craig Ringer <craig(at)2ndquadrant(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: auto_explain sample rate
Date: 2015-06-03 10:54:24
Views: Raw Message | Whole Thread | Download mbox
Lists: pgsql-hackers

On 2 June 2015 at 15:07, Craig Ringer <craig(at)2ndquadrant(dot)com> wrote:

> On 29 May 2015 at 11:35, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Craig Ringer <craig(at)2ndquadrant(dot)com> writes:
>> > It's sometimes desirable to collect auto_explain data with ANALYZE in
>> order
>> > to track down hard-to-reproduce issues, but the performance impacts can
>> be
>> > pretty hefty on the DB.
>> > I'm inclined to add a sample rate to auto_explain so that it can trigger
>> > only on x percent of queries,
>> That sounds reasonable ...
> Cool, I'll cook that up then. Thanks for the sanity check.

OK, here we go.

To make sure it doesn't trigger on all backends at once, and to ensure it
doesn't rely on a shared point of contention in shmem, this sets up a
counter with a random value on each backend start.

Because it needs to either always run both the Start and End hooks, or run
neither, this doesn't count nested statements for sampling purposes. So if
you run my_huge_plpgsql_function() then either all its statements will be
explained or none of them will. This only applies if nested statement
explain is enabled. It's possible to get around this by adding a separate
nested statement counter that's reset at each top level End hook, but it
doesn't seem worthwhile.

The sample rate has no effect on ANALYZE, which remains enabled or disabled
for all queries. I don't see any point adding a separate sample rate
control to ANALYZE only some sub-proportion of EXPLAINed statements.

Craig Ringer
PostgreSQL Development, 24x7 Support, Tra

Attachment Content-Type Size
0001-Allow-sampling-of-only-some-queries-by-auto_explain.patch text/x-patch 4.9 KB

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Michael Paquier 2015-06-03 11:34:38 Re: why does txid_current() assign new transaction-id?
Previous Message Thomas Munro 2015-06-03 08:48:09 Re: Re: [GENERAL] 9.4.1 -> 9.4.2 problem: could not access status of transaction 1