F.3. auto_dump — extension with ProcessInterrupts hook#

F.3. auto_dump — extension with ProcessInterrupts hook
F.3. auto_dump — extension with ProcessInterrupts hook
Prev UpAppendix F. Additional Supplied Modules and ExtensionsHome Next

F.3. auto_dump — extension with ProcessInterrupts hook #

F.3.1. Overview
F.3.2. Requirements
F.3.3. Enabling the extension
F.3.4. Configuration (GUCs)
F.3.5. Triggering modes
F.3.6. What gets dumped
F.3.7. Output directory & file naming
F.3.8. Usage example (from real 1C-like workload)
F.3.9. Tuning & recommendations
F.3.10. Troubleshooting

F.3.1. Overview #

auto_dump helps you capture a self-contained reproduction of problematic queries that run in production (e.g., from 1C).

When a trigger condition is met, the extension writes:

  • DDL for the tables used by the query (temp and/or persistent — configurable)

  • Optional data dumps (INSERT statements)

  • Optional index creation commands

  • Optional query text

  • Optional execution plan(s): EXPLAIN and EXPLAIN (ANALYZE, BUFFERS, WAL, TIMING, SUMMARY)

Dumps are written under a per-query directory named: 

<PID>-YYYY_MM_DD_hh_mm_ss-<counter>/

Example files: 

create.sql
createwithdata.sql

Note

Plan dumping and the “dump on cancel” trigger require a small core patch that adds ProcessInterrupts_hook. You must apply and build Tantor SE with that patch for these features to work.

F.3.2. Requirements #

  • Tantor SE version matching the patches/sources you build against.

  • C toolchain for building Tantor SE extensions.

  • Core patch (adds ProcessInterrupts_hook) if you want:

    • dump-on-cancel (auto_dump.dump_on_cancel)

    • plan collection with instrumentation (auto_dump.dump_plan = on or auto_dump.dump_on_bad_plan = on)

F.3.3. Enabling the extension #

  1. Add to postgresql.conf (cluster-wide): 

    shared_preload_libraries = 'auto_dump'
auto_dump.enable = on
auto_dump.output_directory = '/var/lib/postgresql/auto_dump_files/'

  2. Restart Tantor SE: 

    systemctl restart tantor-se-server-17

  3. In the target database: 

    CREATE EXTENSION auto_dump;

F.3.4. Configuration (GUCs) #

All GUCs are SUSET (settable by superuser at runtime unless otherwise restricted). Defaults reflect the current code.

F.3.4.1. Core switches #

GUC Type Default Description
auto_dump.enable bool off Master switch to enable dumping.
auto_dump.output_directory string '' Directory where dump folders are created (required).
auto_dump.dump_query bool on Include original query text (as a comment block).
auto_dump.dump_create bool on Include table creation statements.
auto_dump.dump_indexes bool on Include index creation statements.
auto_dump.dump_data bool on Include table data (INSERTs).
auto_dump.dump_plan bool on Include EXPLAIN and EXPLAIN ANALYZE output. Requires instrumentation.

F.3.4.2. Table coverage #

GUC Type Default Description
auto_dump.dump_temporary_tables bool true Dump temporary tables referenced by the query.
auto_dump.dump_persistent_tables bool false Dump persistent tables referenced by the query.
auto_dump.dump_all_temp_tables bool false Dump all temp tables in the backend session, not only those referenced in the triggering query.

F.3.4.3. Triggers #

GUC Type Default Description
auto_dump.dump_on_query_string string '' If non-empty, dump when the query text contains this substring or substrings.
auto_dump.dump_on_cancel bool false Dump when the current query is canceled. Requires core hook patch.
auto_dump.dump_on_bad_plan bool false Dump when the executed plan is considered “bad” (see thresholds below). Requires instrumentation.
F.3.4.3.1. dump_on_query_string: multiple substrings with | #

You can provide multiple alternatives for the substring trigger by separating them with a pipe (|): 

CREATE TABLE AAA(BBB int, CCC int);

SET auto_dump.dump_on_query_string = 'AAA';
-- Triggers (contains 'AAA') - default work
SELECT 1 FROM AAA;

SET auto_dump.dump_on_query_string = 'AAA|BBB|CCC';

-- Triggers (contains 'AAA' or 'BBB' or 'CCC')
SELECT 1 FROM AAA;
SELECT BBB FROM AAA;
-- Does not trigger (different case):
SELECT ccc FROM aaa;

