public inbox for [email protected]  

help / color / mirror / Atom feed

From: Yuri Kutsko <[email protected]>
To: [email protected]
Subject: EXPLAIN(GENERIC_PLAN) failing for some queries
Date: Wed, 7 Jan 2026 14:57:27 -0800
Message-ID: <CABvjvhruTCeDfx6XMoG5xEY74tJHip1zxHny6TFXEruL4=sKsQ@mail.gmail.com> (raw)

I want to identify every query in my company’s database that potentially
uses full table scans.
To do this, I wrote a simple PL/pgSQL function that generates an execution
plan for each query from pg_stat_statements and then searches the plan for
the 'Seq Scan' pattern.
Most queries contain parameters, so I use EXPLAIN (GENERIC_PLAN). This
works for approximately 80% of queries, but it fails in the following
scenarios:

1. Planner cannot infer parameter types without explicit casts
The following fails: EXPLAIN (GENERIC_PLAN) SELECT * FROM tbl WHERE id = $1
+ $2;
The following works: EXPLAIN (GENERIC_PLAN) SELECT * FROM tbl WHERE id =
$1::integer + $2::integer;

2. Parameter is used in EXTRACT
The following fails: EXPLAIN (GENERIC_PLAN) SELECT * FROM tbl WHERE
EXTRACT($1 FROM date_col) = 1;
The following works: EXPLAIN (GENERIC_PLAN) SELECT * FROM tbl WHERE
date_part($1, date_col) = 1;

3. Parameter is used in type 'string' notation
The following fails: EXPLAIN (GENERIC_PLAN) SELECT int $1;
The following works: EXPLAIN (GENERIC_PLAN) SELECT $1::int;

I modified my procedure to account for these scenarios, but I am not sure
whether there are other issues with EXPLAIN (GENERIC_PLAN) that I am not
aware of.
Problem #1 is mentioned in the EXPLAIN documentation
(https://www.postgresql.org/docs/18/sql-explain.html) at the bottom of the
page, but the other cases are not.
My questions are:
Are all of the issues described above expected behavior for EXPLAIN
(GENERIC_PLAN)?
Are there other known scenarios where EXPLAIN (GENERIC_PLAN) will fail?
Should the EXPLAIN documentation be updated to list scenarios in which
EXPLAIN (GENERIC_PLAN) can fail?
Yuri Kutsko

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: EXPLAIN(GENERIC_PLAN) failing for some queries
  In-Reply-To: <CABvjvhruTCeDfx6XMoG5xEY74tJHip1zxHny6TFXEruL4=sKsQ@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox