MIME-Version: 1.0
From: Yuri Kutsko <ykutsko@gmail.com>
Date: Wed, 7 Jan 2026 14:57:27 -0800
Message-ID: <CABvjvhruTCeDfx6XMoG5xEY74tJHip1zxHny6TFXEruL4=sKsQ@mail.gmail.com>
Subject: EXPLAIN(GENERIC_PLAN) failing for some queries
To: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000175e4e0647d43846"
Archived-At: <https://www.postgresql.org/message-id/CABvjvhruTCeDfx6XMoG5xEY74tJHip1zxHny6TFXEruL4%3DsKsQ%40mail.gmail.com>
Precedence: bulk

--000000000000175e4e0647d43846
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

I want to identify every query in my company=E2=80=99s database that potent=
ially
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 =3D =
$1
+ $2;
The following works: EXPLAIN (GENERIC_PLAN) SELECT * FROM tbl WHERE id =3D
$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) =3D 1;
The following works: EXPLAIN (GENERIC_PLAN) SELECT * FROM tbl WHERE
date_part($1, date_col) =3D 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

--000000000000175e4e0647d43846
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">I want to identify every query in my company=E2=80=99s dat=
abase that potentially uses full table scans.<br>To do this, I wrote a simp=
le PL/pgSQL function that generates an execution plan for each query from p=
g_stat_statements and then searches the plan for the &#39;Seq Scan&#39; pat=
tern.<br>Most queries contain parameters, so I use EXPLAIN (GENERIC_PLAN). =
This works for approximately 80% of queries, but it fails in the following =
scenarios:<br><br>1. Planner cannot infer parameter types without explicit =
casts<br>The following fails: EXPLAIN (GENERIC_PLAN) SELECT * FROM tbl WHER=
E id =3D $1 + $2;<br>The following works: EXPLAIN (GENERIC_PLAN) SELECT * F=
ROM tbl WHERE id =3D $1::integer + $2::integer;<br><br>2. Parameter is used=
 in EXTRACT<br>The following fails: EXPLAIN (GENERIC_PLAN) SELECT * FROM tb=
l WHERE EXTRACT($1 FROM date_col) =3D 1;<br>The following works: EXPLAIN (G=
ENERIC_PLAN) SELECT * FROM tbl WHERE date_part($1, date_col) =3D 1;<br><br>=
3. Parameter is used in type &#39;string&#39; notation<br>The following fai=
ls: EXPLAIN (GENERIC_PLAN) SELECT int $1;<br>The following works: EXPLAIN (=
GENERIC_PLAN) SELECT $1::int;<br><br>I modified my procedure to account for=
 these scenarios, but I am not sure whether there are other issues with EXP=
LAIN (GENERIC_PLAN) that I am not aware of.<br>Problem #1 is mentioned in t=
he EXPLAIN documentation<br>(<a href=3D"https://www.postgresql.org/docs/18/=
sql-explain.html">https://www.postgresql.org/docs/18/sql-explain.html</a>) =
at the bottom of the page, but the other cases are not.<br>My questions are=
:<br>Are all of the issues described above expected behavior for EXPLAIN (G=
ENERIC_PLAN)?<br>Are there other known scenarios where EXPLAIN (GENERIC_PLA=
N) will fail?<br>Should the EXPLAIN documentation be updated to list scenar=
ios in which EXPLAIN (GENERIC_PLAN) can fail?<br>Yuri Kutsko<br></div>

--000000000000175e4e0647d43846--