-- To match case variants, list them explicitly:
SET auto_dump.dump_on_query_string = 'AAA|BBB|CCC|aaa|bbb|ccc';

F.3.4.4. “Bad plan” thresholds #

A plan node is “bad” only if both enabled thresholds are passed (logical AND). You can disable a threshold by setting it to 0 or below.

GUC Type Default Meaning
auto_dump.bad_plan_count_threshold int 0 Absolute difference between estimated and actual rows to trigger. > 0 enables; <= 0 disables.
auto_dump.bad_plan_percent_threshold int 0 Percent difference to trigger. > 0 enables; <= 0 disables. Percentage is evaluated only if estimated > 0 to avoid division by zero artifacts.
auto_dump.bad_plan_min_expected_rows int 100 Minimum estimated rows floor. Nodes with both estimated and actual rows below floors are ignored (noise suppression).
auto_dump.bad_plan_min_actual_rows int 100 Minimum actual rows floor (pairs with the estimated rows floor).

Note

On noisy 1C workloads, start with: 

auto_dump.dump_on_bad_plan = on
auto_dump.bad_plan_min_expected_rows = 100
auto_dump.bad_plan_min_actual_rows   = 100
auto_dump.bad_plan_count_threshold   = 1000
auto_dump.bad_plan_percent_threshold = 300

If queries often have estimate = 0 and you still want to trigger on large absolute mismatches, disable the percent threshold: 

auto_dump.bad_plan_percent_threshold = 0

F.3.5. Triggering modes #

  • By substring: if auto_dump.dump_on_query_string is a non-empty string and the query text contains it, a dump is created.

  • On cancel: if auto_dump.dump_on_cancel = on, a dump is created when pg_cancel_backend() interrupts the running query (core hook required).

  • On “bad plan”: if auto_dump.dump_on_bad_plan = on, the executor inspects instrumentation counters after execution and triggers if thresholds are met.

F.3.6. What gets dumped #

  • create.sql: schema objects (tables, indexes), ANALYZE, and—in a comment block—the EXPLAIN text if dump_plan = on and EXPLAIN was requested without data.

  • createwithdata.sql: same as create.sql, plus INSERT statements with the captured rows (when dump_data = on).

  • The original query text is included as a comment block when dump_query = on.

By default, only temporary tables are dumped (auto_dump.dump_temporary_tables = on, auto_dump.dump_persistent_tables = off). Enable persistent tables if you need full reproduction outside of the original session.

F.3.7. Output directory & file naming #

  • Base directory: auto_dump.output_directory (must be writable by the Database server OS user).

  • Each dump creates a subdirectory: 

    <PID>-YYYY_MM_DD_hh_mm_ss-<counter>/

  • Files inside: 

    create.sql
createwithdata.sql

F.3.8. Usage example (from real 1C-like workload) #

We have an SQL text that we need to reproduce and debug: 

SELECT
T1._Q_000_F_001RRef,
T1._Q_000_F_002RRef,
CASE WHEN (T1._Q_000_F_003RRef IN ('\\261\\321\\276\\257\\272\\331\\300wM\\263\\336*q\\031I\\316'::bytea, '\\247\\202\\266$\\366\\301r\\343N\\300\\333\\\\m1\\221\\330'::bytea, '\\235\\352S\\336\\242/$\\372L\\011\\237\\202K\\246\\332n'::bytea)) THEN SUM(T2._Fld33213) WHEN (T1._Q_000_F_003RRef IN ('\\200\\357\\030o\\256\\333\\332W@\\263\\231\\3626\\013\\334t'::bytea)) THEN SUM(T4._Fld33309) END,
T1._Q_000_F_000RRef
FROM pg_temp.tt7 T1
LEFT OUTER JOIN _Document1328_VT33208X1 T2
LEFT OUTER JOIN _Document1328X1 T3
ON (T2._Document1328_IDRRef = T3._IDRRef) AND (T3._Fld2488 = CAST(0 AS NUMERIC))
ON ((T1._Q_000_F_000RRef = T3._Fld33135RRef) AND (T1._Q_000_F_001RRef = T2._Fld33210RRef) AND (T1._Q_000_F_002RRef = T2._Fld33211RRef) AND (T2._Fld33240 = FALSE)) AND (T2._Fld2488 = CAST(0 AS NUMERIC))
LEFT OUTER JOIN _Document1328_VT33302X1 T4
LEFT OUTER JOIN _Document1328X1 T5
ON (T4._Document1328_IDRRef = T5._IDRRef) AND (T5._Fld2488 = CAST(0 AS NUMERIC))
ON ((T1._Q_000_F_000RRef = T5._Fld33135RRef) AND (T1._Q_000_F_001RRef = T4._Fld33304RRef) AND (T1._Q_000_F_002RRef = T4._Fld33305RRef) AND (T4._Fld77517 = FALSE)) AND (T4._Fld2488 = CAST(0 AS NUMERIC))
GROUP BY T1._Q_000_F_000RRef,
T1._Q_000_F_003RRef,
T1._Q_000_F_001RRef,
T1._Q_000_F_002RRef;

