Message-ID: <d4a9c31ffcfdd68f1b66bdb66fd7b7680af99f96.camel@cybertec.at>
Subject: Re: EXPLAIN(GENERIC_PLAN) failing for some queries
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Yuri Kutsko <ykutsko@gmail.com>, pgsql-general@lists.postgresql.org
Date: Thu, 08 Jan 2026 09:27:16 +0100
In-Reply-To: <CABvjvhruTCeDfx6XMoG5xEY74tJHip1zxHny6TFXEruL4=sKsQ@mail.gmail.com>
References: 
	<CABvjvhruTCeDfx6XMoG5xEY74tJHip1zxHny6TFXEruL4=sKsQ@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.58.2 (3.58.2-1.fc43) 
MIME-Version: 1.0
Archived-At: <https://www.postgresql.org/message-id/d4a9c31ffcfdd68f1b66bdb66fd7b7680af99f96.camel%40cybertec.at>
Precedence: bulk

On Wed, 2026-01-07 at 14:57 -0800, Yuri Kutsko wrote:
> I want to identify every query in my company=E2=80=99s database that pote=
ntially uses full table scans.
> To do this, I wrote a simple PL/pgSQL function that generates an executio=
n plan for each query
> from pg_stat_statements and then searches the plan for the 'Seq Scan' pat=
tern.
> Most queries contain parameters, so I use EXPLAIN (GENERIC_PLAN). This wo=
rks for approximately
> 80% of queries, but it fails in the following scenarios:
>=20
> 1. Planner cannot infer parameter types without explicit casts
> The following fails: EXPLAIN (GENERIC_PLAN) SELECT * FROM tbl WHERE id =
=3D $1 + $2;
> The following works: EXPLAIN (GENERIC_PLAN) SELECT * FROM tbl WHERE id =
=3D $1::integer + $2::integer;
>=20
> 2. Parameter is used in EXTRACT
> The following fails: EXPLAIN (GENERIC_PLAN) SELECT * FROM tbl WHERE EXTRA=
CT($1 FROM date_col) =3D 1;
> The following works: EXPLAIN (GENERIC_PLAN) SELECT * FROM tbl WHERE date_=
part($1, date_col) =3D 1;
>=20
> 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;
>=20
> 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 th=
e page, but the other cases are not.
> My questions are:
> Are all of the issues described above expected behavior for EXPLAIN (GENE=
RIC_PLAN)?

Yes.

Note that your cases #2 and #3 are illegal SQL, since you cannot use a para=
meter in these places,
only string literals.  I'd say that your problem is that you are using stri=
ngs from pg_stat_statements,
which ignores the value of constants.  Replacing literals with placeholders=
 can result in incorrect
SQL statements.

> Are there other known scenarios where EXPLAIN (GENERIC_PLAN) will fail?

It is expected to fail for all statements with syntax errors...

> Should the EXPLAIN documentation be updated to list scenarios in which EX=
PLAIN (GENERIC_PLAN) can fail?

I don't think that #2 and #3 deserve documentation, and I'd say it doesn't =
need to be documented
that EXPLAIN will fail for syntactically incorrect SQL.

Yours,
Laurenz Albe