Today you get
test=> EXPLAIN SELECT * FROM tab WHERE col = $1;
ERROR: there is no parameter $1
which makes sense. Nonetheless, it would be great to get a generic plan
for such a query. Sometimes you don't have the parameters (if you grab
the statement from "pg_stat_statements", or if it is fro
On Tue, 2022-12-27 at 14:37 -0800, Michel Pelletier wrote:
> I built and tested this patch for review and it works well, although I got
> the following warning when building:
>
> analyze.c: In function 'transformStmt':
> analyze.c:2919:35: warning: 'generic_plan' may be used uninitialized in this
Hi Laurenz,
I'm testing your patch and the GENERIC_PLAN parameter seems to work just
OK ..
db=# CREATE TABLE t (col numeric);
CREATE TABLE
db=# CREATE INDEX t_col_idx ON t (col);
CREATE INDEX
db=# INSERT INTO t SELECT random() FROM generate_series(1,10) ;
INSERT 0 10
db=# EXPLAIN (GENE
Jim Jones writes:
> However, when GENERIC_PLAN is used combined with BUFFERS, the 'Buffers'
> node is shown the first time the query executed in a session:
> psql (16devel)
> Type "help" for help.
> postgres=# \c db
> You are now connected to database "db" as user "postgres".
> db=# EXPLAIN (BU
Laurenz Albe writes:
> Today you get
> test=> EXPLAIN SELECT * FROM tab WHERE col = $1;
> ERROR: there is no parameter $1
> which makes sense. Nonetheless, it would be great to get a generic plan
> for such a query.
I can see the point, but it also seems like it risks masking stupid
mistakes.
On Tue, Oct 11, 2022 at 09:49:14AM -0400, Tom Lane wrote:
>
> If you're trying to investigate custom-plan behavior, then you
> need to supply concrete parameter values somewhere, so I think
> this approach is fine for that case. (Shoehorning parameter
> values into EXPLAIN options seems like it'd
On Wed, 2022-10-12 at 00:03 +0800, Julien Rouhaud wrote:
> On Tue, Oct 11, 2022 at 09:49:14AM -0400, Tom Lane wrote:
> > I think it might be better to drive it off an explicit EXPLAIN option,
> > perhaps
> >
> > EXPLAIN (GENERIC_PLAN) SELECT * FROM tab WHERE col = $1;
> >
> > If you're trying to i
Hi,
On Tue, Oct 25, 2022 at 11:08:27AM +0200, Laurenz Albe wrote:
>
> Here is a patch that
> implements it with an EXPLAIN option named GENERIC_PLAN.
I only have a quick look at the patch for now. Any reason why you don't rely
on the existing explain_filter() function for emitting stable output
On Tue, 2022-10-25 at 19:03 +0800, Julien Rouhaud wrote:
> On Tue, Oct 25, 2022 at 11:08:27AM +0200, Laurenz Albe wrote:
> > Here is a patch that
> > implements it with an EXPLAIN option named GENERIC_PLAN.
>
> I only have a quick look at the patch for now. Any reason why you don't rely
> on the
Laurenz Albe writes:
> [ 0001-Add-EXPLAIN-option-GENERIC_PLAN.v4.patch ]
I took a closer look at this patch, and didn't like the implementation
much. You're not matching the behavior of PREPARE at all: for example,
this patch is content to let $1 be resolved with different types in
different pla
On Tue, 2023-01-31 at 13:49 -0500, Tom Lane wrote:
> Laurenz Albe writes:
> > [ 0001-Add-EXPLAIN-option-GENERIC_PLAN.v4.patch ]
>
> I took a closer look at this patch, and didn't like the implementation
> much. You're not matching the behavior of PREPARE at all: for example,
> this patch is cont
Laurenz Albe writes:
> I played around with it, and I ran into a problem with partitions that
> are foreign tables:
> ...
> EXPLAIN (GENERIC_PLAN) SELECT * FROM looppart WHERE key = $1;
> ERROR: no value found for parameter 1
Hmm, offhand I'd say that something is doing something it has no
b
On Fri, 2023-02-03 at 09:44 -0500, Tom Lane wrote:
> Laurenz Albe writes:
> > I played around with it, and I ran into a problem with partitions that
> > are foreign tables:
> > ...
> > EXPLAIN (GENERIC_PLAN) SELECT * FROM looppart WHERE key = $1;
> > ERROR: no value found for parameter 1
>
>
Laurenz Albe writes:
> Thanks for the pointer. Perhaps something like the attached?
> The changes in "CreatePartitionPruneState" make my test case work,
> but they cause a difference in the regression tests, which would be
> intended if we have no partition pruning with plain EXPLAIN.
Hmm, so I
On Fri, 2023-02-03 at 15:11 -0500, Tom Lane wrote:
> I can think of a couple of possible ways forward:
>
> * Fix things so that the generic parameters appear to have NULL
> values when inspected during executor startup. I'm not sure
> how useful that'd be though. In partition-pruning cases that'
Hi,
On 2023-02-05 18:24:03 +0100, Laurenz Albe wrote:
> Anyway, attached is v7 that tries to do it that way.
This consistently fails on CI:
https://cirrus-ci.com/github/postgresql-cfbot/postgresql/commitfest%2F42%2F3962
https://api.cirrus-ci.com/v1/artifact/task/5156723929907200/testrun/build/te
On Mon, 2023-02-13 at 16:33 -0800, Andres Freund wrote:
> On 2023-02-05 18:24:03 +0100, Laurenz Albe wrote:
> > Anyway, attached is v7 that tries to do it that way.
>
> This consistently fails on CI:
> https://cirrus-ci.com/github/postgresql-cfbot/postgresql/commitfest%2F42%2F3962
>
> https://api
Hi,
On 2022-10-29 10:35:26 +0200, Laurenz Albe wrote:
> On Tue, 2022-10-25 at 19:03 +0800, Julien Rouhaud wrote:
> > On Tue, Oct 25, 2022 at 11:08:27AM +0200, Laurenz Albe wrote:
> > > Here is a patch that
> > > implements it with an EXPLAIN option named GENERIC_PLAN.
> >
> > I only have a quick
On Tue, 2022-12-06 at 10:17 -0800, Andres Freund wrote:
> On 2022-10-29 10:35:26 +0200, Laurenz Albe wrote:
> > > > Here is a patch that
> > > > implements it with an EXPLAIN option named GENERIC_PLAN.
>
> This fails to build the docs:
>
> https://cirrus-ci.com/task/5609301511766016
>
> [17:47:0
On Wed, Dec 7, 2022 at 3:23 AM Laurenz Albe
wrote:
> On Tue, 2022-12-06 at 10:17 -0800, Andres Freund wrote:
> > On 2022-10-29 10:35:26 +0200, Laurenz Albe wrote:
> > > > > Here is a patch that
> > > > > implements it with an EXPLAIN option named GENERIC_PLAN.
> >
> > This fails to build the docs
Hi,
I reviewed the patch and find the idea of allowing $placeholders with
EXPLAIN very useful, it will solve relevant real-world use-cases.
(Queries from pg_stat_statements, found in the log, or in application
code.)
I have some comments:
> This allows EXPLAIN to generate generic plans for param
Thanks for the review!
On Tue, 2023-03-21 at 16:32 +0100, Christoph Berg wrote:
> I have some comments:
>
> > This allows EXPLAIN to generate generic plans for parameterized statements
> > (that have parameter placeholders like $1 in the statement text).
>
> > +
> > + GENERIC_PLAN
> > +
And here is v10, which includes tab completion for the new option.
Yours,
Laurenz Albe
From dfe6d36d79c74fba7bf70b990fdada166d012ff4 Mon Sep 17 00:00:00 2001
From: Laurenz Albe
Date: Thu, 23 Mar 2023 19:28:49 +0100
Subject: [PATCH] Add EXPLAIN option GENERIC_PLAN
This allows EXPLAIN to generate
Re: Laurenz Albe
> And here is v10, which includes tab completion for the new option.
IMHO everything looks good now. Marking as ready for committer.
Thanks!
Christoph
Laurenz Albe writes:
> On Tue, 2023-03-21 at 16:32 +0100, Christoph Berg wrote:
>> The test involving postgres_fdw is still necessary to exercise the new
>> EXEC_FLAG_EXPLAIN_GENERIC code path, but needs to be moved elsewhere,
>> probably src/test/modules/.
> Tests for postgres_fdw are in contrib
Re: Tom Lane
> I don't actually see why a postgres_fdw test case is needed at all?
> The tests in explain.sql seem sufficient.
When I asked Laurenz about that he told me that local tables wouldn't
exercise the code specific for EXEC_FLAG_EXPLAIN_GENERIC.
(Admittedly my knowledge of the planner wa
Christoph Berg writes:
> Re: Tom Lane
>> I don't actually see why a postgres_fdw test case is needed at all?
>> The tests in explain.sql seem sufficient.
> When I asked Laurenz about that he told me that local tables wouldn't
> exercise the code specific for EXEC_FLAG_EXPLAIN_GENERIC.
But there
On Fri, 2023-03-24 at 16:41 -0400, Tom Lane wrote:
> Christoph Berg writes:
> > Re: Tom Lane
> > > I don't actually see why a postgres_fdw test case is needed at all?
> > > The tests in explain.sql seem sufficient.
>
> > When I asked Laurenz about that he told me that local tables wouldn't
> > ex
28 matches
Mail list logo