In postgresql.conf, enable the extension, set the dump directory, and define a substring that should trigger the dump for this query: 

shared_preload_libraries = 'auto_dump'
auto_dump.enable = on
auto_dump.output_directory = '/var/lib/postgresql/auto_dump_files/'
auto_dump.dump_on_query_string = 'LEFT OUTER JOIN _Document1328_VT33208X1 T2'

Restart the Tantor SE service: 

systemctl restart tantor-se-server-17

Create the extension in the database where the target query runs: 

-- psql
\c erp
CREATE EXTENSION auto_dump;

After a user in 1C triggers the business action that runs the query, a new directory appears under /var/lib/postgresql/auto_dump_files/: 

/var/lib/postgresql/auto_dump_files/984933-2025_10_30_15_08_04_04/

Files in that directory: 

-rw------- 1 postgres postgres 12906 Oct 30 15:08 create.sql
-rw------- 1 postgres postgres 13290 Oct 30 15:08 createwithdata.sql

Difference between files:

  • create.sql – DDL (and comments with the captured query/plan if enabled)

  • createwithdata.sql – everything in create.sql plus INSERT statements with captured rows

By default only temporary tables are dumped (auto_dump.dump_temporary_tables = on). You will see data like: 

CREATE TEMPORARY TABLE tt7 (
    _q_000_f_000rref bytea,
    _q_000_f_001rref bytea,
    _q_000_f_002rref bytea,
    _q_000_f_003rref bytea,
    _q_000_f_004 numeric(10,0),
    _q_000_f_005 timestamp without time zone,
    _q_000_f_006 timestamp without time zone,
    _q_000_f_007 timestamp without time zone
);

CREATE INDEX tmpind_0 ON pg_temp.tt7 USING btree (_q_000_f_000rref);INSERT INTO tt7 (_q_000_f_000rref, _q_000_f_001rref, _q_000_f_002rref, _q_000_f_003rref, _q_000_f_004, _q_000_f_005, _q_000_f_006, _q_000_f_007) VALUES (E'\\x98891866dab152db11eedf71f5f2a486',E'\\x83b11866dab152db11ee95becd03e9a6',E'\\x00000000000000000000000000000000',E'\\xb1d1beafbad9c0774db3de2a711949ce',0,'2024-03-12 00:00:00','2024-03-13 00:00:00','2024-03-12 00:00:00');

ANALYZE tt7;
/*
SELECT
T1._Q_000_F_001RRef,
T1._Q_000_F_002RRef,
CASE WHEN (T1._Q_000_F_003RRef IN ('\261\321\276\257\272\331\300wM\263\336*q\031I\316'::bytea, '\247\202\266$\366\301r\343N\300\333\\m1\221\330'::bytea, '\235\352S\336\242/$\372L\011\237\202K\246\332n'::bytea)) THEN SUM(T2._Fld33213) WHEN (T1._Q_000_F_003RRef IN ('\200\357\030o\256\333\332W@\263\231\3626\013\334t'::bytea)) THEN SUM(T4._Fld33309) END,
T1._Q_000_F_000RRef
FROM pg_temp.tt7 T1
LEFT OUTER JOIN _Document1328_VT33208X1 T2
LEFT OUTER JOIN _Document1328X1 T3
ON (T2._Document1328_IDRRef = T3._IDRRef) AND (T3._Fld2488 = CAST(0 AS NUMERIC))
ON ((T1._Q_000_F_000RRef = T3._Fld33135RRef) AND (T1._Q_000_F_001RRef = T2._Fld33210RRef) AND (T1._Q_000_F_002RRef = T2._Fld33211RRef) AND (T2._Fld33240 = FALSE)) AND (T2._Fld2488 = CAST(0 AS NUMERIC))
LEFT OUTER JOIN _Document1328_VT33302X1 T4
LEFT OUTER JOIN _Document1328X1 T5
ON (T4._Document1328_IDRRef = T5._IDRRef) AND (T5._Fld2488 = CAST(0 AS NUMERIC))
ON ((T1._Q_000_F_000RRef = T5._Fld33135RRef) AND (T1._Q_000_F_001RRef = T4._Fld33304RRef) AND (T1._Q_000_F_002RRef = T4._Fld33305RRef) AND (T4._Fld77517 = FALSE)) AND (T4._Fld2488 = CAST(0 AS NUMERIC))
GROUP BY T1._Q_000_F_000RRef,
T1._Q_000_F_003RRef,
T1._Q_000_F_001RRef,
T1._Q_000_F_002RRef;*/
/*
Query Text: SELECT
T1._Q_000_F_001RRef,
T1._Q_000_F_002RRef,
CASE WHEN (T1._Q_000_F_003RRef IN ('\\261\\321\\276\\257\\272\\331\\300wM\\263\\336*q\\031I\\316'::bytea, '\\247\\202\\266$\\366\\301r\\343N\\300\\333\\\\m1\\221\\330'::bytea, '\\235\\352S\\336\\242/$\\372L\\011\\237\\202K\\246\\332n'::bytea)) THEN SUM(T2._Fld33213) WHEN (T1._Q_000_F_003RRef IN ('\\200\\357\\030o\\256\\333\\332W@\\263\\231\\3626\\013\\334t'::bytea)) THEN SUM(T4._Fld33309) END,
T1._Q_000_F_000RRef
FROM pg_temp.tt7 T1
LEFT OUTER JOIN _Document1328_VT33208X1 T2
LEFT OUTER JOIN _Document1328X1 T3
ON (T2._Document1328_IDRRef = T3._IDRRef) AND (T3._Fld2488 = CAST(0 AS NUMERIC))
ON ((T1._Q_000_F_000RRef = T3._Fld33135RRef) AND (T1._Q_000_F_001RRef = T2._Fld33210RRef) AND (T1._Q_000_F_002RRef = T2._Fld33211RRef) AND (T2._Fld33240 = FALSE)) AND (T2._Fld2488 = CAST(0 AS NUMERIC))
LEFT OUTER JOIN _Document1328_VT33302X1 T4
LEFT OUTER JOIN _Document1328X1 T5
ON (T4._Document1328_IDRRef = T5._IDRRef) AND (T5._Fld2488 = CAST(0 AS NUMERIC))
ON ((T1._Q_000_F_000RRef = T5._Fld33135RRef) AND (T1._Q_000_F_001RRef = T4._Fld33304RRef) AND (T1._Q_000_F_002RRef = T4._Fld33305RRef) AND (T4._Fld77517 = FALSE)) AND (T4._Fld2488 = CAST(0 AS NUMERIC))
GROUP BY T1._Q_000_F_000RRef,
T1._Q_000_F_003RRef,
T1._Q_000_F_001RRef,
T1._Q_000_F_002RRef
GroupAggregate  (cost=5.02..5.04 rows=1 width=100)
  Group Key: t1._q_000_f_000rref, t1._q_000_f_003rref, t1._q_000_f_001rref, t1._q_000_f_002rref
  ->  Sort  (cost=5.02..5.02 rows=1 width=78)
        Sort Key: t1._q_000_f_000rref, t1._q_000_f_003rref, t1._q_000_f_001rref, t1._q_000_f_002rref
        ->  Nested Loop Left Join  (cost=0.73..5.00 rows=1 width=78)
              ->  Nested Loop Left Join  (cost=0.39..3.41 rows=1 width=73)
                    ->  Seq Scan on tt7 t1  (cost=0.00..1.01 rows=1 width=68)
                    ->  Nested Loop  (cost=0.39..2.39 rows=1 width=56)
                          Join Filter: ((t1._q_000_f_001rref = t4._fld33304rref) AND (t1._q_000_f_002rref = t4._fld33305rref))
                          ->  Index Only Scan using _document1328_4x1 on _document1328x1 t5  (cost=0.17..1.29 rows=1 width=34)
                                Index Cond: ((_fld2488 = '0'::numeric) AND (_fld33135rref = t1._q_000_f_000rref))
                          ->  Index Scan using _document1328_vt33302_skx1 on _document1328_vt33302x1 t4  (cost=0.22..0.93 rows=14 width=56)
                                Index Cond: ((_fld2488 = '0'::numeric) AND (_document1328_idrref = t5._idrref))
                                Filter: (NOT _fld77517)
              ->  Nested Loop  (cost=0.34..1.58 rows=1 width=56)
                    Join Filter: ((t1._q_000_f_001rref = t2._fld33210rref) AND (t1._q_000_f_002rref = t2._fld33211rref))
                    ->  Index Only Scan using _document1328_4x1 on _document1328x1 t3  (cost=0.17..1.29 rows=1 width=34)
                          Index Cond: ((_fld2488 = '0'::numeric) AND (_fld33135rref = t1._q_000_f_000rref))
                    ->  Index Scan using _document1328_vt33208_skx1 on _document1328_vt33208x1 t2  (cost=0.17..0.28 rows=1 width=56)
                          Index Cond: ((_fld2488 = '0'::numeric) AND (_document1328_idrref = t3._idrref))
                          Filter: (NOT _fld33240)
*/
/*
Query Text: SELECT
T1._Q_000_F_001RRef,
T1._Q_000_F_002RRef,
CASE WHEN (T1._Q_000_F_003RRef IN ('\\261\\321\\276\\257\\272\\331\\300wM\\263\\336*q\\031I\\316'::bytea, '\\247\\202\\266$\\366\\301r\\343N\\300\\333\\\\m1\\221\\330'::bytea, '\\235\\352S\\336\\242/$\\372L\\011\\237\\202K\\246\\332n'::bytea)) THEN SUM(T2._Fld33213) WHEN (T1._Q_000_F_003RRef IN ('\\200\\357\\030o\\256\\333\\332W@\\263\\231\\3626\\013\\334t'::bytea)) THEN SUM(T4._Fld33309) END,
T1._Q_000_F_000RRef
FROM pg_temp.tt7 T1
LEFT OUTER JOIN _Document1328_VT33208X1 T2
LEFT OUTER JOIN _Document1328X1 T3
ON (T2._Document1328_IDRRef = T3._IDRRef) AND (T3._Fld2488 = CAST(0 AS NUMERIC))
ON ((T1._Q_000_F_000RRef = T3._Fld33135RRef) AND (T1._Q_000_F_001RRef = T2._Fld33210RRef) AND (T1._Q_000_F_002RRef = T2._Fld33211RRef) AND (T2._Fld33240 = FALSE)) AND (T2._Fld2488 = CAST(0 AS NUMERIC))
LEFT OUTER JOIN _Document1328_VT33302X1 T4
LEFT OUTER JOIN _Document1328X1 T5
ON (T4._Document1328_IDRRef = T5._IDRRef) AND (T5._Fld2488 = CAST(0 AS NUMERIC))
ON ((T1._Q_000_F_000RRef = T5._Fld33135RRef) AND (T1._Q_000_F_001RRef = T4._Fld33304RRef) AND (T1._Q_000_F_002RRef = T4._Fld33305RRef) AND (T4._Fld77517 = FALSE)) AND (T4._Fld2488 = CAST(0 AS NUMERIC))
GROUP BY T1._Q_000_F_000RRef,
T1._Q_000_F_003RRef,
T1._Q_000_F_001RRef,
T1._Q_000_F_002RRef
GroupAggregate  (cost=5.02..5.04 rows=1 width=100) (actual time=0.125..0.127 rows=1 loops=1)
  Output: t1._q_000_f_001rref, t1._q_000_f_002rref, CASE WHEN (t1._q_000_f_003rref = ANY ('{"\\\\xb1d1beafbad9c0774db3de2a711949ce","\\\\xa782b624f6c172e34ec0db5c6d3191d8","\\\\x9dea53dea22f24fa4c099f824ba6da6e"}'::bytea[])) THEN sum(t2._fld33213) WHEN (t1._q_000_f_003rref = '\\x80ef186faedbda5740b399f2360bdc74'::bytea) THEN sum(t4._fld33309) ELSE NULL::numeric END, t1._q_000_f_000rref, t1._q_000_f_003rref
  Group Key: t1._q_000_f_000rref, t1._q_000_f_003rref, t1._q_000_f_001rref, t1._q_000_f_002rref
  Buffers: shared hit=20, local hit=1
  ->  Sort  (cost=5.02..5.02 rows=1 width=78) (actual time=0.113..0.114 rows=1 loops=1)
        Output: t1._q_000_f_001rref, t1._q_000_f_002rref, t1._q_000_f_000rref, t1._q_000_f_003rref, t2._fld33213, t4._fld33309
        Sort Key: t1._q_000_f_000rref, t1._q_000_f_003rref, t1._q_000_f_001rref, t1._q_000_f_002rref
        Sort Method: quicksort  Memory: 25kB
        Buffers: shared hit=20, local hit=1
        ->  Nested Loop Left Join  (cost=0.73..5.00 rows=1 width=78) (actual time=0.097..0.099 rows=1 loops=1)
              Output: t1._q_000_f_001rref, t1._q_000_f_002rref, t1._q_000_f_000rref, t1._q_000_f_003rref, t2._fld33213, t4._fld33309
              Buffers: shared hit=20, local hit=1
              ->  Nested Loop Left Join  (cost=0.39..3.41 rows=1 width=73) (actual time=0.074..0.075 rows=1 loops=1)
                    Output: t1._q_000_f_001rref, t1._q_000_f_002rref, t1._q_000_f_003rref, t1._q_000_f_000rref, t4._fld33309
                    Buffers: shared hit=12, local hit=1
                    ->  Seq Scan on pg_temp.tt7 t1  (cost=0.00..1.01 rows=1 width=68) (actual time=0.008..0.009 rows=1 loops=1)
                          Output: t1._q_000_f_000rref, t1._q_000_f_001rref, t1._q_000_f_002rref, t1._q_000_f_003rref, t1._q_000_f_004, t1._q_000_f_005, t1._q_000_f_006, t1._q_000_f_007
                          Buffers: local hit=1
                    ->  Nested Loop  (cost=0.39..2.39 rows=1 width=56) (actual time=0.063..0.064 rows=0 loops=1)
                          Output: t4._fld33309, t4._fld33304rref, t4._fld33305rref, t5._fld33135rref
                          Join Filter: ((t1._q_000_f_001rref = t4._fld33304rref) AND (t1._q_000_f_002rref = t4._fld33305rref))
                          Rows Removed by Join Filter: 7
                          Buffers: shared hit=12
                          ->  Index Only Scan using _document1328_4x1 on public._document1328x1 t5  (cost=0.17..1.29 rows=1 width=34) (actual time=0.030..0.030 rows=1 loops=1)
                                Output: t5._fld2488, t5._fld33135rref, t5._idrref
                                Index Cond: ((t5._fld2488 = '0'::numeric) AND (t5._fld33135rref = t1._q_000_f_000rref))
                                Heap Fetches: 0
                                Buffers: shared hit=4
                          ->  Index Scan using _document1328_vt33302_skx1 on public._document1328_vt33302x1 t4  (cost=0.22..0.93 rows=14 width=56) (actual time=0.022..0.030 rows=7 loops=1)
                                Output: t4._document1328_idrref, t4._fld2488, t4._keyfield, t4._lineno33303, t4._fld33304rref, t4._fld33305rref, t4._fld33306rref, t4._fld33307rref, t4._fld33308, t4._fld33309, t4._fld33310rref, t4._fld33311, t4._fld33312rref, t4._fld33313, t4._fld33314rref, t4._fld33315rref, t4._fld112251rref, t4._fld33316, t4._fld33317, t4._fld33318, t4._fld33319, t4._fld33320, t4._fld33321, t4._fld33322, t4._fld33323, t4._fld33325rref, t4._fld101275, t4._fld77517, t4._fld77518rref, t4._fld77519, t4._fld33324rref, t4._fld77520rref, t4._fld101276rref, t4._fld81551, t4._fld88569rref, t4._fld112252, t4._fld112253
                                Index Cond: ((t4._fld2488 = '0'::numeric) AND (t4._document1328_idrref = t5._idrref))
                                Filter: (NOT t4._fld77517)
                                Buffers: shared hit=8
              ->  Nested Loop  (cost=0.34..1.58 rows=1 width=56) (actual time=0.021..0.022 rows=1 loops=1)
                    Output: t2._fld33213, t2._fld33210rref, t2._fld33211rref, t3._fld33135rref
                    Join Filter: ((t1._q_000_f_001rref = t2._fld33210rref) AND (t1._q_000_f_002rref = t2._fld33211rref))
                    Buffers: shared hit=8
                    ->  Index Only Scan using _document1328_4x1 on public._document1328x1 t3  (cost=0.17..1.29 rows=1 width=34) (actual time=0.005..0.005 rows=1 loops=1)
                          Output: t3._fld2488, t3._fld33135rref, t3._idrref
                          Index Cond: ((t3._fld2488 = '0'::numeric) AND (t3._fld33135rref = t1._q_000_f_000rref))
                          Heap Fetches: 0
                          Buffers: shared hit=4
                    ->  Index Scan using _document1328_vt33208_skx1 on public._document1328_vt33208x1 t2  (cost=0.17..0.28 rows=1 width=56) (actual time=0.014..0.014 rows=1 loops=1)
                          Output: t2._document1328_idrref, t2._fld2488, t2._keyfield, t2._lineno33209, t2._fld33210rref, t2._fld33211rref, t2._fld33212, t2._fld33213, t2._fld33214rref, t2._fld33215_type, t2._fld33215_rtref, t2._fld33215_rrref, t2._fld33216rref, t2._fld33217rref, t2._fld33218, t2._fld33219, t2._fld33220, t2._fld33221, t2._fld33222, t2._fld33223, t2._fld33224, t2._fld33225, t2._fld33226_type, t2._fld33226_rtref, t2._fld33226_rrref, t2._fld33227_type, t2._fld33227_rtref, t2._fld33227_rrref, t2._fld33236_type, t2._fld33236_rtref, t2._fld33236_rrref, t2._fld33229, t2._fld33230rref, t2._fld33231, t2._fld33232rref, t2._fld33233_type, t2._fld33233_rtref, t2._fld33233_rrref, t2._fld33234_type, t2._fld33234_rtref, t2._fld33234_rrref, t2._fld33235_type, t2._fld33235_rtref, t2._fld33235_rrref, t2._fld33237, t2._fld33238, t2._fld33239rref, t2._fld33240, t2._fld33241rref, t2._fld33242rref, t2._fld77511, t2._fld77512, t2._fld77513, t2._fld88563rref, t2._fld88564rref, t2._fld104367, t2._fld104368, t2._fld112249, t2._fld81859
                          Index Cond: ((t2._fld2488 = '0'::numeric) AND (t2._document1328_idrref = t3._idrref))
                          Filter: (NOT t2._fld33240)
                          Buffers: shared hit=4
Settings: max_parallel_workers_per_gather = '0', effective_io_concurrency = '128', maintenance_io_concurrency = '128', random_page_cost = '1.1', from_collapse_limit = '20', join_collapse_limit = '20', max_parallel_workers = '13', hash_mem_multiplier = '8', temp_buffers = '189MB', work_mem = '378MB', effective_cache_size = '279GB', selectivity_model = '1c', enable_mergejoin = 'off', cpu_operator_cost = '0.001'
*/

F.3.9. Tuning & recommendations #

  • Noisy environments (1C): use floors to suppress tiny nodes: 

    auto_dump.dump_on_bad_plan = on
auto_dump.bad_plan_min_expected_rows = 100
auto_dump.bad_plan_min_actual_rows   = 100
auto_dump.bad_plan_count_threshold   = 1000
auto_dump.bad_plan_percent_threshold = 300

  • If many plans have estimate = 0 and you still want to catch them by absolute error, disable the percent threshold: 

    auto_dump.bad_plan_percent_threshold = 0

  • To limit dump volume during triage: 

    auto_dump.dump_plan = off
auto_dump.dump_persistent_tables = off
auto_dump.dump_all_temp_tables = off

F.3.10. Troubleshooting #

  • No dumps appear:

    • Check auto_dump.enable = on and auto_dump.output_directory is set and writable.

    • Ensure shared_preload_libraries includes auto_dump and the server was restarted.

    • Verify the trigger condition (substring / cancel / bad plan) is actually met.

  • Server crashes on cancel or plan dump:

    • Make sure you are running a server built with the ProcessInterrupts_hook patch.

  • Too many dumps (storm):

    • Increase floors and thresholds (see tuning).

    • Use substring trigger for narrow targeting while investigating.

  • Persistent tables are missing from dumps:

    • Set auto_dump.dump_persistent_tables = on.

Prev Up Next
F.2. auth_delay — pause on authentication failure Home F.4. auto_explain — log execution plans of slow queries