public inbox for [email protected]help / color / mirror / Atom feed
[PATCH v16] Add CopyFromRoutine/CopyToRountine 20+ messages / 10 participants [nested] [flat]
* [PATCH v16] Add CopyFromRoutine/CopyToRountine @ 2024-03-04 04:52 Sutou Kouhei <[email protected]> 0 siblings, 0 replies; 20+ messages in thread From: Sutou Kouhei @ 2024-03-04 04:52 UTC (permalink / raw) They are for implementing custom COPY FROM/TO format. But this is not enough to implement custom COPY FROM/TO format yet. We'll export some APIs to receive/send data and add "format" option to COPY FROM/TO later. Existing text/csv/binary format implementations don't use CopyFromRoutine/CopyToRoutine for now. We have a patch for it but we defer it. Because there are some mysterious profile results in spite of we get faster runtimes. See [1] for details. [1] https://www.postgresql.org/message-id/ZdbtQJ-p5H1_EDwE%40paquier.xyz Note that this doesn't change existing text/csv/binary format implementations. There are many diffs for CopyOneRowTo() but they're caused by indentation. They don't change implementations. --- src/backend/commands/copyfrom.c | 22 ++++- src/backend/commands/copyfromparse.c | 5 ++ src/backend/commands/copyto.c | 21 +++++ src/include/commands/copyapi.h | 100 +++++++++++++++++++++++ src/include/commands/copyfrom_internal.h | 4 + src/tools/pgindent/typedefs.list | 2 + 6 files changed, 151 insertions(+), 3 deletions(-) create mode 100644 src/include/commands/copyapi.h diff --git a/src/backend/commands/copyfrom.c b/src/backend/commands/copyfrom.c index c3bc897028..9bf2f6497e 100644 --- a/src/backend/commands/copyfrom.c +++ b/src/backend/commands/copyfrom.c @@ -1623,12 +1623,22 @@ BeginCopyFrom(ParseState *pstate, /* Fetch the input function and typioparam info */ if (cstate->opts.binary) + { getTypeBinaryInputInfo(att->atttypid, &in_func_oid, &typioparams[attnum - 1]); + fmgr_info(in_func_oid, &in_functions[attnum - 1]); + } + else if (cstate->routine) + cstate->routine->CopyFromInFunc(cstate, att->atttypid, + &in_functions[attnum - 1], + &typioparams[attnum - 1]); + else + { getTypeInputInfo(att->atttypid, &in_func_oid, &typioparams[attnum - 1]); fmgr_info(in_func_oid, &in_functions[attnum - 1]); + } /* Get default info if available */ defexprs[attnum - 1] = NULL; @@ -1768,10 +1778,13 @@ BeginCopyFrom(ParseState *pstate, /* Read and verify binary header */ ReceiveCopyBinaryHeader(cstate); } - - /* create workspace for CopyReadAttributes results */ - if (!cstate->opts.binary) + else if (cstate->routine) { + cstate->routine->CopyFromStart(cstate, tupDesc); + } + else + { + /* create workspace for CopyReadAttributes results */ AttrNumber attr_count = list_length(cstate->attnumlist); cstate->max_fields = attr_count; @@ -1789,6 +1802,9 @@ BeginCopyFrom(ParseState *pstate, void EndCopyFrom(CopyFromState cstate) { + if (cstate->routine) + cstate->routine->CopyFromEnd(cstate); + /* No COPY FROM related resources except memory. */ if (cstate->is_program) { diff --git a/src/backend/commands/copyfromparse.c b/src/backend/commands/copyfromparse.c index 7cacd0b752..8b15080585 100644 --- a/src/backend/commands/copyfromparse.c +++ b/src/backend/commands/copyfromparse.c @@ -978,6 +978,11 @@ NextCopyFrom(CopyFromState cstate, ExprContext *econtext, Assert(fieldno == attr_count); } + else if (cstate->routine) + { + if (!cstate->routine->CopyFromOneRow(cstate, econtext, values, nulls)) + return false; + } else { /* binary */ diff --git a/src/backend/commands/copyto.c b/src/backend/commands/copyto.c index 20ffc90363..6080627c83 100644 --- a/src/backend/commands/copyto.c +++ b/src/backend/commands/copyto.c @@ -24,6 +24,7 @@ #include "access/xact.h" #include "access/xlog.h" #include "commands/copy.h" +#include "commands/copyapi.h" #include "commands/progress.h" #include "executor/execdesc.h" #include "executor/executor.h" @@ -71,6 +72,9 @@ typedef enum CopyDest */ typedef struct CopyToStateData { + /* format routine */ + const CopyToRoutine *routine; + /* low-level state data */ CopyDest copy_dest; /* type of copy source/destination */ FILE *copy_file; /* used if copy_dest == COPY_FILE */ @@ -777,15 +781,23 @@ DoCopyTo(CopyToState cstate) Form_pg_attribute attr = TupleDescAttr(tupDesc, attnum - 1); if (cstate->opts.binary) + { getTypeBinaryOutputInfo(attr->atttypid, &out_func_oid, &isvarlena); + fmgr_info(out_func_oid, &cstate->out_functions[attnum - 1]); + } + else if (cstate->routine) + cstate->routine->CopyToOutFunc(cstate, attr->atttypid, + &cstate->out_functions[attnum - 1]); else + { getTypeOutputInfo(attr->atttypid, &out_func_oid, &isvarlena); fmgr_info(out_func_oid, &cstate->out_functions[attnum - 1]); } + } /* * Create a temporary memory context that we can reset once per row to @@ -811,6 +823,8 @@ DoCopyTo(CopyToState cstate) tmp = 0; CopySendInt32(cstate, tmp); } + else if (cstate->routine) + cstate->routine->CopyToStart(cstate, tupDesc); else { /* @@ -892,6 +906,8 @@ DoCopyTo(CopyToState cstate) /* Need to flush out the trailer */ CopySendEndOfRow(cstate); } + else if (cstate->routine) + cstate->routine->CopyToEnd(cstate); MemoryContextDelete(cstate->rowcontext); @@ -916,6 +932,10 @@ CopyOneRowTo(CopyToState cstate, TupleTableSlot *slot) MemoryContextReset(cstate->rowcontext); oldcontext = MemoryContextSwitchTo(cstate->rowcontext); + if (cstate->routine) + cstate->routine->CopyToOneRow(cstate, slot); + else + { if (cstate->opts.binary) { /* Binary per-tuple header */ @@ -971,6 +991,7 @@ CopyOneRowTo(CopyToState cstate, TupleTableSlot *slot) } CopySendEndOfRow(cstate); + } MemoryContextSwitchTo(oldcontext); } diff --git a/src/include/commands/copyapi.h b/src/include/commands/copyapi.h new file mode 100644 index 0000000000..635c4cbff2 --- /dev/null +++ b/src/include/commands/copyapi.h @@ -0,0 +1,100 @@ +/*------------------------------------------------------------------------- + * + * copyapi.h + * API for COPY TO/FROM handlers + * + * + * Portions Copyright (c) 1996-2024, PostgreSQL Global Development Group + * Portions Copyright (c) 1994, Regents of the University of California + * + * src/include/commands/copyapi.h + * + *------------------------------------------------------------------------- + */ +#ifndef COPYAPI_H +#define COPYAPI_H + +#include "executor/tuptable.h" +#include "nodes/execnodes.h" + +/* These are private in commands/copy[from|to].c */ +typedef struct CopyFromStateData *CopyFromState; +typedef struct CopyToStateData *CopyToState; + +/* + * API structure for a COPY FROM format implementation. Note this must be + * allocated in a server-lifetime manner, typically as a static const struct. + */ +typedef struct CopyFromRoutine +{ + /* + * Called when COPY FROM is started to set up the input functions + * associated to the relation's attributes writing to. `finfo` can be + * optionally filled to provide the catalog information of the input + * function. `typioparam` can be optionally filled to define the OID of + * the type to pass to the input function. `atttypid` is the OID of data + * type used by the relation's attribute. + */ + void (*CopyFromInFunc) (CopyFromState cstate, Oid atttypid, + FmgrInfo *finfo, Oid *typioparam); + + /* + * Called when COPY FROM is started. + * + * `tupDesc` is the tuple descriptor of the relation where the data needs + * to be copied. This can be used for any initialization steps required + * by a format. + */ + void (*CopyFromStart) (CopyFromState cstate, TupleDesc tupDesc); + + /* + * Copy one row to a set of `values` and `nulls` of size tupDesc->natts. + * + * 'econtext' is used to evaluate default expression for each column that + * is either not read from the file or is using the DEFAULT option of COPY + * FROM. It is NULL if no default values are used. + * + * Returns false if there are no more tuples to copy. + */ + bool (*CopyFromOneRow) (CopyFromState cstate, ExprContext *econtext, + Datum *values, bool *nulls); + + /* Called when COPY FROM has ended. */ + void (*CopyFromEnd) (CopyFromState cstate); +} CopyFromRoutine; + +/* + * API structure for a COPY TO format implementation. Note this must be + * allocated in a server-lifetime manner, typically as a static const struct. + */ +typedef struct CopyToRoutine +{ + /* + * Called when COPY TO is started to set up the output functions + * associated to the relation's attributes reading from. `finfo` can be + * optionally filled. `atttypid` is the OID of data type used by the + * relation's attribute. + */ + void (*CopyToOutFunc) (CopyToState cstate, Oid atttypid, + FmgrInfo *finfo); + + /* + * Called when COPY TO is started. + * + * `tupDesc` is the tuple descriptor of the relation from where the data + * is read. + */ + void (*CopyToStart) (CopyToState cstate, TupleDesc tupDesc); + + /* + * Copy one row for COPY TO. + * + * `slot` is the tuple slot where the data is emitted. + */ + void (*CopyToOneRow) (CopyToState cstate, TupleTableSlot *slot); + + /* Called when COPY TO has ended */ + void (*CopyToEnd) (CopyToState cstate); +} CopyToRoutine; + +#endif /* COPYAPI_H */ diff --git a/src/include/commands/copyfrom_internal.h b/src/include/commands/copyfrom_internal.h index cad52fcc78..509b9e92a1 100644 --- a/src/include/commands/copyfrom_internal.h +++ b/src/include/commands/copyfrom_internal.h @@ -15,6 +15,7 @@ #define COPYFROM_INTERNAL_H #include "commands/copy.h" +#include "commands/copyapi.h" #include "commands/trigger.h" #include "nodes/miscnodes.h" @@ -58,6 +59,9 @@ typedef enum CopyInsertMethod */ typedef struct CopyFromStateData { + /* format routine */ + const CopyFromRoutine *routine; + /* low-level state data */ CopySource copy_src; /* type of copy source */ FILE *copy_file; /* used if copy_src == COPY_FILE */ diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list index ee40a341d3..a5ae161ca5 100644 --- a/src/tools/pgindent/typedefs.list +++ b/src/tools/pgindent/typedefs.list @@ -475,6 +475,7 @@ ConvertRowtypeExpr CookedConstraint CopyDest CopyFormatOptions +CopyFromRoutine CopyFromState CopyFromStateData CopyHeaderChoice @@ -484,6 +485,7 @@ CopyMultiInsertInfo CopyOnErrorChoice CopySource CopyStmt +CopyToRoutine CopyToState CopyToStateData Cost -- 2.43.0 ----Next_Part(Mon_Mar__4_14_11_08_2024_631)---- ^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Proposals for EXPLAIN: rename ANALYZE to EXECUTE and extend VERBOSE @ 2024-11-05 18:19 Robert Haas <[email protected]> 0 siblings, 2 replies; 20+ messages in thread From: Robert Haas @ 2024-11-05 18:19 UTC (permalink / raw) To: Nikolay Samokhvalov <[email protected]>; +Cc: pgsql-hackers On Tue, Nov 5, 2024 at 1:02 PM Nikolay Samokhvalov <[email protected]> wrote: > hi, I have a proposal, resulted from numerous communications with various folks, both very experienced and new Postgres users: > > 1) EXPLAIN ANALYZE Is sometimes very confusing (because there is ANALYZE). Let's rename it to EXPLAIN EXECUTE? The trouble is that EXPLAIN EXECUTE already means something. robert.haas=# explain execute foo; ERROR: prepared statement "foo" does not exist Granted, that would not make it impossible to make EXPLAIN (EXECUTE) a synonym for EXPLAIN (ANALYZE), but IMHO it would be pretty confusing if EXPLAIN EXECUTE and EXPLAIN (EXECUTE) did different things. > 2) VERBOSE doesn't include BUFFERS, and doesn't include SETTINGS; it might be also confusing sometimes. Let's include them so VERBOSE would be really verbose? I agree that the naming here isn't great, but I think making the options non-orthogonal would probably be worse. > 3) small thing about grammar: allow omitting parentheses, so EXPLAIN EXECUTE VERBOSE would work. Perhaps surprisingly, it turns out that this is not a small change. As Tom mentions, this would have a pretty large blast radius. In fact, the reason I wrote the patch to introduce parenthesized options for EXPLAIN was precisely because the unparenthesized option syntax does not scale nicely at all. -- Robert Haas EDB: http://www.enterprisedb.com ^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Proposals for EXPLAIN: rename ANALYZE to EXECUTE and extend VERBOSE @ 2024-11-05 18:24 Nikolay Samokhvalov <[email protected]> parent: Robert Haas <[email protected]> 1 sibling, 2 replies; 20+ messages in thread From: Nikolay Samokhvalov @ 2024-11-05 18:24 UTC (permalink / raw) To: Robert Haas <[email protected]>; +Cc: pgsql-hackers On Tue, Nov 5, 2024 at 10:19 AM Robert Haas <[email protected]> wrote: > On Tue, Nov 5, 2024 at 1:02 PM Nikolay Samokhvalov > <[email protected]> wrote: > > hi, I have a proposal, resulted from numerous communications with > various folks, both very experienced and new Postgres users: > > > > 1) EXPLAIN ANALYZE Is sometimes very confusing (because there is > ANALYZE). Let's rename it to EXPLAIN EXECUTE? > > The trouble is that EXPLAIN EXECUTE already means something. > > robert.haas=# explain execute foo; > ERROR: prepared statement "foo" does not exist > > Granted, that would not make it impossible to make EXPLAIN (EXECUTE) a > synonym for EXPLAIN (ANALYZE), but IMHO it would be pretty confusing > if EXPLAIN EXECUTE and EXPLAIN (EXECUTE) did different things. > > > 2) VERBOSE doesn't include BUFFERS, and doesn't include SETTINGS; it > might be also confusing sometimes. Let's include them so VERBOSE would be > really verbose? > > I agree that the naming here isn't great, but I think making the > options non-orthogonal would probably be worse. > > > 3) small thing about grammar: allow omitting parentheses, so EXPLAIN > EXECUTE VERBOSE would work. > > Perhaps surprisingly, it turns out that this is not a small change. As > Tom mentions, this would have a pretty large blast radius. In fact, > the reason I wrote the patch to introduce parenthesized options for > EXPLAIN was precisely because the unparenthesized option syntax does > not scale nicely at all. > I appreciate all yours and Tom's very quick comments here! Item 3 is already solved, as it turned out. Let's focus on item 2. Is it really impossible to make VERBOSE really verbose? ^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Proposals for EXPLAIN: rename ANALYZE to EXECUTE and extend VERBOSE @ 2024-11-05 18:30 Tom Lane <[email protected]> parent: Nikolay Samokhvalov <[email protected]> 1 sibling, 1 reply; 20+ messages in thread From: Tom Lane @ 2024-11-05 18:30 UTC (permalink / raw) To: Nikolay Samokhvalov <[email protected]>; +Cc: Robert Haas <[email protected]>; pgsql-hackers Nikolay Samokhvalov <[email protected]> writes: > Let's focus on item 2. Is it really impossible to make VERBOSE really > verbose? It's obviously not "impossible" -- the code changes would likely be trivial. The question is whether it's a good idea. These semantics were (I presume) deliberately chosen when the options were added, so somebody thought not. You would need to go back and review the relevant mail thread and then make arguments why that decision was wrong. In short: we're not working in a green field here, and all these decisions have history. You will not get far by just popping up and saying "I think it should be different". You need to make a case why the decision was wrong, and why it was so wrong that we should risk cross-version-compatibility problems by changing. regards, tom lane ^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Proposals for EXPLAIN: rename ANALYZE to EXECUTE and extend VERBOSE @ 2024-11-05 18:45 Robert Haas <[email protected]> parent: Nikolay Samokhvalov <[email protected]> 1 sibling, 0 replies; 20+ messages in thread From: Robert Haas @ 2024-11-05 18:45 UTC (permalink / raw) To: Nikolay Samokhvalov <[email protected]>; +Cc: pgsql-hackers On Tue, Nov 5, 2024 at 1:24 PM Nikolay Samokhvalov <[email protected]> wrote: > Item 3 is already solved, as it turned out. ANALYZE and VERBOSE are treated specially because those options existed prior to the parenthesized syntax. Scaling that treatment to a large number of options will not work out. > Let's focus on item 2. Is it really impossible to make VERBOSE really verbose? It is, of course, not impossible. But the fact that something is possible does not necessarily mean that it is a good idea. I think it can be quite confusing when the same behavior is controlled in more than one way. If the VERBOSE option turns information about BUFFERS on and off, and the BUFFERS option does the same thing, what happens if I say EXPLAIN (VERBOSE ON, BUFFERS OFF)? Is it different if I say EXPLAIN (BUFFERS OFF, VERBOSE ON)? There's a lot of opportunity for the behavior to be confusing here. Then, too, we can argue about what should be included in VERBOSE. You propose BUFFERS and SETTINGS, but we've also got SERIALIZE (which is not even Boolean-valued), WAL, and MEMORY. One can argue that we ought to include everything when VERBOSE is specified; one can also argue that some of this stuff is too marginal and too high-overhead to justify its inclusion. Both arguments have merit, IMHO. I'm not very happy with the current situation. I agree that EXPLAIN has gotten a bit too complicated. However, I also know that not everyone wants the same things. And I can say from a PostgreSQL support perspective that I do not always want a customer to just "turn on everything", as EXPLAIN output can be extremely long and adding a whole bunch of additional details that make already-long output even longer can easily be actively unhelpful. For me personally, just plain EXPLAIN ANALYZE is usually enough. Sometimes I need VERBOSE to see the target lists at each level, and very occasionally I need BUFFERS to see how much data is being accessed, but at least for me, those are pretty rare cases. So I don't think I really believe the "everybody always wants that" argument. One of the most common things that I have to do with EXPLAIN output is trim the small amounts of relevant material out of the giant pile of things that don't matter to the problem at hand. If you enable an option that adds an extra line of output for every node and there are 100 nodes in the query plan, that is a whole lot of additional clutter. -- Robert Haas EDB: http://www.enterprisedb.com ^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Proposals for EXPLAIN: rename ANALYZE to EXECUTE and extend VERBOSE @ 2024-11-05 22:54 Nikolay Samokhvalov <[email protected]> parent: Tom Lane <[email protected]> 0 siblings, 2 replies; 20+ messages in thread From: Nikolay Samokhvalov @ 2024-11-05 22:54 UTC (permalink / raw) To: Tom Lane <[email protected]>; +Cc: Robert Haas <[email protected]>; pgsql-hackers On Tue, Nov 5, 2024 at 10:30 AM Tom Lane <[email protected]> wrote: > we're not working in a green field here, and all these > decisions have history. > I hear you and understand. Ready to do legwork here. 1. VERBOSE first appeared in 1997 in 6.3 in 3a02ccfa, with different meaning: > This command [EXPLAIN] outputs details about the supplied query. The default > output is the computed query cost. \f2verbose\f1 displays the full query > plan and cost. 2. Support for parenthesis was added in d4382c4a (2009, 8.5), with "test" option COSTS, and this opened gates to extending with many options. 3. BUFFERS was added in d4382c4 (also 2009, 8.5), discussion https://www.postgresql.org/message-id/flat/4AC12A17.5040305%40timbira.com, I didn't see that inclusion it to VERBOSE was discussed. In my opinion, this option is invaluable: most of the performance optimization is done by reducing IO so seeing these numbers helps make decisions much faster. I always use them. When you optimize and, for example, want to verify an index idea, it's not good to do it on production – it's better to work with clones. There, we can have weaker hardware, different buffer state, etc. So timing numbers might be really off. Timing can be different even on the same server, e.g. after restart, when buffer pool is not warmed up. But BUFFERS never lie – they are not affected by saturated CPU if it happens, lock acquisition waits, etc. Not looking at them is missing an essential part of analysis, I strongly believe. It looks like in 2009, when the BUFFERS option was created, it was not enough understanding that it is so useful, so it was not discussed to include them by default or at least – as we discuss here – to involve in VERBOSE. I want to emphasize: BUFFERS is essential in my work and more and more people are convinced that during the optimization process, when you're inside it, in most cases it's beneficial to focus on BUFFERS. Notice that explain.depesz.com, explain.dalibo.com, pgMustard and many tools recognize it and ask users to include BUFFERS to analysis. And see the next item: 4. Making BUFFERS default behavior for EXPLAIN ANALYZE was raised several times, for example https://www.postgresql.org/message-id/flat/CANNMO++=LrJ4upoeydZhbmpd_ZgZjrTLueKSrivn6xmb=yFwQw@mail.... (2021) – and my understanding that it was received great support and it discussed in detail why it's useful, but then several attempts to implement it were not accomplished because of tech difficulties (as I remember, problem with broken tests and how to fix that). 5. EXPLAIN ALL proposed in https://www.postgresql.org/message-id/flat/[email protected] (2016) – I think it's actually a good idea originally, but didn't survive questions of mutually exclusive options and non-binary options, and then discussion stopped after pivoting in direction of GUC. 6. FInally, the fresh SERIALIZE option was discussed in https://www.postgresql.org/message-id/flat/ca0adb0e-fa4e-c37e-1cd7-91170b18cae1%40gmx.de (2023-2024, 17), and unfortunately again. I might be missing some discussions – please help me find them; I also expect that there are many people who support me thinking that BUFFERS are very useful and should be default or at least inside VERBOSE. Meanwhile: - to be able to have all data in hand during analysis, we need to recommend users to collect plans using EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS), which looks really long - independently, I know see pgMustard ended up having a similar recommendation: https://www.pgmustard.com/getting-a-query-plan: > For better advice, we recommend using at least: explain (analyze, format json, buffers, verbose, settings) My proposal remains: EXPLAIN ANALYZE VERBOSE -- let's consider this, please. ^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Proposals for EXPLAIN: rename ANALYZE to EXECUTE and extend VERBOSE @ 2024-11-05 23:09 Nikolay Samokhvalov <[email protected]> parent: Nikolay Samokhvalov <[email protected]> 1 sibling, 0 replies; 20+ messages in thread From: Nikolay Samokhvalov @ 2024-11-05 23:09 UTC (permalink / raw) To: Tom Lane <[email protected]>; +Cc: Robert Haas <[email protected]>; pgsql-hackers On Tue, Nov 5, 2024 at 2:54 PM Nikolay Samokhvalov <[email protected]> wrote: > 6. FInally, the fresh SERIALIZE option was discussed in > https://www.postgresql.org/message-id/flat/ca0adb0e-fa4e-c37e-1cd7-91170b18cae1%40gmx.de > (2023-2024, 17), and unfortunately again. > (didn't finish the phrase here and hit Send) ...again, I don't see that it was discussed to include the SERIALIZE behavior to VERBOSE. I don't use SERIALIZE myself, but during our podcasts, Michael (CCing him) was wondering why it was so. Summary: I haven't found explicit discussions of including new options to VERBOSE, when that new options were created. I used Google, the .org search, and postgres.ai semantic search over archives involving pgvector/HNSW – I might be missing something, or it was really not discussed when new options were added. ^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Proposals for EXPLAIN: rename ANALYZE to EXECUTE and extend VERBOSE @ 2024-11-05 23:32 David G. Johnston <[email protected]> parent: Nikolay Samokhvalov <[email protected]> 1 sibling, 1 reply; 20+ messages in thread From: David G. Johnston @ 2024-11-05 23:32 UTC (permalink / raw) To: Nikolay Samokhvalov <[email protected]>; +Cc: Tom Lane <[email protected]>; Robert Haas <[email protected]>; pgsql-hackers On Tue, Nov 5, 2024 at 3:55 PM Nikolay Samokhvalov <[email protected]> wrote: > > 4. Making BUFFERS default behavior for EXPLAIN ANALYZE was raised several > times, for example > https://www.postgresql.org/message-id/flat/CANNMO++=LrJ4upoeydZhbmpd_ZgZjrTLueKSrivn6xmb=yFwQw@mail.... > (2021) – and my understanding that it was received great support and it > discussed in detail why it's useful, but then several attempts to implement > it were not accomplished because of tech difficulties (as I remember, > problem with broken tests and how to fix that). > The main premise here is that explain should include buffers by default, and to do so we are willing to inconvenience testers who do not want buffer data in their test plans to have to modify their tests to explicitly exclude buffers. We'll have to eat our own dog food here and go and add "buffers off" throughout our code base to make this happen. I personally feel that we should accept a patch that does so. The benefits to the many outweigh the one-time inconveniencing of the few. Especially if limited to explain analyze. > 5. EXPLAIN ALL proposed in > https://www.postgresql.org/message-id/flat/[email protected] > (2016) – I think it's actually a good idea originally, but didn't survive > questions of mutually exclusive options and non-binary options, and then > discussion stopped after pivoting in direction of GUC. > If the desire is to make the current keyword VERBOSE behave like the proposed ALL keyword then one must first get a version of ALL accepted, then argue for repurposing VERBOSE instead of adding the new keyword. But at this point I really do not see extending verbose to mean more than "add more comments and context labels". Verbose has never meant to include everything and getting buy-in to change that seems highly unlikely. In short, neither change is deemed unwanted, and indeed has desire. It's a matter of learning from the previous attempt to increase the odds of getting something committed. I wouldn't advise expending effort or political capital on the parentheses topic at this point. David J. ^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Proposals for EXPLAIN: rename ANALYZE to EXECUTE and extend VERBOSE @ 2024-11-06 00:23 David Rowley <[email protected]> parent: David G. Johnston <[email protected]> 0 siblings, 1 reply; 20+ messages in thread From: David Rowley @ 2024-11-06 00:23 UTC (permalink / raw) To: David G. Johnston <[email protected]>; +Cc: Nikolay Samokhvalov <[email protected]>; Tom Lane <[email protected]>; Robert Haas <[email protected]>; pgsql-hackers On Wed, 6 Nov 2024 at 12:33, David G. Johnston <[email protected]> wrote: > The main premise here is that explain should include buffers by default, and to do so we are willing to inconvenience testers who do not want buffer data in their test plans to have to modify their tests to explicitly exclude buffers. We'll have to eat our own dog food here and go and add "buffers off" throughout our code base to make this happen. I personally feel that we should accept a patch that does so. The benefits to the many outweigh the one-time inconveniencing of the few. Especially if limited to explain analyze. I'm not against analyze = on turning buffers on by default. However, I think it would be quite painful to fix the tests if it were on without analyze. I tried it to see just how extensive the changes would need to be. It's not too bad. partition_prune.sql is the worst hit. 23 files changed, 171 insertions(+), 166 deletions(-) David diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out index f2bcd6aa98..bf322198a2 100644 --- a/contrib/postgres_fdw/expected/postgres_fdw.out +++ b/contrib/postgres_fdw/expected/postgres_fdw.out @@ -11553,7 +11553,7 @@ SELECT * FROM local_tbl, async_pt WHERE local_tbl.a = async_pt.a AND local_tbl.c Filter: (async_pt_3.a = local_tbl.a) (15 rows) -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) SELECT * FROM local_tbl, async_pt WHERE local_tbl.a = async_pt.a AND local_tbl.c = 'bar'; QUERY PLAN ------------------------------------------------------------------------------- @@ -11799,7 +11799,7 @@ SELECT * FROM local_tbl t1 LEFT JOIN (SELECT *, (SELECT count(*) FROM async_pt W Remote SQL: SELECT a, b, c FROM public.base_tbl2 WHERE ((a < 3000)) (20 rows) -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) SELECT * FROM local_tbl t1 LEFT JOIN (SELECT *, (SELECT count(*) FROM async_pt WHERE a < 3000) FROM async_pt WHERE a < 3000) t2 ON t1.a = t2.a; QUERY PLAN ----------------------------------------------------------------------------------------- @@ -11843,7 +11843,7 @@ SELECT * FROM async_pt t1 WHERE t1.b === 505 LIMIT 1; Filter: (t1_3.b === 505) (14 rows) -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) SELECT * FROM async_pt t1 WHERE t1.b === 505 LIMIT 1; QUERY PLAN ------------------------------------------------------------------------- @@ -12003,7 +12003,7 @@ DELETE FROM async_pt WHERE b = 0 RETURNING *; DELETE FROM async_p1; DELETE FROM async_p2; DELETE FROM async_p3; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) SELECT * FROM async_pt; QUERY PLAN ------------------------------------------------------------------------- diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql index 372fe6dad1..3900522ccb 100644 --- a/contrib/postgres_fdw/sql/postgres_fdw.sql +++ b/contrib/postgres_fdw/sql/postgres_fdw.sql @@ -3904,7 +3904,7 @@ ALTER FOREIGN TABLE async_p2 OPTIONS (use_remote_estimate 'true'); EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM local_tbl, async_pt WHERE local_tbl.a = async_pt.a AND local_tbl.c = 'bar'; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) SELECT * FROM local_tbl, async_pt WHERE local_tbl.a = async_pt.a AND local_tbl.c = 'bar'; SELECT * FROM local_tbl, async_pt WHERE local_tbl.a = async_pt.a AND local_tbl.c = 'bar'; @@ -3979,13 +3979,13 @@ ANALYZE local_tbl; EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM local_tbl t1 LEFT JOIN (SELECT *, (SELECT count(*) FROM async_pt WHERE a < 3000) FROM async_pt WHERE a < 3000) t2 ON t1.a = t2.a; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) SELECT * FROM local_tbl t1 LEFT JOIN (SELECT *, (SELECT count(*) FROM async_pt WHERE a < 3000) FROM async_pt WHERE a < 3000) t2 ON t1.a = t2.a; SELECT * FROM local_tbl t1 LEFT JOIN (SELECT *, (SELECT count(*) FROM async_pt WHERE a < 3000) FROM async_pt WHERE a < 3000) t2 ON t1.a = t2.a; EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM async_pt t1 WHERE t1.b === 505 LIMIT 1; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) SELECT * FROM async_pt t1 WHERE t1.b === 505 LIMIT 1; SELECT * FROM async_pt t1 WHERE t1.b === 505 LIMIT 1; @@ -4037,7 +4037,7 @@ DELETE FROM async_p1; DELETE FROM async_p2; DELETE FROM async_p3; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) SELECT * FROM async_pt; -- Clean up diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c index 7c0fd63b2f..87ad83e984 100644 --- a/src/backend/commands/explain.c +++ b/src/backend/commands/explain.c @@ -199,6 +199,7 @@ ExplainQuery(ParseState *pstate, ExplainStmt *stmt, ListCell *lc; bool timing_set = false; bool summary_set = false; + bool buffers_set = false; /* Parse options list. */ foreach(lc, stmt->options) @@ -212,7 +213,10 @@ ExplainQuery(ParseState *pstate, ExplainStmt *stmt, else if (strcmp(opt->defname, "costs") == 0) es->costs = defGetBoolean(opt); else if (strcmp(opt->defname, "buffers") == 0) + { es->buffers = defGetBoolean(opt); + buffers_set = true; + } else if (strcmp(opt->defname, "wal") == 0) es->wal = defGetBoolean(opt); else if (strcmp(opt->defname, "settings") == 0) @@ -291,6 +295,7 @@ ExplainQuery(ParseState *pstate, ExplainStmt *stmt, /* if the timing was not set explicitly, set default value */ es->timing = (timing_set) ? es->timing : es->analyze; + es->buffers = (buffers_set) ? es->buffers : es->analyze; /* check that timing is used with EXPLAIN ANALYZE */ if (es->timing && !es->analyze) diff --git a/src/test/regress/expected/brin_multi.out b/src/test/regress/expected/brin_multi.out index ae9ce9d8ec..f2d1465818 100644 --- a/src/test/regress/expected/brin_multi.out +++ b/src/test/regress/expected/brin_multi.out @@ -845,7 +845,7 @@ INSERT INTO brin_date_test SELECT '5874897-12-01'::date + i FROM generate_series CREATE INDEX ON brin_date_test USING brin (a date_minmax_multi_ops) WITH (pages_per_range=1); SET enable_seqscan = off; -- make sure the ranges were built correctly and 2023-01-01 eliminates all -EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF) +EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF, BUFFERS OFF) SELECT * FROM brin_date_test WHERE a = '2023-01-01'::date; QUERY PLAN ------------------------------------------------------------------------- @@ -864,7 +864,7 @@ INSERT INTO brin_timestamp_test SELECT i FROM generate_series('2000-01-01'::timestamp, '2000-02-09'::timestamp, '1 day'::interval) s(i); CREATE INDEX ON brin_timestamp_test USING brin (a timestamp_minmax_multi_ops) WITH (pages_per_range=1); SET enable_seqscan = off; -EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF) +EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF, BUFFERS OFF) SELECT * FROM brin_timestamp_test WHERE a = '2023-01-01'::timestamp; QUERY PLAN ------------------------------------------------------------------------------ @@ -874,7 +874,7 @@ SELECT * FROM brin_timestamp_test WHERE a = '2023-01-01'::timestamp; Index Cond: (a = '2023-01-01 00:00:00'::timestamp without time zone) (4 rows) -EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF) +EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF, BUFFERS OFF) SELECT * FROM brin_timestamp_test WHERE a = '1900-01-01'::timestamp; QUERY PLAN ------------------------------------------------------------------------------ @@ -892,7 +892,7 @@ INSERT INTO brin_date_test VALUES ('-infinity'), ('infinity'); INSERT INTO brin_date_test SELECT '2000-01-01'::date + i FROM generate_series(1, 40) s(i); CREATE INDEX ON brin_date_test USING brin (a date_minmax_multi_ops) WITH (pages_per_range=1); SET enable_seqscan = off; -EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF) +EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF, BUFFERS OFF) SELECT * FROM brin_date_test WHERE a = '2023-01-01'::date; QUERY PLAN ------------------------------------------------------------------------- @@ -902,7 +902,7 @@ SELECT * FROM brin_date_test WHERE a = '2023-01-01'::date; Index Cond: (a = '2023-01-01'::date) (4 rows) -EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF) +EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF, BUFFERS OFF) SELECT * FROM brin_date_test WHERE a = '1900-01-01'::date; QUERY PLAN ------------------------------------------------------------------------- @@ -921,7 +921,7 @@ INSERT INTO brin_interval_test SELECT (i || ' years')::interval FROM generate_se INSERT INTO brin_interval_test SELECT (i || ' years')::interval FROM generate_series( 177999980, 178000000) s(i); CREATE INDEX ON brin_interval_test USING brin (a interval_minmax_multi_ops) WITH (pages_per_range=1); SET enable_seqscan = off; -EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF) +EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF, BUFFERS OFF) SELECT * FROM brin_interval_test WHERE a = '-30 years'::interval; QUERY PLAN ----------------------------------------------------------------------------- @@ -931,7 +931,7 @@ SELECT * FROM brin_interval_test WHERE a = '-30 years'::interval; Index Cond: (a = '@ 30 years ago'::interval) (4 rows) -EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF) +EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF, BUFFERS OFF) SELECT * FROM brin_interval_test WHERE a = '30 years'::interval; QUERY PLAN ----------------------------------------------------------------------------- @@ -949,7 +949,7 @@ INSERT INTO brin_interval_test VALUES ('-infinity'), ('infinity'); INSERT INTO brin_interval_test SELECT (i || ' days')::interval FROM generate_series(100, 140) s(i); CREATE INDEX ON brin_interval_test USING brin (a interval_minmax_multi_ops) WITH (pages_per_range=1); SET enable_seqscan = off; -EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF) +EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF, BUFFERS OFF) SELECT * FROM brin_interval_test WHERE a = '-30 years'::interval; QUERY PLAN ----------------------------------------------------------------------------- @@ -959,7 +959,7 @@ SELECT * FROM brin_interval_test WHERE a = '-30 years'::interval; Index Cond: (a = '@ 30 years ago'::interval) (4 rows) -EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF) +EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF, BUFFERS OFF) SELECT * FROM brin_interval_test WHERE a = '30 years'::interval; QUERY PLAN ----------------------------------------------------------------------------- diff --git a/src/test/regress/expected/explain.out b/src/test/regress/expected/explain.out index d2eef8097c..f97fe7542c 100644 --- a/src/test/regress/expected/explain.out +++ b/src/test/regress/expected/explain.out @@ -60,7 +60,7 @@ select explain_filter('explain select * from int8_tbl i8'); Seq Scan on int8_tbl i8 (cost=N.N..N.N rows=N width=N) (1 row) -select explain_filter('explain (analyze) select * from int8_tbl i8'); +select explain_filter('explain (analyze, buffers off) select * from int8_tbl i8'); explain_filter ----------------------------------------------------------------------------------------------- Seq Scan on int8_tbl i8 (cost=N.N..N.N rows=N width=N) (actual time=N.N..N.N rows=N loops=N) @@ -68,7 +68,7 @@ select explain_filter('explain (analyze) select * from int8_tbl i8'); Execution Time: N.N ms (3 rows) -select explain_filter('explain (analyze, verbose) select * from int8_tbl i8'); +select explain_filter('explain (analyze, buffers off, verbose) select * from int8_tbl i8'); explain_filter ------------------------------------------------------------------------------------------------------ Seq Scan on public.int8_tbl i8 (cost=N.N..N.N rows=N width=N) (actual time=N.N..N.N rows=N loops=N) @@ -341,7 +341,7 @@ select explain_filter('explain (generic_plan) select unique1 from tenk1 where th (4 rows) -- should fail -select explain_filter('explain (analyze, generic_plan) select unique1 from tenk1 where thousand = $1'); +select explain_filter('explain (analyze, buffers off, generic_plan) select unique1 from tenk1 where thousand = $1'); ERROR: EXPLAIN options ANALYZE and GENERIC_PLAN cannot be used together CONTEXT: PL/pgSQL function explain_filter(text) line 5 at FOR over EXECUTE statement -- MEMORY option @@ -352,7 +352,7 @@ select explain_filter('explain (memory) select * from int8_tbl i8'); Memory: used=NkB allocated=NkB (2 rows) -select explain_filter('explain (memory, analyze) select * from int8_tbl i8'); +select explain_filter('explain (memory, analyze, buffers off) select * from int8_tbl i8'); explain_filter ----------------------------------------------------------------------------------------------- Seq Scan on int8_tbl i8 (cost=N.N..N.N rows=N width=N) (actual time=N.N..N.N rows=N loops=N) @@ -381,7 +381,7 @@ select explain_filter('explain (memory, summary, format yaml) select * from int8 Planning Time: N.N (1 row) -select explain_filter('explain (memory, analyze, format json) select * from int8_tbl i8'); +select explain_filter('explain (memory, analyze, buffers off, format json) select * from int8_tbl i8'); explain_filter ------------------------------------ [ + @@ -680,7 +680,7 @@ select explain_filter('explain (verbose) create table test_ctas as select 1'); (3 rows) -- Test SERIALIZE option -select explain_filter('explain (analyze,serialize) select * from int8_tbl i8'); +select explain_filter('explain (analyze,buffers off,serialize) select * from int8_tbl i8'); explain_filter ----------------------------------------------------------------------------------------------- Seq Scan on int8_tbl i8 (cost=N.N..N.N rows=N width=N) (actual time=N.N..N.N rows=N loops=N) @@ -708,7 +708,7 @@ select explain_filter('explain (analyze,serialize binary,buffers,timing) select (4 rows) -- this tests an edge case where we have no data to return -select explain_filter('explain (analyze,serialize) create temp table explain_temp as select * from int8_tbl i8'); +select explain_filter('explain (analyze,buffers off,serialize) create temp table explain_temp as select * from int8_tbl i8'); explain_filter ----------------------------------------------------------------------------------------------- Seq Scan on int8_tbl i8 (cost=N.N..N.N rows=N width=N) (actual time=N.N..N.N rows=N loops=N) @@ -718,7 +718,7 @@ select explain_filter('explain (analyze,serialize) create temp table explain_tem (4 rows) -- Test tuplestore storage usage in Window aggregate (memory case) -select explain_filter('explain (analyze,costs off) select sum(n) over() from generate_series(1,10) a(n)'); +select explain_filter('explain (analyze,buffers off,costs off) select sum(n) over() from generate_series(1,10) a(n)'); explain_filter -------------------------------------------------------------------------------- WindowAgg (actual time=N.N..N.N rows=N loops=N) @@ -730,7 +730,7 @@ select explain_filter('explain (analyze,costs off) select sum(n) over() from gen -- Test tuplestore storage usage in Window aggregate (disk case) set work_mem to 64; -select explain_filter('explain (analyze,costs off) select sum(n) over() from generate_series(1,2000) a(n)'); +select explain_filter('explain (analyze,buffers off,costs off) select sum(n) over() from generate_series(1,2000) a(n)'); explain_filter -------------------------------------------------------------------------------- WindowAgg (actual time=N.N..N.N rows=N loops=N) @@ -741,7 +741,7 @@ select explain_filter('explain (analyze,costs off) select sum(n) over() from gen (5 rows) -- Test tuplestore storage usage in Window aggregate (memory and disk case, final result is disk) -select explain_filter('explain (analyze,costs off) select sum(n) over(partition by m) from (SELECT n < 3 as m, n from generate_series(1,2000) a(n))'); +select explain_filter('explain (analyze,buffers off,costs off) select sum(n) over(partition by m) from (SELECT n < 3 as m, n from generate_series(1,2000) a(n))'); explain_filter -------------------------------------------------------------------------------------- WindowAgg (actual time=N.N..N.N rows=N loops=N) diff --git a/src/test/regress/expected/incremental_sort.out b/src/test/regress/expected/incremental_sort.out index 2df7a5db12..d597575840 100644 --- a/src/test/regress/expected/incremental_sort.out +++ b/src/test/regress/expected/incremental_sort.out @@ -39,7 +39,7 @@ declare line text; begin for line in - execute 'explain (analyze, costs off, summary off, timing off) ' || query + execute 'explain (analyze, costs off, summary off, timing off, buffers off) ' || query loop out_line := regexp_replace(line, '\d+kB', 'NNkB', 'g'); return next; @@ -55,7 +55,7 @@ declare element jsonb; matching_nodes jsonb := '[]'::jsonb; begin - execute 'explain (analyze, costs off, summary off, timing off, format ''json'') ' || query into strict elements; + execute 'explain (analyze, costs off, summary off, timing off, buffers off, format ''json'') ' || query into strict elements; while jsonb_array_length(elements) > 0 loop element := elements->0; elements := elements - 0; diff --git a/src/test/regress/expected/memoize.out b/src/test/regress/expected/memoize.out index f6b8329cd6..5ecf971dad 100644 --- a/src/test/regress/expected/memoize.out +++ b/src/test/regress/expected/memoize.out @@ -10,7 +10,7 @@ declare ln text; begin for ln in - execute format('explain (analyze, costs off, summary off, timing off) %s', + execute format('explain (analyze, costs off, summary off, timing off, buffers off) %s', query) loop if hide_hitmiss = true then diff --git a/src/test/regress/expected/merge.out b/src/test/regress/expected/merge.out index 521d70a891..28d8551063 100644 --- a/src/test/regress/expected/merge.out +++ b/src/test/regress/expected/merge.out @@ -1621,7 +1621,7 @@ $$ DECLARE ln text; BEGIN FOR ln IN - EXECUTE 'explain (analyze, timing off, summary off, costs off) ' || + EXECUTE 'explain (analyze, timing off, summary off, costs off, buffers off) ' || query LOOP ln := regexp_replace(ln, '(Memory( Usage)?|Buckets|Batches): \S*', '\1: xxx', 'g'); diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.out index 7a03b4e360..c52bc40e81 100644 --- a/src/test/regress/expected/partition_prune.out +++ b/src/test/regress/expected/partition_prune.out @@ -11,7 +11,7 @@ declare ln text; begin for ln in - execute format('explain (analyze, costs off, summary off, timing off) %s', + execute format('explain (analyze, costs off, summary off, timing off, buffers off) %s', query) loop ln := regexp_replace(ln, 'Maximum Storage: \d+', 'Maximum Storage: N'); @@ -2127,7 +2127,7 @@ create table ab_a3_b3 partition of ab_a3 for values in (3); set enable_indexonlyscan = off; prepare ab_q1 (int, int, int) as select * from ab where a between $1 and $2 and b <= $3; -explain (analyze, costs off, summary off, timing off) execute ab_q1 (2, 2, 3); +explain (analyze, costs off, summary off, timing off, buffers off) execute ab_q1 (2, 2, 3); QUERY PLAN --------------------------------------------------------- Append (actual rows=0 loops=1) @@ -2140,7 +2140,7 @@ explain (analyze, costs off, summary off, timing off) execute ab_q1 (2, 2, 3); Filter: ((a >= $1) AND (a <= $2) AND (b <= $3)) (8 rows) -explain (analyze, costs off, summary off, timing off) execute ab_q1 (1, 2, 3); +explain (analyze, costs off, summary off, timing off, buffers off) execute ab_q1 (1, 2, 3); QUERY PLAN --------------------------------------------------------- Append (actual rows=0 loops=1) @@ -2163,7 +2163,7 @@ deallocate ab_q1; -- Runtime pruning after optimizer pruning prepare ab_q1 (int, int) as select a from ab where a between $1 and $2 and b < 3; -explain (analyze, costs off, summary off, timing off) execute ab_q1 (2, 2); +explain (analyze, costs off, summary off, timing off, buffers off) execute ab_q1 (2, 2); QUERY PLAN --------------------------------------------------------- Append (actual rows=0 loops=1) @@ -2174,7 +2174,7 @@ explain (analyze, costs off, summary off, timing off) execute ab_q1 (2, 2); Filter: ((a >= $1) AND (a <= $2) AND (b < 3)) (6 rows) -explain (analyze, costs off, summary off, timing off) execute ab_q1 (2, 4); +explain (analyze, costs off, summary off, timing off, buffers off) execute ab_q1 (2, 4); QUERY PLAN --------------------------------------------------------- Append (actual rows=0 loops=1) @@ -2193,7 +2193,7 @@ explain (analyze, costs off, summary off, timing off) execute ab_q1 (2, 4); -- different levels of partitioning. prepare ab_q2 (int, int) as select a from ab where a between $1 and $2 and b < (select 3); -explain (analyze, costs off, summary off, timing off) execute ab_q2 (2, 2); +explain (analyze, costs off, summary off, timing off, buffers off) execute ab_q2 (2, 2); QUERY PLAN ----------------------------------------------------------------------- Append (actual rows=0 loops=1) @@ -2211,7 +2211,7 @@ explain (analyze, costs off, summary off, timing off) execute ab_q2 (2, 2); -- As above, but swap the PARAM_EXEC Param to the first partition level prepare ab_q3 (int, int) as select a from ab where b between $1 and $2 and a < (select 3); -explain (analyze, costs off, summary off, timing off) execute ab_q3 (2, 2); +explain (analyze, costs off, summary off, timing off, buffers off) execute ab_q3 (2, 2); QUERY PLAN ----------------------------------------------------------------------- Append (actual rows=0 loops=1) @@ -2273,7 +2273,7 @@ begin; -- Test run-time pruning using stable functions create function list_part_fn(int) returns int as $$ begin return $1; end;$$ language plpgsql stable; -- Ensure pruning works using a stable function containing no Vars -explain (analyze, costs off, summary off, timing off) select * from list_part where a = list_part_fn(1); +explain (analyze, costs off, summary off, timing off, buffers off) select * from list_part where a = list_part_fn(1); QUERY PLAN ------------------------------------------------------------------ Append (actual rows=1 loops=1) @@ -2283,7 +2283,7 @@ explain (analyze, costs off, summary off, timing off) select * from list_part wh (4 rows) -- Ensure pruning does not take place when the function has a Var parameter -explain (analyze, costs off, summary off, timing off) select * from list_part where a = list_part_fn(a); +explain (analyze, costs off, summary off, timing off, buffers off) select * from list_part where a = list_part_fn(a); QUERY PLAN ------------------------------------------------------------------ Append (actual rows=4 loops=1) @@ -2298,7 +2298,7 @@ explain (analyze, costs off, summary off, timing off) select * from list_part wh (9 rows) -- Ensure pruning does not take place when the expression contains a Var. -explain (analyze, costs off, summary off, timing off) select * from list_part where a = list_part_fn(1) + a; +explain (analyze, costs off, summary off, timing off, buffers off) select * from list_part where a = list_part_fn(1) + a; QUERY PLAN ------------------------------------------------------------------ Append (actual rows=0 loops=1) @@ -2334,7 +2334,7 @@ declare ln text; begin for ln in - execute format('explain (analyze, costs off, summary off, timing off) %s', + execute format('explain (analyze, costs off, summary off, timing off, buffers off) %s', $1) loop ln := regexp_replace(ln, 'Workers Launched: \d+', 'Workers Launched: N'); @@ -2641,7 +2641,7 @@ reset parallel_tuple_cost; reset min_parallel_table_scan_size; reset max_parallel_workers_per_gather; -- Test run-time partition pruning with an initplan -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from ab where a = (select max(a) from lprt_a) and b = (select max(a)-1 from lprt_a); QUERY PLAN ------------------------------------------------------------------------- @@ -2700,7 +2700,7 @@ select * from ab where a = (select max(a) from lprt_a) and b = (select max(a)-1 (52 rows) -- Test run-time partition pruning with UNION ALL parents -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from (select * from ab where a = 1 union all select * from ab) ab where b = (select 1); QUERY PLAN ------------------------------------------------------------------------------- @@ -2744,7 +2744,7 @@ select * from (select * from ab where a = 1 union all select * from ab) ab where (37 rows) -- A case containing a UNION ALL with a non-partitioned child. -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from (select * from ab where a = 1 union all (values(10,5)) union all select * from ab) ab where b = (select 1); QUERY PLAN ------------------------------------------------------------------------------- @@ -2803,7 +2803,7 @@ union all select tableoid::regclass,a,b from ab ) ab where a = $1 and b = (select -10); -- Ensure the xy_1 subplan is not pruned. -explain (analyze, costs off, summary off, timing off) execute ab_q6(1); +explain (analyze, costs off, summary off, timing off, buffers off) execute ab_q6(1); QUERY PLAN -------------------------------------------------------- Append (actual rows=0 loops=1) @@ -2952,7 +2952,7 @@ create index tprt6_idx on tprt_6 (col1); insert into tprt values (10), (20), (501), (502), (505), (1001), (4500); set enable_hashjoin = off; set enable_mergejoin = off; -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from tbl1 join tprt on tbl1.col1 > tprt.col1; QUERY PLAN -------------------------------------------------------------------------- @@ -2973,7 +2973,7 @@ select * from tbl1 join tprt on tbl1.col1 > tprt.col1; Index Cond: (col1 < tbl1.col1) (15 rows) -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from tbl1 join tprt on tbl1.col1 = tprt.col1; QUERY PLAN -------------------------------------------------------------------------- @@ -3018,7 +3018,7 @@ order by tbl1.col1, tprt.col1; -- Multiple partitions insert into tbl1 values (1001), (1010), (1011); -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from tbl1 inner join tprt on tbl1.col1 > tprt.col1; QUERY PLAN -------------------------------------------------------------------------- @@ -3039,7 +3039,7 @@ select * from tbl1 inner join tprt on tbl1.col1 > tprt.col1; Index Cond: (col1 < tbl1.col1) (15 rows) -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from tbl1 inner join tprt on tbl1.col1 = tprt.col1; QUERY PLAN -------------------------------------------------------------------------- @@ -3103,7 +3103,7 @@ order by tbl1.col1, tprt.col1; -- Last partition delete from tbl1; insert into tbl1 values (4400); -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from tbl1 join tprt on tbl1.col1 < tprt.col1; QUERY PLAN -------------------------------------------------------------------------- @@ -3135,7 +3135,7 @@ order by tbl1.col1, tprt.col1; -- No matching partition delete from tbl1; insert into tbl1 values (10000); -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from tbl1 join tprt on tbl1.col1 = tprt.col1; QUERY PLAN ------------------------------------------------------------------- @@ -3175,7 +3175,7 @@ alter table part_cab attach partition part_abc_p1 for values in(3); prepare part_abc_q1 (int, int, int) as select * from part_abc where a = $1 and b = $2 and c = $3; -- Single partition should be scanned. -explain (analyze, costs off, summary off, timing off) execute part_abc_q1 (1, 2, 3); +explain (analyze, costs off, summary off, timing off, buffers off) execute part_abc_q1 (1, 2, 3); QUERY PLAN ---------------------------------------------------------- Seq Scan on part_abc_p1 part_abc (actual rows=0 loops=1) @@ -3200,7 +3200,7 @@ select * from listp where b = 1; -- partitions before finally detecting the correct set of 2nd level partitions -- which match the given parameter. prepare q1 (int,int) as select * from listp where b in ($1,$2); -explain (analyze, costs off, summary off, timing off) execute q1 (1,1); +explain (analyze, costs off, summary off, timing off, buffers off) execute q1 (1,1); QUERY PLAN ------------------------------------------------------------- Append (actual rows=0 loops=1) @@ -3209,7 +3209,7 @@ explain (analyze, costs off, summary off, timing off) execute q1 (1,1); Filter: (b = ANY (ARRAY[$1, $2])) (4 rows) -explain (analyze, costs off, summary off, timing off) execute q1 (2,2); +explain (analyze, costs off, summary off, timing off, buffers off) execute q1 (2,2); QUERY PLAN ------------------------------------------------------------- Append (actual rows=0 loops=1) @@ -3219,7 +3219,7 @@ explain (analyze, costs off, summary off, timing off) execute q1 (2,2); (4 rows) -- Try with no matching partitions. -explain (analyze, costs off, summary off, timing off) execute q1 (0,0); +explain (analyze, costs off, summary off, timing off, buffers off) execute q1 (0,0); QUERY PLAN -------------------------------- Append (actual rows=0 loops=1) @@ -3230,7 +3230,7 @@ deallocate q1; -- Test more complex cases where a not-equal condition further eliminates partitions. prepare q1 (int,int,int,int) as select * from listp where b in($1,$2) and $3 <> b and $4 <> b; -- Both partitions allowed by IN clause, but one disallowed by <> clause -explain (analyze, costs off, summary off, timing off) execute q1 (1,2,2,0); +explain (analyze, costs off, summary off, timing off, buffers off) execute q1 (1,2,2,0); QUERY PLAN ------------------------------------------------------------------------- Append (actual rows=0 loops=1) @@ -3240,7 +3240,7 @@ explain (analyze, costs off, summary off, timing off) execute q1 (1,2,2,0); (4 rows) -- Both partitions allowed by IN clause, then both excluded again by <> clauses. -explain (analyze, costs off, summary off, timing off) execute q1 (1,2,2,1); +explain (analyze, costs off, summary off, timing off, buffers off) execute q1 (1,2,2,1); QUERY PLAN -------------------------------- Append (actual rows=0 loops=1) @@ -3248,7 +3248,7 @@ explain (analyze, costs off, summary off, timing off) execute q1 (1,2,2,1); (2 rows) -- Ensure Params that evaluate to NULL properly prune away all partitions -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from listp where a = (select null::int); QUERY PLAN ------------------------------------------------------ @@ -3273,7 +3273,7 @@ create table stable_qual_pruning2 partition of stable_qual_pruning create table stable_qual_pruning3 partition of stable_qual_pruning for values from ('3000-02-01') to ('3000-03-01'); -- comparison against a stable value requires run-time pruning -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from stable_qual_pruning where a < localtimestamp; QUERY PLAN -------------------------------------------------------------------------------------- @@ -3286,7 +3286,7 @@ select * from stable_qual_pruning where a < localtimestamp; (6 rows) -- timestamp < timestamptz comparison is only stable, not immutable -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from stable_qual_pruning where a < '2000-02-01'::timestamptz; QUERY PLAN -------------------------------------------------------------------------------------- @@ -3297,7 +3297,7 @@ select * from stable_qual_pruning where a < '2000-02-01'::timestamptz; (4 rows) -- check ScalarArrayOp cases -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from stable_qual_pruning where a = any(array['2010-02-01', '2020-01-01']::timestamp[]); QUERY PLAN @@ -3306,7 +3306,7 @@ select * from stable_qual_pruning One-Time Filter: false (2 rows) -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from stable_qual_pruning where a = any(array['2000-02-01', '2010-01-01']::timestamp[]); QUERY PLAN @@ -3315,7 +3315,7 @@ select * from stable_qual_pruning Filter: (a = ANY ('{"Tue Feb 01 00:00:00 2000","Fri Jan 01 00:00:00 2010"}'::timestamp without time zone[])) (2 rows) -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from stable_qual_pruning where a = any(array['2000-02-01', localtimestamp]::timestamp[]); QUERY PLAN @@ -3326,7 +3326,7 @@ select * from stable_qual_pruning Filter: (a = ANY (ARRAY['Tue Feb 01 00:00:00 2000'::timestamp without time zone, LOCALTIMESTAMP])) (4 rows) -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from stable_qual_pruning where a = any(array['2010-02-01', '2020-01-01']::timestamptz[]); QUERY PLAN @@ -3335,7 +3335,7 @@ select * from stable_qual_pruning Subplans Removed: 3 (2 rows) -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from stable_qual_pruning where a = any(array['2000-02-01', '2010-01-01']::timestamptz[]); QUERY PLAN @@ -3346,7 +3346,7 @@ select * from stable_qual_pruning Filter: (a = ANY ('{"Tue Feb 01 00:00:00 2000 PST","Fri Jan 01 00:00:00 2010 PST"}'::timestamp with time zone[])) (4 rows) -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from stable_qual_pruning where a = any(null::timestamptz[]); QUERY PLAN @@ -3374,7 +3374,7 @@ create table mc3p1 partition of mc3p create table mc3p2 partition of mc3p for values from (2, minvalue, minvalue) to (3, maxvalue, maxvalue); insert into mc3p values (0, 1, 1), (1, 1, 1), (2, 1, 1); -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from mc3p where a < 3 and abs(b) = 1; QUERY PLAN -------------------------------------------------------- @@ -3394,7 +3394,7 @@ select * from mc3p where a < 3 and abs(b) = 1; -- prepare ps1 as select * from mc3p where a = $1 and abs(b) < (select 3); -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) execute ps1(1); QUERY PLAN ------------------------------------------------------------- @@ -3409,7 +3409,7 @@ execute ps1(1); deallocate ps1; prepare ps2 as select * from mc3p where a <= $1 and abs(b) < (select 3); -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) execute ps2(1); QUERY PLAN -------------------------------------------------------------- @@ -3431,7 +3431,7 @@ insert into boolvalues values('t'),('f'); create table boolp (a bool) partition by list (a); create table boolp_t partition of boolp for values in('t'); create table boolp_f partition of boolp for values in('f'); -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from boolp where a = (select value from boolvalues where value); QUERY PLAN ----------------------------------------------------------- @@ -3446,7 +3446,7 @@ select * from boolp where a = (select value from boolvalues where value); Filter: (a = (InitPlan 1).col1) (9 rows) -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from boolp where a = (select value from boolvalues where not value); QUERY PLAN ----------------------------------------------------------- @@ -3475,7 +3475,7 @@ insert into ma_test select x,x from generate_series(0,29) t(x); create index on ma_test (b); analyze ma_test; prepare mt_q1 (int) as select a from ma_test where a >= $1 and a % 10 = 5 order by b; -explain (analyze, costs off, summary off, timing off) execute mt_q1(15); +explain (analyze, costs off, summary off, timing off, buffers off) execute mt_q1(15); QUERY PLAN ----------------------------------------------------------------------------------------- Merge Append (actual rows=2 loops=1) @@ -3496,7 +3496,7 @@ execute mt_q1(15); 25 (2 rows) -explain (analyze, costs off, summary off, timing off) execute mt_q1(25); +explain (analyze, costs off, summary off, timing off, buffers off) execute mt_q1(25); QUERY PLAN ----------------------------------------------------------------------------------------- Merge Append (actual rows=1 loops=1) @@ -3514,7 +3514,7 @@ execute mt_q1(25); (1 row) -- Ensure MergeAppend behaves correctly when no subplans match -explain (analyze, costs off, summary off, timing off) execute mt_q1(35); +explain (analyze, costs off, summary off, timing off, buffers off) execute mt_q1(35); QUERY PLAN -------------------------------------- Merge Append (actual rows=0 loops=1) @@ -3530,7 +3530,7 @@ execute mt_q1(35); deallocate mt_q1; prepare mt_q2 (int) as select * from ma_test where a >= $1 order by b limit 1; -- Ensure output list looks sane when the MergeAppend has no subplans. -explain (analyze, verbose, costs off, summary off, timing off) execute mt_q2 (35); +explain (analyze, verbose, costs off, summary off, timing off, buffers off) execute mt_q2 (35); QUERY PLAN -------------------------------------------- Limit (actual rows=0 loops=1) @@ -3542,7 +3542,7 @@ explain (analyze, verbose, costs off, summary off, timing off) execute mt_q2 (35 deallocate mt_q2; -- ensure initplan params properly prune partitions -explain (analyze, costs off, summary off, timing off) select * from ma_test where a >= (select min(b) from ma_test_p2) order by b; +explain (analyze, costs off, summary off, timing off, buffers off) select * from ma_test where a >= (select min(b) from ma_test_p2) order by b; QUERY PLAN ----------------------------------------------------------------------------------------------- Merge Append (actual rows=20 loops=1) @@ -3992,7 +3992,7 @@ create table listp (a int, b int) partition by list (a); create table listp1 partition of listp for values in(1); create table listp2 partition of listp for values in(2) partition by list(b); create table listp2_10 partition of listp2 for values in (10); -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from listp where a = (select 2) and b <> 10; QUERY PLAN --------------------------------------------------- @@ -4117,7 +4117,7 @@ create table rangep_0_to_100_3 partition of rangep_0_to_100 for values in(3); create table rangep_100_to_200 partition of rangep for values from (100) to (200); create index on rangep (a); -- Ensure run-time pruning works on the nested Merge Append -explain (analyze on, costs off, timing off, summary off) +explain (analyze on, costs off, timing off, summary off, buffers off) select * from rangep where b IN((select 1),(select 2)) order by a; QUERY PLAN ------------------------------------------------------------------------------------------------------------ diff --git a/src/test/regress/expected/select.out b/src/test/regress/expected/select.out index 33a6dceb0e..88911ca2b9 100644 --- a/src/test/regress/expected/select.out +++ b/src/test/regress/expected/select.out @@ -757,7 +757,7 @@ select * from onek2 where unique2 = 11 and stringu1 = 'ATAAAA'; (1 row) -- actually run the query with an analyze to use the partial index -explain (costs off, analyze on, timing off, summary off) +explain (costs off, analyze on, timing off, summary off, buffers off) select * from onek2 where unique2 = 11 and stringu1 = 'ATAAAA'; QUERY PLAN ----------------------------------------------------------------- diff --git a/src/test/regress/expected/select_parallel.out b/src/test/regress/expected/select_parallel.out index d17ade278b..21956d3c48 100644 --- a/src/test/regress/expected/select_parallel.out +++ b/src/test/regress/expected/select_parallel.out @@ -569,7 +569,7 @@ select count(*) from bmscantest where a>1; -- test accumulation of stats for parallel nodes reset enable_seqscan; alter table tenk2 set (parallel_workers = 0); -explain (analyze, timing off, summary off, costs off) +explain (analyze, timing off, summary off, costs off, buffers off) select count(*) from tenk1, tenk2 where tenk1.hundred > 1 and tenk2.thousand=0; QUERY PLAN @@ -595,7 +595,7 @@ $$ declare ln text; begin for ln in - explain (analyze, timing off, summary off, costs off) + explain (analyze, timing off, summary off, costs off, buffers off) select * from (select ten from tenk1 where ten < 100 order by ten) ss right join (values (1),(2),(3)) v(x) on true @@ -1158,7 +1158,7 @@ explain (costs off) -- to increase the parallel query test coverage SAVEPOINT settings; SET LOCAL debug_parallel_query = 1; -EXPLAIN (analyze, timing off, summary off, costs off) SELECT * FROM tenk1; +EXPLAIN (analyze, timing off, summary off, costs off, buffers off) SELECT * FROM tenk1; QUERY PLAN ------------------------------------------------------------- Gather (actual rows=10000 loops=1) diff --git a/src/test/regress/expected/subselect.out b/src/test/regress/expected/subselect.out index 2d35de3fad..e04cea3d36 100644 --- a/src/test/regress/expected/subselect.out +++ b/src/test/regress/expected/subselect.out @@ -1675,7 +1675,7 @@ $$ declare ln text; begin for ln in - explain (analyze, summary off, timing off, costs off) + explain (analyze, summary off, timing off, costs off, buffers off) select * from (select pk,c2 from sq_limit order by c1,pk) as x limit 3 loop ln := regexp_replace(ln, 'Memory: \S*', 'Memory: xxx'); diff --git a/src/test/regress/expected/tidscan.out b/src/test/regress/expected/tidscan.out index f133b5a4ac..f6ebdf0601 100644 --- a/src/test/regress/expected/tidscan.out +++ b/src/test/regress/expected/tidscan.out @@ -189,7 +189,7 @@ FETCH NEXT FROM c; (1 row) -- perform update -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *; QUERY PLAN --------------------------------------------------- @@ -205,7 +205,7 @@ FETCH NEXT FROM c; (1 row) -- perform update -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *; QUERY PLAN --------------------------------------------------- @@ -229,7 +229,7 @@ FETCH NEXT FROM c; (0 rows) -- should error out -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *; ERROR: cursor "c" is not positioned on a row ROLLBACK; diff --git a/src/test/regress/sql/brin_multi.sql b/src/test/regress/sql/brin_multi.sql index 55349b4e1f..dda9fb73e0 100644 --- a/src/test/regress/sql/brin_multi.sql +++ b/src/test/regress/sql/brin_multi.sql @@ -619,7 +619,7 @@ CREATE INDEX ON brin_date_test USING brin (a date_minmax_multi_ops) WITH (pages_ SET enable_seqscan = off; -- make sure the ranges were built correctly and 2023-01-01 eliminates all -EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF) +EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF, BUFFERS OFF) SELECT * FROM brin_date_test WHERE a = '2023-01-01'::date; DROP TABLE brin_date_test; @@ -636,10 +636,10 @@ CREATE INDEX ON brin_timestamp_test USING brin (a timestamp_minmax_multi_ops) WI SET enable_seqscan = off; -EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF) +EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF, BUFFERS OFF) SELECT * FROM brin_timestamp_test WHERE a = '2023-01-01'::timestamp; -EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF) +EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF, BUFFERS OFF) SELECT * FROM brin_timestamp_test WHERE a = '1900-01-01'::timestamp; DROP TABLE brin_timestamp_test; @@ -655,10 +655,10 @@ CREATE INDEX ON brin_date_test USING brin (a date_minmax_multi_ops) WITH (pages_ SET enable_seqscan = off; -EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF) +EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF, BUFFERS OFF) SELECT * FROM brin_date_test WHERE a = '2023-01-01'::date; -EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF) +EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF, BUFFERS OFF) SELECT * FROM brin_date_test WHERE a = '1900-01-01'::date; DROP TABLE brin_date_test; @@ -676,10 +676,10 @@ CREATE INDEX ON brin_interval_test USING brin (a interval_minmax_multi_ops) WITH SET enable_seqscan = off; -EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF) +EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF, BUFFERS OFF) SELECT * FROM brin_interval_test WHERE a = '-30 years'::interval; -EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF) +EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF, BUFFERS OFF) SELECT * FROM brin_interval_test WHERE a = '30 years'::interval; DROP TABLE brin_interval_test; @@ -695,10 +695,10 @@ CREATE INDEX ON brin_interval_test USING brin (a interval_minmax_multi_ops) WITH SET enable_seqscan = off; -EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF) +EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF, BUFFERS OFF) SELECT * FROM brin_interval_test WHERE a = '-30 years'::interval; -EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF) +EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF, BUFFERS OFF) SELECT * FROM brin_interval_test WHERE a = '30 years'::interval; DROP TABLE brin_interval_test; diff --git a/src/test/regress/sql/explain.sql b/src/test/regress/sql/explain.sql index 3ca285a1d7..f3677abcdd 100644 --- a/src/test/regress/sql/explain.sql +++ b/src/test/regress/sql/explain.sql @@ -62,8 +62,8 @@ set track_io_timing = off; -- Simple cases select explain_filter('explain select * from int8_tbl i8'); -select explain_filter('explain (analyze) select * from int8_tbl i8'); -select explain_filter('explain (analyze, verbose) select * from int8_tbl i8'); +select explain_filter('explain (analyze, buffers off) select * from int8_tbl i8'); +select explain_filter('explain (analyze, buffers off, verbose) select * from int8_tbl i8'); select explain_filter('explain (analyze, buffers, format text) select * from int8_tbl i8'); select explain_filter('explain (analyze, buffers, format xml) select * from int8_tbl i8'); select explain_filter('explain (analyze, serialize, buffers, format yaml) select * from int8_tbl i8'); @@ -92,13 +92,13 @@ rollback; select explain_filter('explain (generic_plan) select unique1 from tenk1 where thousand = $1'); -- should fail -select explain_filter('explain (analyze, generic_plan) select unique1 from tenk1 where thousand = $1'); +select explain_filter('explain (analyze, buffers off, generic_plan) select unique1 from tenk1 where thousand = $1'); -- MEMORY option select explain_filter('explain (memory) select * from int8_tbl i8'); -select explain_filter('explain (memory, analyze) select * from int8_tbl i8'); +select explain_filter('explain (memory, analyze, buffers off) select * from int8_tbl i8'); select explain_filter('explain (memory, summary, format yaml) select * from int8_tbl i8'); -select explain_filter('explain (memory, analyze, format json) select * from int8_tbl i8'); +select explain_filter('explain (memory, analyze, buffers off, format json) select * from int8_tbl i8'); prepare int8_query as select * from int8_tbl i8; select explain_filter('explain (memory) execute int8_query'); @@ -168,17 +168,17 @@ select explain_filter('explain (verbose) declare test_cur cursor for select * fr select explain_filter('explain (verbose) create table test_ctas as select 1'); -- Test SERIALIZE option -select explain_filter('explain (analyze,serialize) select * from int8_tbl i8'); +select explain_filter('explain (analyze,buffers off,serialize) select * from int8_tbl i8'); select explain_filter('explain (analyze,serialize text,buffers,timing off) select * from int8_tbl i8'); select explain_filter('explain (analyze,serialize binary,buffers,timing) select * from int8_tbl i8'); -- this tests an edge case where we have no data to return -select explain_filter('explain (analyze,serialize) create temp table explain_temp as select * from int8_tbl i8'); +select explain_filter('explain (analyze,buffers off,serialize) create temp table explain_temp as select * from int8_tbl i8'); -- Test tuplestore storage usage in Window aggregate (memory case) -select explain_filter('explain (analyze,costs off) select sum(n) over() from generate_series(1,10) a(n)'); +select explain_filter('explain (analyze,buffers off,costs off) select sum(n) over() from generate_series(1,10) a(n)'); -- Test tuplestore storage usage in Window aggregate (disk case) set work_mem to 64; -select explain_filter('explain (analyze,costs off) select sum(n) over() from generate_series(1,2000) a(n)'); +select explain_filter('explain (analyze,buffers off,costs off) select sum(n) over() from generate_series(1,2000) a(n)'); -- Test tuplestore storage usage in Window aggregate (memory and disk case, final result is disk) -select explain_filter('explain (analyze,costs off) select sum(n) over(partition by m) from (SELECT n < 3 as m, n from generate_series(1,2000) a(n))'); +select explain_filter('explain (analyze,buffers off,costs off) select sum(n) over(partition by m) from (SELECT n < 3 as m, n from generate_series(1,2000) a(n))'); reset work_mem; diff --git a/src/test/regress/sql/incremental_sort.sql b/src/test/regress/sql/incremental_sort.sql index 98b20e17e1..f1f8fae565 100644 --- a/src/test/regress/sql/incremental_sort.sql +++ b/src/test/regress/sql/incremental_sort.sql @@ -21,7 +21,7 @@ declare line text; begin for line in - execute 'explain (analyze, costs off, summary off, timing off) ' || query + execute 'explain (analyze, costs off, summary off, timing off, buffers off) ' || query loop out_line := regexp_replace(line, '\d+kB', 'NNkB', 'g'); return next; @@ -38,7 +38,7 @@ declare element jsonb; matching_nodes jsonb := '[]'::jsonb; begin - execute 'explain (analyze, costs off, summary off, timing off, format ''json'') ' || query into strict elements; + execute 'explain (analyze, costs off, summary off, timing off, buffers off, format ''json'') ' || query into strict elements; while jsonb_array_length(elements) > 0 loop element := elements->0; elements := elements - 0; diff --git a/src/test/regress/sql/memoize.sql b/src/test/regress/sql/memoize.sql index 2eaeb1477a..d5aab4e566 100644 --- a/src/test/regress/sql/memoize.sql +++ b/src/test/regress/sql/memoize.sql @@ -11,7 +11,7 @@ declare ln text; begin for ln in - execute format('explain (analyze, costs off, summary off, timing off) %s', + execute format('explain (analyze, costs off, summary off, timing off, buffers off) %s', query) loop if hide_hitmiss = true then diff --git a/src/test/regress/sql/merge.sql b/src/test/regress/sql/merge.sql index 5ddcca84f8..54929a92fa 100644 --- a/src/test/regress/sql/merge.sql +++ b/src/test/regress/sql/merge.sql @@ -1072,7 +1072,7 @@ $$ DECLARE ln text; BEGIN FOR ln IN - EXECUTE 'explain (analyze, timing off, summary off, costs off) ' || + EXECUTE 'explain (analyze, timing off, summary off, costs off, buffers off) ' || query LOOP ln := regexp_replace(ln, '(Memory( Usage)?|Buckets|Batches): \S*', '\1: xxx', 'g'); diff --git a/src/test/regress/sql/partition_prune.sql b/src/test/regress/sql/partition_prune.sql index 442428d937..d67598d5c7 100644 --- a/src/test/regress/sql/partition_prune.sql +++ b/src/test/regress/sql/partition_prune.sql @@ -12,7 +12,7 @@ declare ln text; begin for ln in - execute format('explain (analyze, costs off, summary off, timing off) %s', + execute format('explain (analyze, costs off, summary off, timing off, buffers off) %s', query) loop ln := regexp_replace(ln, 'Maximum Storage: \d+', 'Maximum Storage: N'); @@ -465,8 +465,8 @@ set enable_indexonlyscan = off; prepare ab_q1 (int, int, int) as select * from ab where a between $1 and $2 and b <= $3; -explain (analyze, costs off, summary off, timing off) execute ab_q1 (2, 2, 3); -explain (analyze, costs off, summary off, timing off) execute ab_q1 (1, 2, 3); +explain (analyze, costs off, summary off, timing off, buffers off) execute ab_q1 (2, 2, 3); +explain (analyze, costs off, summary off, timing off, buffers off) execute ab_q1 (1, 2, 3); deallocate ab_q1; @@ -474,21 +474,21 @@ deallocate ab_q1; prepare ab_q1 (int, int) as select a from ab where a between $1 and $2 and b < 3; -explain (analyze, costs off, summary off, timing off) execute ab_q1 (2, 2); -explain (analyze, costs off, summary off, timing off) execute ab_q1 (2, 4); +explain (analyze, costs off, summary off, timing off, buffers off) execute ab_q1 (2, 2); +explain (analyze, costs off, summary off, timing off, buffers off) execute ab_q1 (2, 4); -- Ensure a mix of PARAM_EXTERN and PARAM_EXEC Params work together at -- different levels of partitioning. prepare ab_q2 (int, int) as select a from ab where a between $1 and $2 and b < (select 3); -explain (analyze, costs off, summary off, timing off) execute ab_q2 (2, 2); +explain (analyze, costs off, summary off, timing off, buffers off) execute ab_q2 (2, 2); -- As above, but swap the PARAM_EXEC Param to the first partition level prepare ab_q3 (int, int) as select a from ab where b between $1 and $2 and a < (select 3); -explain (analyze, costs off, summary off, timing off) execute ab_q3 (2, 2); +explain (analyze, costs off, summary off, timing off, buffers off) execute ab_q3 (2, 2); -- -- Test runtime pruning with hash partitioned tables @@ -538,13 +538,13 @@ begin; create function list_part_fn(int) returns int as $$ begin return $1; end;$$ language plpgsql stable; -- Ensure pruning works using a stable function containing no Vars -explain (analyze, costs off, summary off, timing off) select * from list_part where a = list_part_fn(1); +explain (analyze, costs off, summary off, timing off, buffers off) select * from list_part where a = list_part_fn(1); -- Ensure pruning does not take place when the function has a Var parameter -explain (analyze, costs off, summary off, timing off) select * from list_part where a = list_part_fn(a); +explain (analyze, costs off, summary off, timing off, buffers off) select * from list_part where a = list_part_fn(a); -- Ensure pruning does not take place when the expression contains a Var. -explain (analyze, costs off, summary off, timing off) select * from list_part where a = list_part_fn(1) + a; +explain (analyze, costs off, summary off, timing off, buffers off) select * from list_part where a = list_part_fn(1) + a; rollback; @@ -567,7 +567,7 @@ declare ln text; begin for ln in - execute format('explain (analyze, costs off, summary off, timing off) %s', + execute format('explain (analyze, costs off, summary off, timing off, buffers off) %s', $1) loop ln := regexp_replace(ln, 'Workers Launched: \d+', 'Workers Launched: N'); @@ -650,15 +650,15 @@ reset min_parallel_table_scan_size; reset max_parallel_workers_per_gather; -- Test run-time partition pruning with an initplan -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from ab where a = (select max(a) from lprt_a) and b = (select max(a)-1 from lprt_a); -- Test run-time partition pruning with UNION ALL parents -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from (select * from ab where a = 1 union all select * from ab) ab where b = (select 1); -- A case containing a UNION ALL with a non-partitioned child. -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from (select * from ab where a = 1 union all (values(10,5)) union all select * from ab) ab where b = (select 1); -- Another UNION ALL test, but containing a mix of exec init and exec run-time pruning. @@ -678,7 +678,7 @@ union all ) ab where a = $1 and b = (select -10); -- Ensure the xy_1 subplan is not pruned. -explain (analyze, costs off, summary off, timing off) execute ab_q6(1); +explain (analyze, costs off, summary off, timing off, buffers off) execute ab_q6(1); -- Ensure we see just the xy_1 row. execute ab_q6(100); @@ -733,10 +733,10 @@ insert into tprt values (10), (20), (501), (502), (505), (1001), (4500); set enable_hashjoin = off; set enable_mergejoin = off; -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from tbl1 join tprt on tbl1.col1 > tprt.col1; -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from tbl1 join tprt on tbl1.col1 = tprt.col1; select tbl1.col1, tprt.col1 from tbl1 @@ -749,10 +749,10 @@ order by tbl1.col1, tprt.col1; -- Multiple partitions insert into tbl1 values (1001), (1010), (1011); -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from tbl1 inner join tprt on tbl1.col1 > tprt.col1; -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from tbl1 inner join tprt on tbl1.col1 = tprt.col1; select tbl1.col1, tprt.col1 from tbl1 @@ -766,7 +766,7 @@ order by tbl1.col1, tprt.col1; -- Last partition delete from tbl1; insert into tbl1 values (4400); -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from tbl1 join tprt on tbl1.col1 < tprt.col1; select tbl1.col1, tprt.col1 from tbl1 @@ -776,7 +776,7 @@ order by tbl1.col1, tprt.col1; -- No matching partition delete from tbl1; insert into tbl1 values (10000); -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from tbl1 join tprt on tbl1.col1 = tprt.col1; select tbl1.col1, tprt.col1 from tbl1 @@ -799,7 +799,7 @@ prepare part_abc_q1 (int, int, int) as select * from part_abc where a = $1 and b = $2 and c = $3; -- Single partition should be scanned. -explain (analyze, costs off, summary off, timing off) execute part_abc_q1 (1, 2, 3); +explain (analyze, costs off, summary off, timing off, buffers off) execute part_abc_q1 (1, 2, 3); deallocate part_abc_q1; @@ -819,12 +819,12 @@ select * from listp where b = 1; -- which match the given parameter. prepare q1 (int,int) as select * from listp where b in ($1,$2); -explain (analyze, costs off, summary off, timing off) execute q1 (1,1); +explain (analyze, costs off, summary off, timing off, buffers off) execute q1 (1,1); -explain (analyze, costs off, summary off, timing off) execute q1 (2,2); +explain (analyze, costs off, summary off, timing off, buffers off) execute q1 (2,2); -- Try with no matching partitions. -explain (analyze, costs off, summary off, timing off) execute q1 (0,0); +explain (analyze, costs off, summary off, timing off, buffers off) execute q1 (0,0); deallocate q1; @@ -832,13 +832,13 @@ deallocate q1; prepare q1 (int,int,int,int) as select * from listp where b in($1,$2) and $3 <> b and $4 <> b; -- Both partitions allowed by IN clause, but one disallowed by <> clause -explain (analyze, costs off, summary off, timing off) execute q1 (1,2,2,0); +explain (analyze, costs off, summary off, timing off, buffers off) execute q1 (1,2,2,0); -- Both partitions allowed by IN clause, then both excluded again by <> clauses. -explain (analyze, costs off, summary off, timing off) execute q1 (1,2,2,1); +explain (analyze, costs off, summary off, timing off, buffers off) execute q1 (1,2,2,1); -- Ensure Params that evaluate to NULL properly prune away all partitions -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from listp where a = (select null::int); drop table listp; @@ -855,30 +855,30 @@ create table stable_qual_pruning3 partition of stable_qual_pruning for values from ('3000-02-01') to ('3000-03-01'); -- comparison against a stable value requires run-time pruning -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from stable_qual_pruning where a < localtimestamp; -- timestamp < timestamptz comparison is only stable, not immutable -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from stable_qual_pruning where a < '2000-02-01'::timestamptz; -- check ScalarArrayOp cases -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from stable_qual_pruning where a = any(array['2010-02-01', '2020-01-01']::timestamp[]); -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from stable_qual_pruning where a = any(array['2000-02-01', '2010-01-01']::timestamp[]); -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from stable_qual_pruning where a = any(array['2000-02-01', localtimestamp]::timestamp[]); -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from stable_qual_pruning where a = any(array['2010-02-01', '2020-01-01']::timestamptz[]); -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from stable_qual_pruning where a = any(array['2000-02-01', '2010-01-01']::timestamptz[]); -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from stable_qual_pruning where a = any(null::timestamptz[]); @@ -898,7 +898,7 @@ create table mc3p2 partition of mc3p for values from (2, minvalue, minvalue) to (3, maxvalue, maxvalue); insert into mc3p values (0, 1, 1), (1, 1, 1), (2, 1, 1); -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from mc3p where a < 3 and abs(b) = 1; -- @@ -908,12 +908,12 @@ select * from mc3p where a < 3 and abs(b) = 1; -- prepare ps1 as select * from mc3p where a = $1 and abs(b) < (select 3); -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) execute ps1(1); deallocate ps1; prepare ps2 as select * from mc3p where a <= $1 and abs(b) < (select 3); -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) execute ps2(1); deallocate ps2; @@ -927,10 +927,10 @@ create table boolp (a bool) partition by list (a); create table boolp_t partition of boolp for values in('t'); create table boolp_f partition of boolp for values in('f'); -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from boolp where a = (select value from boolvalues where value); -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from boolp where a = (select value from boolvalues where not value); drop table boolp; @@ -950,12 +950,12 @@ create index on ma_test (b); analyze ma_test; prepare mt_q1 (int) as select a from ma_test where a >= $1 and a % 10 = 5 order by b; -explain (analyze, costs off, summary off, timing off) execute mt_q1(15); +explain (analyze, costs off, summary off, timing off, buffers off) execute mt_q1(15); execute mt_q1(15); -explain (analyze, costs off, summary off, timing off) execute mt_q1(25); +explain (analyze, costs off, summary off, timing off, buffers off) execute mt_q1(25); execute mt_q1(25); -- Ensure MergeAppend behaves correctly when no subplans match -explain (analyze, costs off, summary off, timing off) execute mt_q1(35); +explain (analyze, costs off, summary off, timing off, buffers off) execute mt_q1(35); execute mt_q1(35); deallocate mt_q1; @@ -963,12 +963,12 @@ deallocate mt_q1; prepare mt_q2 (int) as select * from ma_test where a >= $1 order by b limit 1; -- Ensure output list looks sane when the MergeAppend has no subplans. -explain (analyze, verbose, costs off, summary off, timing off) execute mt_q2 (35); +explain (analyze, verbose, costs off, summary off, timing off, buffers off) execute mt_q2 (35); deallocate mt_q2; -- ensure initplan params properly prune partitions -explain (analyze, costs off, summary off, timing off) select * from ma_test where a >= (select min(b) from ma_test_p2) order by b; +explain (analyze, costs off, summary off, timing off, buffers off) select * from ma_test where a >= (select min(b) from ma_test_p2) order by b; reset enable_seqscan; reset enable_sort; @@ -1148,7 +1148,7 @@ create table listp1 partition of listp for values in(1); create table listp2 partition of listp for values in(2) partition by list(b); create table listp2_10 partition of listp2 for values in (10); -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from listp where a = (select 2) and b <> 10; -- @@ -1216,7 +1216,7 @@ create table rangep_100_to_200 partition of rangep for values from (100) to (200 create index on rangep (a); -- Ensure run-time pruning works on the nested Merge Append -explain (analyze on, costs off, timing off, summary off) +explain (analyze on, costs off, timing off, summary off, buffers off) select * from rangep where b IN((select 1),(select 2)) order by a; reset enable_sort; drop table rangep; diff --git a/src/test/regress/sql/select.sql b/src/test/regress/sql/select.sql index 019f1e7673..1d1bf2b931 100644 --- a/src/test/regress/sql/select.sql +++ b/src/test/regress/sql/select.sql @@ -196,7 +196,7 @@ explain (costs off) select * from onek2 where unique2 = 11 and stringu1 = 'ATAAAA'; select * from onek2 where unique2 = 11 and stringu1 = 'ATAAAA'; -- actually run the query with an analyze to use the partial index -explain (costs off, analyze on, timing off, summary off) +explain (costs off, analyze on, timing off, summary off, buffers off) select * from onek2 where unique2 = 11 and stringu1 = 'ATAAAA'; explain (costs off) select unique2 from onek2 where unique2 = 11 and stringu1 = 'ATAAAA'; diff --git a/src/test/regress/sql/select_parallel.sql b/src/test/regress/sql/select_parallel.sql index 9ba1328fd2..868ff8a23b 100644 --- a/src/test/regress/sql/select_parallel.sql +++ b/src/test/regress/sql/select_parallel.sql @@ -223,7 +223,7 @@ select count(*) from bmscantest where a>1; -- test accumulation of stats for parallel nodes reset enable_seqscan; alter table tenk2 set (parallel_workers = 0); -explain (analyze, timing off, summary off, costs off) +explain (analyze, timing off, summary off, costs off, buffers off) select count(*) from tenk1, tenk2 where tenk1.hundred > 1 and tenk2.thousand=0; alter table tenk2 reset (parallel_workers); @@ -235,7 +235,7 @@ $$ declare ln text; begin for ln in - explain (analyze, timing off, summary off, costs off) + explain (analyze, timing off, summary off, costs off, buffers off) select * from (select ten from tenk1 where ten < 100 order by ten) ss right join (values (1),(2),(3)) v(x) on true @@ -443,7 +443,7 @@ explain (costs off) -- to increase the parallel query test coverage SAVEPOINT settings; SET LOCAL debug_parallel_query = 1; -EXPLAIN (analyze, timing off, summary off, costs off) SELECT * FROM tenk1; +EXPLAIN (analyze, timing off, summary off, costs off, buffers off) SELECT * FROM tenk1; ROLLBACK TO SAVEPOINT settings; -- provoke error in worker diff --git a/src/test/regress/sql/subselect.sql b/src/test/regress/sql/subselect.sql index af6e157aca..c53c7f724c 100644 --- a/src/test/regress/sql/subselect.sql +++ b/src/test/regress/sql/subselect.sql @@ -857,7 +857,7 @@ $$ declare ln text; begin for ln in - explain (analyze, summary off, timing off, costs off) + explain (analyze, summary off, timing off, costs off, buffers off) select * from (select pk,c2 from sq_limit order by c1,pk) as x limit 3 loop ln := regexp_replace(ln, 'Memory: \S*', 'Memory: xxx'); diff --git a/src/test/regress/sql/tidscan.sql b/src/test/regress/sql/tidscan.sql index 313e0fb9b6..1b82d5f1a5 100644 --- a/src/test/regress/sql/tidscan.sql +++ b/src/test/regress/sql/tidscan.sql @@ -68,17 +68,17 @@ DECLARE c CURSOR FOR SELECT ctid, * FROM tidscan; FETCH NEXT FROM c; -- skip one row FETCH NEXT FROM c; -- perform update -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *; FETCH NEXT FROM c; -- perform update -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *; SELECT * FROM tidscan; -- position cursor past any rows FETCH NEXT FROM c; -- should error out -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *; ROLLBACK; Attachments: [text/plain] explain_buffers_by_default.patch (73.7K, ../../CAApHDvrYwEt=ueMTn39okNihZffhKgqvxedLqDJu=hmSsZ=g9Q@mail.gmail.com/2-explain_buffers_by_default.patch) download | inline diff: diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out index f2bcd6aa98..bf322198a2 100644 --- a/contrib/postgres_fdw/expected/postgres_fdw.out +++ b/contrib/postgres_fdw/expected/postgres_fdw.out @@ -11553,7 +11553,7 @@ SELECT * FROM local_tbl, async_pt WHERE local_tbl.a = async_pt.a AND local_tbl.c Filter: (async_pt_3.a = local_tbl.a) (15 rows) -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) SELECT * FROM local_tbl, async_pt WHERE local_tbl.a = async_pt.a AND local_tbl.c = 'bar'; QUERY PLAN ------------------------------------------------------------------------------- @@ -11799,7 +11799,7 @@ SELECT * FROM local_tbl t1 LEFT JOIN (SELECT *, (SELECT count(*) FROM async_pt W Remote SQL: SELECT a, b, c FROM public.base_tbl2 WHERE ((a < 3000)) (20 rows) -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) SELECT * FROM local_tbl t1 LEFT JOIN (SELECT *, (SELECT count(*) FROM async_pt WHERE a < 3000) FROM async_pt WHERE a < 3000) t2 ON t1.a = t2.a; QUERY PLAN ----------------------------------------------------------------------------------------- @@ -11843,7 +11843,7 @@ SELECT * FROM async_pt t1 WHERE t1.b === 505 LIMIT 1; Filter: (t1_3.b === 505) (14 rows) -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) SELECT * FROM async_pt t1 WHERE t1.b === 505 LIMIT 1; QUERY PLAN ------------------------------------------------------------------------- @@ -12003,7 +12003,7 @@ DELETE FROM async_pt WHERE b = 0 RETURNING *; DELETE FROM async_p1; DELETE FROM async_p2; DELETE FROM async_p3; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) SELECT * FROM async_pt; QUERY PLAN ------------------------------------------------------------------------- diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql index 372fe6dad1..3900522ccb 100644 --- a/contrib/postgres_fdw/sql/postgres_fdw.sql +++ b/contrib/postgres_fdw/sql/postgres_fdw.sql @@ -3904,7 +3904,7 @@ ALTER FOREIGN TABLE async_p2 OPTIONS (use_remote_estimate 'true'); EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM local_tbl, async_pt WHERE local_tbl.a = async_pt.a AND local_tbl.c = 'bar'; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) SELECT * FROM local_tbl, async_pt WHERE local_tbl.a = async_pt.a AND local_tbl.c = 'bar'; SELECT * FROM local_tbl, async_pt WHERE local_tbl.a = async_pt.a AND local_tbl.c = 'bar'; @@ -3979,13 +3979,13 @@ ANALYZE local_tbl; EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM local_tbl t1 LEFT JOIN (SELECT *, (SELECT count(*) FROM async_pt WHERE a < 3000) FROM async_pt WHERE a < 3000) t2 ON t1.a = t2.a; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) SELECT * FROM local_tbl t1 LEFT JOIN (SELECT *, (SELECT count(*) FROM async_pt WHERE a < 3000) FROM async_pt WHERE a < 3000) t2 ON t1.a = t2.a; SELECT * FROM local_tbl t1 LEFT JOIN (SELECT *, (SELECT count(*) FROM async_pt WHERE a < 3000) FROM async_pt WHERE a < 3000) t2 ON t1.a = t2.a; EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM async_pt t1 WHERE t1.b === 505 LIMIT 1; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) SELECT * FROM async_pt t1 WHERE t1.b === 505 LIMIT 1; SELECT * FROM async_pt t1 WHERE t1.b === 505 LIMIT 1; @@ -4037,7 +4037,7 @@ DELETE FROM async_p1; DELETE FROM async_p2; DELETE FROM async_p3; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) SELECT * FROM async_pt; -- Clean up diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c index 7c0fd63b2f..87ad83e984 100644 --- a/src/backend/commands/explain.c +++ b/src/backend/commands/explain.c @@ -199,6 +199,7 @@ ExplainQuery(ParseState *pstate, ExplainStmt *stmt, ListCell *lc; bool timing_set = false; bool summary_set = false; + bool buffers_set = false; /* Parse options list. */ foreach(lc, stmt->options) @@ -212,7 +213,10 @@ ExplainQuery(ParseState *pstate, ExplainStmt *stmt, else if (strcmp(opt->defname, "costs") == 0) es->costs = defGetBoolean(opt); else if (strcmp(opt->defname, "buffers") == 0) + { es->buffers = defGetBoolean(opt); + buffers_set = true; + } else if (strcmp(opt->defname, "wal") == 0) es->wal = defGetBoolean(opt); else if (strcmp(opt->defname, "settings") == 0) @@ -291,6 +295,7 @@ ExplainQuery(ParseState *pstate, ExplainStmt *stmt, /* if the timing was not set explicitly, set default value */ es->timing = (timing_set) ? es->timing : es->analyze; + es->buffers = (buffers_set) ? es->buffers : es->analyze; /* check that timing is used with EXPLAIN ANALYZE */ if (es->timing && !es->analyze) diff --git a/src/test/regress/expected/brin_multi.out b/src/test/regress/expected/brin_multi.out index ae9ce9d8ec..f2d1465818 100644 --- a/src/test/regress/expected/brin_multi.out +++ b/src/test/regress/expected/brin_multi.out @@ -845,7 +845,7 @@ INSERT INTO brin_date_test SELECT '5874897-12-01'::date + i FROM generate_series CREATE INDEX ON brin_date_test USING brin (a date_minmax_multi_ops) WITH (pages_per_range=1); SET enable_seqscan = off; -- make sure the ranges were built correctly and 2023-01-01 eliminates all -EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF) +EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF, BUFFERS OFF) SELECT * FROM brin_date_test WHERE a = '2023-01-01'::date; QUERY PLAN ------------------------------------------------------------------------- @@ -864,7 +864,7 @@ INSERT INTO brin_timestamp_test SELECT i FROM generate_series('2000-01-01'::timestamp, '2000-02-09'::timestamp, '1 day'::interval) s(i); CREATE INDEX ON brin_timestamp_test USING brin (a timestamp_minmax_multi_ops) WITH (pages_per_range=1); SET enable_seqscan = off; -EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF) +EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF, BUFFERS OFF) SELECT * FROM brin_timestamp_test WHERE a = '2023-01-01'::timestamp; QUERY PLAN ------------------------------------------------------------------------------ @@ -874,7 +874,7 @@ SELECT * FROM brin_timestamp_test WHERE a = '2023-01-01'::timestamp; Index Cond: (a = '2023-01-01 00:00:00'::timestamp without time zone) (4 rows) -EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF) +EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF, BUFFERS OFF) SELECT * FROM brin_timestamp_test WHERE a = '1900-01-01'::timestamp; QUERY PLAN ------------------------------------------------------------------------------ @@ -892,7 +892,7 @@ INSERT INTO brin_date_test VALUES ('-infinity'), ('infinity'); INSERT INTO brin_date_test SELECT '2000-01-01'::date + i FROM generate_series(1, 40) s(i); CREATE INDEX ON brin_date_test USING brin (a date_minmax_multi_ops) WITH (pages_per_range=1); SET enable_seqscan = off; -EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF) +EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF, BUFFERS OFF) SELECT * FROM brin_date_test WHERE a = '2023-01-01'::date; QUERY PLAN ------------------------------------------------------------------------- @@ -902,7 +902,7 @@ SELECT * FROM brin_date_test WHERE a = '2023-01-01'::date; Index Cond: (a = '2023-01-01'::date) (4 rows) -EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF) +EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF, BUFFERS OFF) SELECT * FROM brin_date_test WHERE a = '1900-01-01'::date; QUERY PLAN ------------------------------------------------------------------------- @@ -921,7 +921,7 @@ INSERT INTO brin_interval_test SELECT (i || ' years')::interval FROM generate_se INSERT INTO brin_interval_test SELECT (i || ' years')::interval FROM generate_series( 177999980, 178000000) s(i); CREATE INDEX ON brin_interval_test USING brin (a interval_minmax_multi_ops) WITH (pages_per_range=1); SET enable_seqscan = off; -EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF) +EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF, BUFFERS OFF) SELECT * FROM brin_interval_test WHERE a = '-30 years'::interval; QUERY PLAN ----------------------------------------------------------------------------- @@ -931,7 +931,7 @@ SELECT * FROM brin_interval_test WHERE a = '-30 years'::interval; Index Cond: (a = '@ 30 years ago'::interval) (4 rows) -EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF) +EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF, BUFFERS OFF) SELECT * FROM brin_interval_test WHERE a = '30 years'::interval; QUERY PLAN ----------------------------------------------------------------------------- @@ -949,7 +949,7 @@ INSERT INTO brin_interval_test VALUES ('-infinity'), ('infinity'); INSERT INTO brin_interval_test SELECT (i || ' days')::interval FROM generate_series(100, 140) s(i); CREATE INDEX ON brin_interval_test USING brin (a interval_minmax_multi_ops) WITH (pages_per_range=1); SET enable_seqscan = off; -EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF) +EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF, BUFFERS OFF) SELECT * FROM brin_interval_test WHERE a = '-30 years'::interval; QUERY PLAN ----------------------------------------------------------------------------- @@ -959,7 +959,7 @@ SELECT * FROM brin_interval_test WHERE a = '-30 years'::interval; Index Cond: (a = '@ 30 years ago'::interval) (4 rows) -EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF) +EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF, BUFFERS OFF) SELECT * FROM brin_interval_test WHERE a = '30 years'::interval; QUERY PLAN ----------------------------------------------------------------------------- diff --git a/src/test/regress/expected/explain.out b/src/test/regress/expected/explain.out index d2eef8097c..f97fe7542c 100644 --- a/src/test/regress/expected/explain.out +++ b/src/test/regress/expected/explain.out @@ -60,7 +60,7 @@ select explain_filter('explain select * from int8_tbl i8'); Seq Scan on int8_tbl i8 (cost=N.N..N.N rows=N width=N) (1 row) -select explain_filter('explain (analyze) select * from int8_tbl i8'); +select explain_filter('explain (analyze, buffers off) select * from int8_tbl i8'); explain_filter ----------------------------------------------------------------------------------------------- Seq Scan on int8_tbl i8 (cost=N.N..N.N rows=N width=N) (actual time=N.N..N.N rows=N loops=N) @@ -68,7 +68,7 @@ select explain_filter('explain (analyze) select * from int8_tbl i8'); Execution Time: N.N ms (3 rows) -select explain_filter('explain (analyze, verbose) select * from int8_tbl i8'); +select explain_filter('explain (analyze, buffers off, verbose) select * from int8_tbl i8'); explain_filter ------------------------------------------------------------------------------------------------------ Seq Scan on public.int8_tbl i8 (cost=N.N..N.N rows=N width=N) (actual time=N.N..N.N rows=N loops=N) @@ -341,7 +341,7 @@ select explain_filter('explain (generic_plan) select unique1 from tenk1 where th (4 rows) -- should fail -select explain_filter('explain (analyze, generic_plan) select unique1 from tenk1 where thousand = $1'); +select explain_filter('explain (analyze, buffers off, generic_plan) select unique1 from tenk1 where thousand = $1'); ERROR: EXPLAIN options ANALYZE and GENERIC_PLAN cannot be used together CONTEXT: PL/pgSQL function explain_filter(text) line 5 at FOR over EXECUTE statement -- MEMORY option @@ -352,7 +352,7 @@ select explain_filter('explain (memory) select * from int8_tbl i8'); Memory: used=NkB allocated=NkB (2 rows) -select explain_filter('explain (memory, analyze) select * from int8_tbl i8'); +select explain_filter('explain (memory, analyze, buffers off) select * from int8_tbl i8'); explain_filter ----------------------------------------------------------------------------------------------- Seq Scan on int8_tbl i8 (cost=N.N..N.N rows=N width=N) (actual time=N.N..N.N rows=N loops=N) @@ -381,7 +381,7 @@ select explain_filter('explain (memory, summary, format yaml) select * from int8 Planning Time: N.N (1 row) -select explain_filter('explain (memory, analyze, format json) select * from int8_tbl i8'); +select explain_filter('explain (memory, analyze, buffers off, format json) select * from int8_tbl i8'); explain_filter ------------------------------------ [ + @@ -680,7 +680,7 @@ select explain_filter('explain (verbose) create table test_ctas as select 1'); (3 rows) -- Test SERIALIZE option -select explain_filter('explain (analyze,serialize) select * from int8_tbl i8'); +select explain_filter('explain (analyze,buffers off,serialize) select * from int8_tbl i8'); explain_filter ----------------------------------------------------------------------------------------------- Seq Scan on int8_tbl i8 (cost=N.N..N.N rows=N width=N) (actual time=N.N..N.N rows=N loops=N) @@ -708,7 +708,7 @@ select explain_filter('explain (analyze,serialize binary,buffers,timing) select (4 rows) -- this tests an edge case where we have no data to return -select explain_filter('explain (analyze,serialize) create temp table explain_temp as select * from int8_tbl i8'); +select explain_filter('explain (analyze,buffers off,serialize) create temp table explain_temp as select * from int8_tbl i8'); explain_filter ----------------------------------------------------------------------------------------------- Seq Scan on int8_tbl i8 (cost=N.N..N.N rows=N width=N) (actual time=N.N..N.N rows=N loops=N) @@ -718,7 +718,7 @@ select explain_filter('explain (analyze,serialize) create temp table explain_tem (4 rows) -- Test tuplestore storage usage in Window aggregate (memory case) -select explain_filter('explain (analyze,costs off) select sum(n) over() from generate_series(1,10) a(n)'); +select explain_filter('explain (analyze,buffers off,costs off) select sum(n) over() from generate_series(1,10) a(n)'); explain_filter -------------------------------------------------------------------------------- WindowAgg (actual time=N.N..N.N rows=N loops=N) @@ -730,7 +730,7 @@ select explain_filter('explain (analyze,costs off) select sum(n) over() from gen -- Test tuplestore storage usage in Window aggregate (disk case) set work_mem to 64; -select explain_filter('explain (analyze,costs off) select sum(n) over() from generate_series(1,2000) a(n)'); +select explain_filter('explain (analyze,buffers off,costs off) select sum(n) over() from generate_series(1,2000) a(n)'); explain_filter -------------------------------------------------------------------------------- WindowAgg (actual time=N.N..N.N rows=N loops=N) @@ -741,7 +741,7 @@ select explain_filter('explain (analyze,costs off) select sum(n) over() from gen (5 rows) -- Test tuplestore storage usage in Window aggregate (memory and disk case, final result is disk) -select explain_filter('explain (analyze,costs off) select sum(n) over(partition by m) from (SELECT n < 3 as m, n from generate_series(1,2000) a(n))'); +select explain_filter('explain (analyze,buffers off,costs off) select sum(n) over(partition by m) from (SELECT n < 3 as m, n from generate_series(1,2000) a(n))'); explain_filter -------------------------------------------------------------------------------------- WindowAgg (actual time=N.N..N.N rows=N loops=N) diff --git a/src/test/regress/expected/incremental_sort.out b/src/test/regress/expected/incremental_sort.out index 2df7a5db12..d597575840 100644 --- a/src/test/regress/expected/incremental_sort.out +++ b/src/test/regress/expected/incremental_sort.out @@ -39,7 +39,7 @@ declare line text; begin for line in - execute 'explain (analyze, costs off, summary off, timing off) ' || query + execute 'explain (analyze, costs off, summary off, timing off, buffers off) ' || query loop out_line := regexp_replace(line, '\d+kB', 'NNkB', 'g'); return next; @@ -55,7 +55,7 @@ declare element jsonb; matching_nodes jsonb := '[]'::jsonb; begin - execute 'explain (analyze, costs off, summary off, timing off, format ''json'') ' || query into strict elements; + execute 'explain (analyze, costs off, summary off, timing off, buffers off, format ''json'') ' || query into strict elements; while jsonb_array_length(elements) > 0 loop element := elements->0; elements := elements - 0; diff --git a/src/test/regress/expected/memoize.out b/src/test/regress/expected/memoize.out index f6b8329cd6..5ecf971dad 100644 --- a/src/test/regress/expected/memoize.out +++ b/src/test/regress/expected/memoize.out @@ -10,7 +10,7 @@ declare ln text; begin for ln in - execute format('explain (analyze, costs off, summary off, timing off) %s', + execute format('explain (analyze, costs off, summary off, timing off, buffers off) %s', query) loop if hide_hitmiss = true then diff --git a/src/test/regress/expected/merge.out b/src/test/regress/expected/merge.out index 521d70a891..28d8551063 100644 --- a/src/test/regress/expected/merge.out +++ b/src/test/regress/expected/merge.out @@ -1621,7 +1621,7 @@ $$ DECLARE ln text; BEGIN FOR ln IN - EXECUTE 'explain (analyze, timing off, summary off, costs off) ' || + EXECUTE 'explain (analyze, timing off, summary off, costs off, buffers off) ' || query LOOP ln := regexp_replace(ln, '(Memory( Usage)?|Buckets|Batches): \S*', '\1: xxx', 'g'); diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.out index 7a03b4e360..c52bc40e81 100644 --- a/src/test/regress/expected/partition_prune.out +++ b/src/test/regress/expected/partition_prune.out @@ -11,7 +11,7 @@ declare ln text; begin for ln in - execute format('explain (analyze, costs off, summary off, timing off) %s', + execute format('explain (analyze, costs off, summary off, timing off, buffers off) %s', query) loop ln := regexp_replace(ln, 'Maximum Storage: \d+', 'Maximum Storage: N'); @@ -2127,7 +2127,7 @@ create table ab_a3_b3 partition of ab_a3 for values in (3); set enable_indexonlyscan = off; prepare ab_q1 (int, int, int) as select * from ab where a between $1 and $2 and b <= $3; -explain (analyze, costs off, summary off, timing off) execute ab_q1 (2, 2, 3); +explain (analyze, costs off, summary off, timing off, buffers off) execute ab_q1 (2, 2, 3); QUERY PLAN --------------------------------------------------------- Append (actual rows=0 loops=1) @@ -2140,7 +2140,7 @@ explain (analyze, costs off, summary off, timing off) execute ab_q1 (2, 2, 3); Filter: ((a >= $1) AND (a <= $2) AND (b <= $3)) (8 rows) -explain (analyze, costs off, summary off, timing off) execute ab_q1 (1, 2, 3); +explain (analyze, costs off, summary off, timing off, buffers off) execute ab_q1 (1, 2, 3); QUERY PLAN --------------------------------------------------------- Append (actual rows=0 loops=1) @@ -2163,7 +2163,7 @@ deallocate ab_q1; -- Runtime pruning after optimizer pruning prepare ab_q1 (int, int) as select a from ab where a between $1 and $2 and b < 3; -explain (analyze, costs off, summary off, timing off) execute ab_q1 (2, 2); +explain (analyze, costs off, summary off, timing off, buffers off) execute ab_q1 (2, 2); QUERY PLAN --------------------------------------------------------- Append (actual rows=0 loops=1) @@ -2174,7 +2174,7 @@ explain (analyze, costs off, summary off, timing off) execute ab_q1 (2, 2); Filter: ((a >= $1) AND (a <= $2) AND (b < 3)) (6 rows) -explain (analyze, costs off, summary off, timing off) execute ab_q1 (2, 4); +explain (analyze, costs off, summary off, timing off, buffers off) execute ab_q1 (2, 4); QUERY PLAN --------------------------------------------------------- Append (actual rows=0 loops=1) @@ -2193,7 +2193,7 @@ explain (analyze, costs off, summary off, timing off) execute ab_q1 (2, 4); -- different levels of partitioning. prepare ab_q2 (int, int) as select a from ab where a between $1 and $2 and b < (select 3); -explain (analyze, costs off, summary off, timing off) execute ab_q2 (2, 2); +explain (analyze, costs off, summary off, timing off, buffers off) execute ab_q2 (2, 2); QUERY PLAN ----------------------------------------------------------------------- Append (actual rows=0 loops=1) @@ -2211,7 +2211,7 @@ explain (analyze, costs off, summary off, timing off) execute ab_q2 (2, 2); -- As above, but swap the PARAM_EXEC Param to the first partition level prepare ab_q3 (int, int) as select a from ab where b between $1 and $2 and a < (select 3); -explain (analyze, costs off, summary off, timing off) execute ab_q3 (2, 2); +explain (analyze, costs off, summary off, timing off, buffers off) execute ab_q3 (2, 2); QUERY PLAN ----------------------------------------------------------------------- Append (actual rows=0 loops=1) @@ -2273,7 +2273,7 @@ begin; -- Test run-time pruning using stable functions create function list_part_fn(int) returns int as $$ begin return $1; end;$$ language plpgsql stable; -- Ensure pruning works using a stable function containing no Vars -explain (analyze, costs off, summary off, timing off) select * from list_part where a = list_part_fn(1); +explain (analyze, costs off, summary off, timing off, buffers off) select * from list_part where a = list_part_fn(1); QUERY PLAN ------------------------------------------------------------------ Append (actual rows=1 loops=1) @@ -2283,7 +2283,7 @@ explain (analyze, costs off, summary off, timing off) select * from list_part wh (4 rows) -- Ensure pruning does not take place when the function has a Var parameter -explain (analyze, costs off, summary off, timing off) select * from list_part where a = list_part_fn(a); +explain (analyze, costs off, summary off, timing off, buffers off) select * from list_part where a = list_part_fn(a); QUERY PLAN ------------------------------------------------------------------ Append (actual rows=4 loops=1) @@ -2298,7 +2298,7 @@ explain (analyze, costs off, summary off, timing off) select * from list_part wh (9 rows) -- Ensure pruning does not take place when the expression contains a Var. -explain (analyze, costs off, summary off, timing off) select * from list_part where a = list_part_fn(1) + a; +explain (analyze, costs off, summary off, timing off, buffers off) select * from list_part where a = list_part_fn(1) + a; QUERY PLAN ------------------------------------------------------------------ Append (actual rows=0 loops=1) @@ -2334,7 +2334,7 @@ declare ln text; begin for ln in - execute format('explain (analyze, costs off, summary off, timing off) %s', + execute format('explain (analyze, costs off, summary off, timing off, buffers off) %s', $1) loop ln := regexp_replace(ln, 'Workers Launched: \d+', 'Workers Launched: N'); @@ -2641,7 +2641,7 @@ reset parallel_tuple_cost; reset min_parallel_table_scan_size; reset max_parallel_workers_per_gather; -- Test run-time partition pruning with an initplan -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from ab where a = (select max(a) from lprt_a) and b = (select max(a)-1 from lprt_a); QUERY PLAN ------------------------------------------------------------------------- @@ -2700,7 +2700,7 @@ select * from ab where a = (select max(a) from lprt_a) and b = (select max(a)-1 (52 rows) -- Test run-time partition pruning with UNION ALL parents -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from (select * from ab where a = 1 union all select * from ab) ab where b = (select 1); QUERY PLAN ------------------------------------------------------------------------------- @@ -2744,7 +2744,7 @@ select * from (select * from ab where a = 1 union all select * from ab) ab where (37 rows) -- A case containing a UNION ALL with a non-partitioned child. -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from (select * from ab where a = 1 union all (values(10,5)) union all select * from ab) ab where b = (select 1); QUERY PLAN ------------------------------------------------------------------------------- @@ -2803,7 +2803,7 @@ union all select tableoid::regclass,a,b from ab ) ab where a = $1 and b = (select -10); -- Ensure the xy_1 subplan is not pruned. -explain (analyze, costs off, summary off, timing off) execute ab_q6(1); +explain (analyze, costs off, summary off, timing off, buffers off) execute ab_q6(1); QUERY PLAN -------------------------------------------------------- Append (actual rows=0 loops=1) @@ -2952,7 +2952,7 @@ create index tprt6_idx on tprt_6 (col1); insert into tprt values (10), (20), (501), (502), (505), (1001), (4500); set enable_hashjoin = off; set enable_mergejoin = off; -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from tbl1 join tprt on tbl1.col1 > tprt.col1; QUERY PLAN -------------------------------------------------------------------------- @@ -2973,7 +2973,7 @@ select * from tbl1 join tprt on tbl1.col1 > tprt.col1; Index Cond: (col1 < tbl1.col1) (15 rows) -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from tbl1 join tprt on tbl1.col1 = tprt.col1; QUERY PLAN -------------------------------------------------------------------------- @@ -3018,7 +3018,7 @@ order by tbl1.col1, tprt.col1; -- Multiple partitions insert into tbl1 values (1001), (1010), (1011); -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from tbl1 inner join tprt on tbl1.col1 > tprt.col1; QUERY PLAN -------------------------------------------------------------------------- @@ -3039,7 +3039,7 @@ select * from tbl1 inner join tprt on tbl1.col1 > tprt.col1; Index Cond: (col1 < tbl1.col1) (15 rows) -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from tbl1 inner join tprt on tbl1.col1 = tprt.col1; QUERY PLAN -------------------------------------------------------------------------- @@ -3103,7 +3103,7 @@ order by tbl1.col1, tprt.col1; -- Last partition delete from tbl1; insert into tbl1 values (4400); -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from tbl1 join tprt on tbl1.col1 < tprt.col1; QUERY PLAN -------------------------------------------------------------------------- @@ -3135,7 +3135,7 @@ order by tbl1.col1, tprt.col1; -- No matching partition delete from tbl1; insert into tbl1 values (10000); -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from tbl1 join tprt on tbl1.col1 = tprt.col1; QUERY PLAN ------------------------------------------------------------------- @@ -3175,7 +3175,7 @@ alter table part_cab attach partition part_abc_p1 for values in(3); prepare part_abc_q1 (int, int, int) as select * from part_abc where a = $1 and b = $2 and c = $3; -- Single partition should be scanned. -explain (analyze, costs off, summary off, timing off) execute part_abc_q1 (1, 2, 3); +explain (analyze, costs off, summary off, timing off, buffers off) execute part_abc_q1 (1, 2, 3); QUERY PLAN ---------------------------------------------------------- Seq Scan on part_abc_p1 part_abc (actual rows=0 loops=1) @@ -3200,7 +3200,7 @@ select * from listp where b = 1; -- partitions before finally detecting the correct set of 2nd level partitions -- which match the given parameter. prepare q1 (int,int) as select * from listp where b in ($1,$2); -explain (analyze, costs off, summary off, timing off) execute q1 (1,1); +explain (analyze, costs off, summary off, timing off, buffers off) execute q1 (1,1); QUERY PLAN ------------------------------------------------------------- Append (actual rows=0 loops=1) @@ -3209,7 +3209,7 @@ explain (analyze, costs off, summary off, timing off) execute q1 (1,1); Filter: (b = ANY (ARRAY[$1, $2])) (4 rows) -explain (analyze, costs off, summary off, timing off) execute q1 (2,2); +explain (analyze, costs off, summary off, timing off, buffers off) execute q1 (2,2); QUERY PLAN ------------------------------------------------------------- Append (actual rows=0 loops=1) @@ -3219,7 +3219,7 @@ explain (analyze, costs off, summary off, timing off) execute q1 (2,2); (4 rows) -- Try with no matching partitions. -explain (analyze, costs off, summary off, timing off) execute q1 (0,0); +explain (analyze, costs off, summary off, timing off, buffers off) execute q1 (0,0); QUERY PLAN -------------------------------- Append (actual rows=0 loops=1) @@ -3230,7 +3230,7 @@ deallocate q1; -- Test more complex cases where a not-equal condition further eliminates partitions. prepare q1 (int,int,int,int) as select * from listp where b in($1,$2) and $3 <> b and $4 <> b; -- Both partitions allowed by IN clause, but one disallowed by <> clause -explain (analyze, costs off, summary off, timing off) execute q1 (1,2,2,0); +explain (analyze, costs off, summary off, timing off, buffers off) execute q1 (1,2,2,0); QUERY PLAN ------------------------------------------------------------------------- Append (actual rows=0 loops=1) @@ -3240,7 +3240,7 @@ explain (analyze, costs off, summary off, timing off) execute q1 (1,2,2,0); (4 rows) -- Both partitions allowed by IN clause, then both excluded again by <> clauses. -explain (analyze, costs off, summary off, timing off) execute q1 (1,2,2,1); +explain (analyze, costs off, summary off, timing off, buffers off) execute q1 (1,2,2,1); QUERY PLAN -------------------------------- Append (actual rows=0 loops=1) @@ -3248,7 +3248,7 @@ explain (analyze, costs off, summary off, timing off) execute q1 (1,2,2,1); (2 rows) -- Ensure Params that evaluate to NULL properly prune away all partitions -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from listp where a = (select null::int); QUERY PLAN ------------------------------------------------------ @@ -3273,7 +3273,7 @@ create table stable_qual_pruning2 partition of stable_qual_pruning create table stable_qual_pruning3 partition of stable_qual_pruning for values from ('3000-02-01') to ('3000-03-01'); -- comparison against a stable value requires run-time pruning -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from stable_qual_pruning where a < localtimestamp; QUERY PLAN -------------------------------------------------------------------------------------- @@ -3286,7 +3286,7 @@ select * from stable_qual_pruning where a < localtimestamp; (6 rows) -- timestamp < timestamptz comparison is only stable, not immutable -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from stable_qual_pruning where a < '2000-02-01'::timestamptz; QUERY PLAN -------------------------------------------------------------------------------------- @@ -3297,7 +3297,7 @@ select * from stable_qual_pruning where a < '2000-02-01'::timestamptz; (4 rows) -- check ScalarArrayOp cases -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from stable_qual_pruning where a = any(array['2010-02-01', '2020-01-01']::timestamp[]); QUERY PLAN @@ -3306,7 +3306,7 @@ select * from stable_qual_pruning One-Time Filter: false (2 rows) -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from stable_qual_pruning where a = any(array['2000-02-01', '2010-01-01']::timestamp[]); QUERY PLAN @@ -3315,7 +3315,7 @@ select * from stable_qual_pruning Filter: (a = ANY ('{"Tue Feb 01 00:00:00 2000","Fri Jan 01 00:00:00 2010"}'::timestamp without time zone[])) (2 rows) -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from stable_qual_pruning where a = any(array['2000-02-01', localtimestamp]::timestamp[]); QUERY PLAN @@ -3326,7 +3326,7 @@ select * from stable_qual_pruning Filter: (a = ANY (ARRAY['Tue Feb 01 00:00:00 2000'::timestamp without time zone, LOCALTIMESTAMP])) (4 rows) -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from stable_qual_pruning where a = any(array['2010-02-01', '2020-01-01']::timestamptz[]); QUERY PLAN @@ -3335,7 +3335,7 @@ select * from stable_qual_pruning Subplans Removed: 3 (2 rows) -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from stable_qual_pruning where a = any(array['2000-02-01', '2010-01-01']::timestamptz[]); QUERY PLAN @@ -3346,7 +3346,7 @@ select * from stable_qual_pruning Filter: (a = ANY ('{"Tue Feb 01 00:00:00 2000 PST","Fri Jan 01 00:00:00 2010 PST"}'::timestamp with time zone[])) (4 rows) -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from stable_qual_pruning where a = any(null::timestamptz[]); QUERY PLAN @@ -3374,7 +3374,7 @@ create table mc3p1 partition of mc3p create table mc3p2 partition of mc3p for values from (2, minvalue, minvalue) to (3, maxvalue, maxvalue); insert into mc3p values (0, 1, 1), (1, 1, 1), (2, 1, 1); -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from mc3p where a < 3 and abs(b) = 1; QUERY PLAN -------------------------------------------------------- @@ -3394,7 +3394,7 @@ select * from mc3p where a < 3 and abs(b) = 1; -- prepare ps1 as select * from mc3p where a = $1 and abs(b) < (select 3); -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) execute ps1(1); QUERY PLAN ------------------------------------------------------------- @@ -3409,7 +3409,7 @@ execute ps1(1); deallocate ps1; prepare ps2 as select * from mc3p where a <= $1 and abs(b) < (select 3); -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) execute ps2(1); QUERY PLAN -------------------------------------------------------------- @@ -3431,7 +3431,7 @@ insert into boolvalues values('t'),('f'); create table boolp (a bool) partition by list (a); create table boolp_t partition of boolp for values in('t'); create table boolp_f partition of boolp for values in('f'); -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from boolp where a = (select value from boolvalues where value); QUERY PLAN ----------------------------------------------------------- @@ -3446,7 +3446,7 @@ select * from boolp where a = (select value from boolvalues where value); Filter: (a = (InitPlan 1).col1) (9 rows) -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from boolp where a = (select value from boolvalues where not value); QUERY PLAN ----------------------------------------------------------- @@ -3475,7 +3475,7 @@ insert into ma_test select x,x from generate_series(0,29) t(x); create index on ma_test (b); analyze ma_test; prepare mt_q1 (int) as select a from ma_test where a >= $1 and a % 10 = 5 order by b; -explain (analyze, costs off, summary off, timing off) execute mt_q1(15); +explain (analyze, costs off, summary off, timing off, buffers off) execute mt_q1(15); QUERY PLAN ----------------------------------------------------------------------------------------- Merge Append (actual rows=2 loops=1) @@ -3496,7 +3496,7 @@ execute mt_q1(15); 25 (2 rows) -explain (analyze, costs off, summary off, timing off) execute mt_q1(25); +explain (analyze, costs off, summary off, timing off, buffers off) execute mt_q1(25); QUERY PLAN ----------------------------------------------------------------------------------------- Merge Append (actual rows=1 loops=1) @@ -3514,7 +3514,7 @@ execute mt_q1(25); (1 row) -- Ensure MergeAppend behaves correctly when no subplans match -explain (analyze, costs off, summary off, timing off) execute mt_q1(35); +explain (analyze, costs off, summary off, timing off, buffers off) execute mt_q1(35); QUERY PLAN -------------------------------------- Merge Append (actual rows=0 loops=1) @@ -3530,7 +3530,7 @@ execute mt_q1(35); deallocate mt_q1; prepare mt_q2 (int) as select * from ma_test where a >= $1 order by b limit 1; -- Ensure output list looks sane when the MergeAppend has no subplans. -explain (analyze, verbose, costs off, summary off, timing off) execute mt_q2 (35); +explain (analyze, verbose, costs off, summary off, timing off, buffers off) execute mt_q2 (35); QUERY PLAN -------------------------------------------- Limit (actual rows=0 loops=1) @@ -3542,7 +3542,7 @@ explain (analyze, verbose, costs off, summary off, timing off) execute mt_q2 (35 deallocate mt_q2; -- ensure initplan params properly prune partitions -explain (analyze, costs off, summary off, timing off) select * from ma_test where a >= (select min(b) from ma_test_p2) order by b; +explain (analyze, costs off, summary off, timing off, buffers off) select * from ma_test where a >= (select min(b) from ma_test_p2) order by b; QUERY PLAN ----------------------------------------------------------------------------------------------- Merge Append (actual rows=20 loops=1) @@ -3992,7 +3992,7 @@ create table listp (a int, b int) partition by list (a); create table listp1 partition of listp for values in(1); create table listp2 partition of listp for values in(2) partition by list(b); create table listp2_10 partition of listp2 for values in (10); -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from listp where a = (select 2) and b <> 10; QUERY PLAN --------------------------------------------------- @@ -4117,7 +4117,7 @@ create table rangep_0_to_100_3 partition of rangep_0_to_100 for values in(3); create table rangep_100_to_200 partition of rangep for values from (100) to (200); create index on rangep (a); -- Ensure run-time pruning works on the nested Merge Append -explain (analyze on, costs off, timing off, summary off) +explain (analyze on, costs off, timing off, summary off, buffers off) select * from rangep where b IN((select 1),(select 2)) order by a; QUERY PLAN ------------------------------------------------------------------------------------------------------------ diff --git a/src/test/regress/expected/select.out b/src/test/regress/expected/select.out index 33a6dceb0e..88911ca2b9 100644 --- a/src/test/regress/expected/select.out +++ b/src/test/regress/expected/select.out @@ -757,7 +757,7 @@ select * from onek2 where unique2 = 11 and stringu1 = 'ATAAAA'; (1 row) -- actually run the query with an analyze to use the partial index -explain (costs off, analyze on, timing off, summary off) +explain (costs off, analyze on, timing off, summary off, buffers off) select * from onek2 where unique2 = 11 and stringu1 = 'ATAAAA'; QUERY PLAN ----------------------------------------------------------------- diff --git a/src/test/regress/expected/select_parallel.out b/src/test/regress/expected/select_parallel.out index d17ade278b..21956d3c48 100644 --- a/src/test/regress/expected/select_parallel.out +++ b/src/test/regress/expected/select_parallel.out @@ -569,7 +569,7 @@ select count(*) from bmscantest where a>1; -- test accumulation of stats for parallel nodes reset enable_seqscan; alter table tenk2 set (parallel_workers = 0); -explain (analyze, timing off, summary off, costs off) +explain (analyze, timing off, summary off, costs off, buffers off) select count(*) from tenk1, tenk2 where tenk1.hundred > 1 and tenk2.thousand=0; QUERY PLAN @@ -595,7 +595,7 @@ $$ declare ln text; begin for ln in - explain (analyze, timing off, summary off, costs off) + explain (analyze, timing off, summary off, costs off, buffers off) select * from (select ten from tenk1 where ten < 100 order by ten) ss right join (values (1),(2),(3)) v(x) on true @@ -1158,7 +1158,7 @@ explain (costs off) -- to increase the parallel query test coverage SAVEPOINT settings; SET LOCAL debug_parallel_query = 1; -EXPLAIN (analyze, timing off, summary off, costs off) SELECT * FROM tenk1; +EXPLAIN (analyze, timing off, summary off, costs off, buffers off) SELECT * FROM tenk1; QUERY PLAN ------------------------------------------------------------- Gather (actual rows=10000 loops=1) diff --git a/src/test/regress/expected/subselect.out b/src/test/regress/expected/subselect.out index 2d35de3fad..e04cea3d36 100644 --- a/src/test/regress/expected/subselect.out +++ b/src/test/regress/expected/subselect.out @@ -1675,7 +1675,7 @@ $$ declare ln text; begin for ln in - explain (analyze, summary off, timing off, costs off) + explain (analyze, summary off, timing off, costs off, buffers off) select * from (select pk,c2 from sq_limit order by c1,pk) as x limit 3 loop ln := regexp_replace(ln, 'Memory: \S*', 'Memory: xxx'); diff --git a/src/test/regress/expected/tidscan.out b/src/test/regress/expected/tidscan.out index f133b5a4ac..f6ebdf0601 100644 --- a/src/test/regress/expected/tidscan.out +++ b/src/test/regress/expected/tidscan.out @@ -189,7 +189,7 @@ FETCH NEXT FROM c; (1 row) -- perform update -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *; QUERY PLAN --------------------------------------------------- @@ -205,7 +205,7 @@ FETCH NEXT FROM c; (1 row) -- perform update -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *; QUERY PLAN --------------------------------------------------- @@ -229,7 +229,7 @@ FETCH NEXT FROM c; (0 rows) -- should error out -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *; ERROR: cursor "c" is not positioned on a row ROLLBACK; diff --git a/src/test/regress/sql/brin_multi.sql b/src/test/regress/sql/brin_multi.sql index 55349b4e1f..dda9fb73e0 100644 --- a/src/test/regress/sql/brin_multi.sql +++ b/src/test/regress/sql/brin_multi.sql @@ -619,7 +619,7 @@ CREATE INDEX ON brin_date_test USING brin (a date_minmax_multi_ops) WITH (pages_ SET enable_seqscan = off; -- make sure the ranges were built correctly and 2023-01-01 eliminates all -EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF) +EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF, BUFFERS OFF) SELECT * FROM brin_date_test WHERE a = '2023-01-01'::date; DROP TABLE brin_date_test; @@ -636,10 +636,10 @@ CREATE INDEX ON brin_timestamp_test USING brin (a timestamp_minmax_multi_ops) WI SET enable_seqscan = off; -EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF) +EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF, BUFFERS OFF) SELECT * FROM brin_timestamp_test WHERE a = '2023-01-01'::timestamp; -EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF) +EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF, BUFFERS OFF) SELECT * FROM brin_timestamp_test WHERE a = '1900-01-01'::timestamp; DROP TABLE brin_timestamp_test; @@ -655,10 +655,10 @@ CREATE INDEX ON brin_date_test USING brin (a date_minmax_multi_ops) WITH (pages_ SET enable_seqscan = off; -EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF) +EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF, BUFFERS OFF) SELECT * FROM brin_date_test WHERE a = '2023-01-01'::date; -EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF) +EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF, BUFFERS OFF) SELECT * FROM brin_date_test WHERE a = '1900-01-01'::date; DROP TABLE brin_date_test; @@ -676,10 +676,10 @@ CREATE INDEX ON brin_interval_test USING brin (a interval_minmax_multi_ops) WITH SET enable_seqscan = off; -EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF) +EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF, BUFFERS OFF) SELECT * FROM brin_interval_test WHERE a = '-30 years'::interval; -EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF) +EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF, BUFFERS OFF) SELECT * FROM brin_interval_test WHERE a = '30 years'::interval; DROP TABLE brin_interval_test; @@ -695,10 +695,10 @@ CREATE INDEX ON brin_interval_test USING brin (a interval_minmax_multi_ops) WITH SET enable_seqscan = off; -EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF) +EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF, BUFFERS OFF) SELECT * FROM brin_interval_test WHERE a = '-30 years'::interval; -EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF) +EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF, BUFFERS OFF) SELECT * FROM brin_interval_test WHERE a = '30 years'::interval; DROP TABLE brin_interval_test; diff --git a/src/test/regress/sql/explain.sql b/src/test/regress/sql/explain.sql index 3ca285a1d7..f3677abcdd 100644 --- a/src/test/regress/sql/explain.sql +++ b/src/test/regress/sql/explain.sql @@ -62,8 +62,8 @@ set track_io_timing = off; -- Simple cases select explain_filter('explain select * from int8_tbl i8'); -select explain_filter('explain (analyze) select * from int8_tbl i8'); -select explain_filter('explain (analyze, verbose) select * from int8_tbl i8'); +select explain_filter('explain (analyze, buffers off) select * from int8_tbl i8'); +select explain_filter('explain (analyze, buffers off, verbose) select * from int8_tbl i8'); select explain_filter('explain (analyze, buffers, format text) select * from int8_tbl i8'); select explain_filter('explain (analyze, buffers, format xml) select * from int8_tbl i8'); select explain_filter('explain (analyze, serialize, buffers, format yaml) select * from int8_tbl i8'); @@ -92,13 +92,13 @@ rollback; select explain_filter('explain (generic_plan) select unique1 from tenk1 where thousand = $1'); -- should fail -select explain_filter('explain (analyze, generic_plan) select unique1 from tenk1 where thousand = $1'); +select explain_filter('explain (analyze, buffers off, generic_plan) select unique1 from tenk1 where thousand = $1'); -- MEMORY option select explain_filter('explain (memory) select * from int8_tbl i8'); -select explain_filter('explain (memory, analyze) select * from int8_tbl i8'); +select explain_filter('explain (memory, analyze, buffers off) select * from int8_tbl i8'); select explain_filter('explain (memory, summary, format yaml) select * from int8_tbl i8'); -select explain_filter('explain (memory, analyze, format json) select * from int8_tbl i8'); +select explain_filter('explain (memory, analyze, buffers off, format json) select * from int8_tbl i8'); prepare int8_query as select * from int8_tbl i8; select explain_filter('explain (memory) execute int8_query'); @@ -168,17 +168,17 @@ select explain_filter('explain (verbose) declare test_cur cursor for select * fr select explain_filter('explain (verbose) create table test_ctas as select 1'); -- Test SERIALIZE option -select explain_filter('explain (analyze,serialize) select * from int8_tbl i8'); +select explain_filter('explain (analyze,buffers off,serialize) select * from int8_tbl i8'); select explain_filter('explain (analyze,serialize text,buffers,timing off) select * from int8_tbl i8'); select explain_filter('explain (analyze,serialize binary,buffers,timing) select * from int8_tbl i8'); -- this tests an edge case where we have no data to return -select explain_filter('explain (analyze,serialize) create temp table explain_temp as select * from int8_tbl i8'); +select explain_filter('explain (analyze,buffers off,serialize) create temp table explain_temp as select * from int8_tbl i8'); -- Test tuplestore storage usage in Window aggregate (memory case) -select explain_filter('explain (analyze,costs off) select sum(n) over() from generate_series(1,10) a(n)'); +select explain_filter('explain (analyze,buffers off,costs off) select sum(n) over() from generate_series(1,10) a(n)'); -- Test tuplestore storage usage in Window aggregate (disk case) set work_mem to 64; -select explain_filter('explain (analyze,costs off) select sum(n) over() from generate_series(1,2000) a(n)'); +select explain_filter('explain (analyze,buffers off,costs off) select sum(n) over() from generate_series(1,2000) a(n)'); -- Test tuplestore storage usage in Window aggregate (memory and disk case, final result is disk) -select explain_filter('explain (analyze,costs off) select sum(n) over(partition by m) from (SELECT n < 3 as m, n from generate_series(1,2000) a(n))'); +select explain_filter('explain (analyze,buffers off,costs off) select sum(n) over(partition by m) from (SELECT n < 3 as m, n from generate_series(1,2000) a(n))'); reset work_mem; diff --git a/src/test/regress/sql/incremental_sort.sql b/src/test/regress/sql/incremental_sort.sql index 98b20e17e1..f1f8fae565 100644 --- a/src/test/regress/sql/incremental_sort.sql +++ b/src/test/regress/sql/incremental_sort.sql @@ -21,7 +21,7 @@ declare line text; begin for line in - execute 'explain (analyze, costs off, summary off, timing off) ' || query + execute 'explain (analyze, costs off, summary off, timing off, buffers off) ' || query loop out_line := regexp_replace(line, '\d+kB', 'NNkB', 'g'); return next; @@ -38,7 +38,7 @@ declare element jsonb; matching_nodes jsonb := '[]'::jsonb; begin - execute 'explain (analyze, costs off, summary off, timing off, format ''json'') ' || query into strict elements; + execute 'explain (analyze, costs off, summary off, timing off, buffers off, format ''json'') ' || query into strict elements; while jsonb_array_length(elements) > 0 loop element := elements->0; elements := elements - 0; diff --git a/src/test/regress/sql/memoize.sql b/src/test/regress/sql/memoize.sql index 2eaeb1477a..d5aab4e566 100644 --- a/src/test/regress/sql/memoize.sql +++ b/src/test/regress/sql/memoize.sql @@ -11,7 +11,7 @@ declare ln text; begin for ln in - execute format('explain (analyze, costs off, summary off, timing off) %s', + execute format('explain (analyze, costs off, summary off, timing off, buffers off) %s', query) loop if hide_hitmiss = true then diff --git a/src/test/regress/sql/merge.sql b/src/test/regress/sql/merge.sql index 5ddcca84f8..54929a92fa 100644 --- a/src/test/regress/sql/merge.sql +++ b/src/test/regress/sql/merge.sql @@ -1072,7 +1072,7 @@ $$ DECLARE ln text; BEGIN FOR ln IN - EXECUTE 'explain (analyze, timing off, summary off, costs off) ' || + EXECUTE 'explain (analyze, timing off, summary off, costs off, buffers off) ' || query LOOP ln := regexp_replace(ln, '(Memory( Usage)?|Buckets|Batches): \S*', '\1: xxx', 'g'); diff --git a/src/test/regress/sql/partition_prune.sql b/src/test/regress/sql/partition_prune.sql index 442428d937..d67598d5c7 100644 --- a/src/test/regress/sql/partition_prune.sql +++ b/src/test/regress/sql/partition_prune.sql @@ -12,7 +12,7 @@ declare ln text; begin for ln in - execute format('explain (analyze, costs off, summary off, timing off) %s', + execute format('explain (analyze, costs off, summary off, timing off, buffers off) %s', query) loop ln := regexp_replace(ln, 'Maximum Storage: \d+', 'Maximum Storage: N'); @@ -465,8 +465,8 @@ set enable_indexonlyscan = off; prepare ab_q1 (int, int, int) as select * from ab where a between $1 and $2 and b <= $3; -explain (analyze, costs off, summary off, timing off) execute ab_q1 (2, 2, 3); -explain (analyze, costs off, summary off, timing off) execute ab_q1 (1, 2, 3); +explain (analyze, costs off, summary off, timing off, buffers off) execute ab_q1 (2, 2, 3); +explain (analyze, costs off, summary off, timing off, buffers off) execute ab_q1 (1, 2, 3); deallocate ab_q1; @@ -474,21 +474,21 @@ deallocate ab_q1; prepare ab_q1 (int, int) as select a from ab where a between $1 and $2 and b < 3; -explain (analyze, costs off, summary off, timing off) execute ab_q1 (2, 2); -explain (analyze, costs off, summary off, timing off) execute ab_q1 (2, 4); +explain (analyze, costs off, summary off, timing off, buffers off) execute ab_q1 (2, 2); +explain (analyze, costs off, summary off, timing off, buffers off) execute ab_q1 (2, 4); -- Ensure a mix of PARAM_EXTERN and PARAM_EXEC Params work together at -- different levels of partitioning. prepare ab_q2 (int, int) as select a from ab where a between $1 and $2 and b < (select 3); -explain (analyze, costs off, summary off, timing off) execute ab_q2 (2, 2); +explain (analyze, costs off, summary off, timing off, buffers off) execute ab_q2 (2, 2); -- As above, but swap the PARAM_EXEC Param to the first partition level prepare ab_q3 (int, int) as select a from ab where b between $1 and $2 and a < (select 3); -explain (analyze, costs off, summary off, timing off) execute ab_q3 (2, 2); +explain (analyze, costs off, summary off, timing off, buffers off) execute ab_q3 (2, 2); -- -- Test runtime pruning with hash partitioned tables @@ -538,13 +538,13 @@ begin; create function list_part_fn(int) returns int as $$ begin return $1; end;$$ language plpgsql stable; -- Ensure pruning works using a stable function containing no Vars -explain (analyze, costs off, summary off, timing off) select * from list_part where a = list_part_fn(1); +explain (analyze, costs off, summary off, timing off, buffers off) select * from list_part where a = list_part_fn(1); -- Ensure pruning does not take place when the function has a Var parameter -explain (analyze, costs off, summary off, timing off) select * from list_part where a = list_part_fn(a); +explain (analyze, costs off, summary off, timing off, buffers off) select * from list_part where a = list_part_fn(a); -- Ensure pruning does not take place when the expression contains a Var. -explain (analyze, costs off, summary off, timing off) select * from list_part where a = list_part_fn(1) + a; +explain (analyze, costs off, summary off, timing off, buffers off) select * from list_part where a = list_part_fn(1) + a; rollback; @@ -567,7 +567,7 @@ declare ln text; begin for ln in - execute format('explain (analyze, costs off, summary off, timing off) %s', + execute format('explain (analyze, costs off, summary off, timing off, buffers off) %s', $1) loop ln := regexp_replace(ln, 'Workers Launched: \d+', 'Workers Launched: N'); @@ -650,15 +650,15 @@ reset min_parallel_table_scan_size; reset max_parallel_workers_per_gather; -- Test run-time partition pruning with an initplan -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from ab where a = (select max(a) from lprt_a) and b = (select max(a)-1 from lprt_a); -- Test run-time partition pruning with UNION ALL parents -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from (select * from ab where a = 1 union all select * from ab) ab where b = (select 1); -- A case containing a UNION ALL with a non-partitioned child. -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from (select * from ab where a = 1 union all (values(10,5)) union all select * from ab) ab where b = (select 1); -- Another UNION ALL test, but containing a mix of exec init and exec run-time pruning. @@ -678,7 +678,7 @@ union all ) ab where a = $1 and b = (select -10); -- Ensure the xy_1 subplan is not pruned. -explain (analyze, costs off, summary off, timing off) execute ab_q6(1); +explain (analyze, costs off, summary off, timing off, buffers off) execute ab_q6(1); -- Ensure we see just the xy_1 row. execute ab_q6(100); @@ -733,10 +733,10 @@ insert into tprt values (10), (20), (501), (502), (505), (1001), (4500); set enable_hashjoin = off; set enable_mergejoin = off; -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from tbl1 join tprt on tbl1.col1 > tprt.col1; -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from tbl1 join tprt on tbl1.col1 = tprt.col1; select tbl1.col1, tprt.col1 from tbl1 @@ -749,10 +749,10 @@ order by tbl1.col1, tprt.col1; -- Multiple partitions insert into tbl1 values (1001), (1010), (1011); -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from tbl1 inner join tprt on tbl1.col1 > tprt.col1; -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from tbl1 inner join tprt on tbl1.col1 = tprt.col1; select tbl1.col1, tprt.col1 from tbl1 @@ -766,7 +766,7 @@ order by tbl1.col1, tprt.col1; -- Last partition delete from tbl1; insert into tbl1 values (4400); -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from tbl1 join tprt on tbl1.col1 < tprt.col1; select tbl1.col1, tprt.col1 from tbl1 @@ -776,7 +776,7 @@ order by tbl1.col1, tprt.col1; -- No matching partition delete from tbl1; insert into tbl1 values (10000); -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from tbl1 join tprt on tbl1.col1 = tprt.col1; select tbl1.col1, tprt.col1 from tbl1 @@ -799,7 +799,7 @@ prepare part_abc_q1 (int, int, int) as select * from part_abc where a = $1 and b = $2 and c = $3; -- Single partition should be scanned. -explain (analyze, costs off, summary off, timing off) execute part_abc_q1 (1, 2, 3); +explain (analyze, costs off, summary off, timing off, buffers off) execute part_abc_q1 (1, 2, 3); deallocate part_abc_q1; @@ -819,12 +819,12 @@ select * from listp where b = 1; -- which match the given parameter. prepare q1 (int,int) as select * from listp where b in ($1,$2); -explain (analyze, costs off, summary off, timing off) execute q1 (1,1); +explain (analyze, costs off, summary off, timing off, buffers off) execute q1 (1,1); -explain (analyze, costs off, summary off, timing off) execute q1 (2,2); +explain (analyze, costs off, summary off, timing off, buffers off) execute q1 (2,2); -- Try with no matching partitions. -explain (analyze, costs off, summary off, timing off) execute q1 (0,0); +explain (analyze, costs off, summary off, timing off, buffers off) execute q1 (0,0); deallocate q1; @@ -832,13 +832,13 @@ deallocate q1; prepare q1 (int,int,int,int) as select * from listp where b in($1,$2) and $3 <> b and $4 <> b; -- Both partitions allowed by IN clause, but one disallowed by <> clause -explain (analyze, costs off, summary off, timing off) execute q1 (1,2,2,0); +explain (analyze, costs off, summary off, timing off, buffers off) execute q1 (1,2,2,0); -- Both partitions allowed by IN clause, then both excluded again by <> clauses. -explain (analyze, costs off, summary off, timing off) execute q1 (1,2,2,1); +explain (analyze, costs off, summary off, timing off, buffers off) execute q1 (1,2,2,1); -- Ensure Params that evaluate to NULL properly prune away all partitions -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from listp where a = (select null::int); drop table listp; @@ -855,30 +855,30 @@ create table stable_qual_pruning3 partition of stable_qual_pruning for values from ('3000-02-01') to ('3000-03-01'); -- comparison against a stable value requires run-time pruning -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from stable_qual_pruning where a < localtimestamp; -- timestamp < timestamptz comparison is only stable, not immutable -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from stable_qual_pruning where a < '2000-02-01'::timestamptz; -- check ScalarArrayOp cases -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from stable_qual_pruning where a = any(array['2010-02-01', '2020-01-01']::timestamp[]); -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from stable_qual_pruning where a = any(array['2000-02-01', '2010-01-01']::timestamp[]); -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from stable_qual_pruning where a = any(array['2000-02-01', localtimestamp]::timestamp[]); -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from stable_qual_pruning where a = any(array['2010-02-01', '2020-01-01']::timestamptz[]); -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from stable_qual_pruning where a = any(array['2000-02-01', '2010-01-01']::timestamptz[]); -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from stable_qual_pruning where a = any(null::timestamptz[]); @@ -898,7 +898,7 @@ create table mc3p2 partition of mc3p for values from (2, minvalue, minvalue) to (3, maxvalue, maxvalue); insert into mc3p values (0, 1, 1), (1, 1, 1), (2, 1, 1); -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from mc3p where a < 3 and abs(b) = 1; -- @@ -908,12 +908,12 @@ select * from mc3p where a < 3 and abs(b) = 1; -- prepare ps1 as select * from mc3p where a = $1 and abs(b) < (select 3); -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) execute ps1(1); deallocate ps1; prepare ps2 as select * from mc3p where a <= $1 and abs(b) < (select 3); -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) execute ps2(1); deallocate ps2; @@ -927,10 +927,10 @@ create table boolp (a bool) partition by list (a); create table boolp_t partition of boolp for values in('t'); create table boolp_f partition of boolp for values in('f'); -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from boolp where a = (select value from boolvalues where value); -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from boolp where a = (select value from boolvalues where not value); drop table boolp; @@ -950,12 +950,12 @@ create index on ma_test (b); analyze ma_test; prepare mt_q1 (int) as select a from ma_test where a >= $1 and a % 10 = 5 order by b; -explain (analyze, costs off, summary off, timing off) execute mt_q1(15); +explain (analyze, costs off, summary off, timing off, buffers off) execute mt_q1(15); execute mt_q1(15); -explain (analyze, costs off, summary off, timing off) execute mt_q1(25); +explain (analyze, costs off, summary off, timing off, buffers off) execute mt_q1(25); execute mt_q1(25); -- Ensure MergeAppend behaves correctly when no subplans match -explain (analyze, costs off, summary off, timing off) execute mt_q1(35); +explain (analyze, costs off, summary off, timing off, buffers off) execute mt_q1(35); execute mt_q1(35); deallocate mt_q1; @@ -963,12 +963,12 @@ deallocate mt_q1; prepare mt_q2 (int) as select * from ma_test where a >= $1 order by b limit 1; -- Ensure output list looks sane when the MergeAppend has no subplans. -explain (analyze, verbose, costs off, summary off, timing off) execute mt_q2 (35); +explain (analyze, verbose, costs off, summary off, timing off, buffers off) execute mt_q2 (35); deallocate mt_q2; -- ensure initplan params properly prune partitions -explain (analyze, costs off, summary off, timing off) select * from ma_test where a >= (select min(b) from ma_test_p2) order by b; +explain (analyze, costs off, summary off, timing off, buffers off) select * from ma_test where a >= (select min(b) from ma_test_p2) order by b; reset enable_seqscan; reset enable_sort; @@ -1148,7 +1148,7 @@ create table listp1 partition of listp for values in(1); create table listp2 partition of listp for values in(2) partition by list(b); create table listp2_10 partition of listp2 for values in (10); -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from listp where a = (select 2) and b <> 10; -- @@ -1216,7 +1216,7 @@ create table rangep_100_to_200 partition of rangep for values from (100) to (200 create index on rangep (a); -- Ensure run-time pruning works on the nested Merge Append -explain (analyze on, costs off, timing off, summary off) +explain (analyze on, costs off, timing off, summary off, buffers off) select * from rangep where b IN((select 1),(select 2)) order by a; reset enable_sort; drop table rangep; diff --git a/src/test/regress/sql/select.sql b/src/test/regress/sql/select.sql index 019f1e7673..1d1bf2b931 100644 --- a/src/test/regress/sql/select.sql +++ b/src/test/regress/sql/select.sql @@ -196,7 +196,7 @@ explain (costs off) select * from onek2 where unique2 = 11 and stringu1 = 'ATAAAA'; select * from onek2 where unique2 = 11 and stringu1 = 'ATAAAA'; -- actually run the query with an analyze to use the partial index -explain (costs off, analyze on, timing off, summary off) +explain (costs off, analyze on, timing off, summary off, buffers off) select * from onek2 where unique2 = 11 and stringu1 = 'ATAAAA'; explain (costs off) select unique2 from onek2 where unique2 = 11 and stringu1 = 'ATAAAA'; diff --git a/src/test/regress/sql/select_parallel.sql b/src/test/regress/sql/select_parallel.sql index 9ba1328fd2..868ff8a23b 100644 --- a/src/test/regress/sql/select_parallel.sql +++ b/src/test/regress/sql/select_parallel.sql @@ -223,7 +223,7 @@ select count(*) from bmscantest where a>1; -- test accumulation of stats for parallel nodes reset enable_seqscan; alter table tenk2 set (parallel_workers = 0); -explain (analyze, timing off, summary off, costs off) +explain (analyze, timing off, summary off, costs off, buffers off) select count(*) from tenk1, tenk2 where tenk1.hundred > 1 and tenk2.thousand=0; alter table tenk2 reset (parallel_workers); @@ -235,7 +235,7 @@ $$ declare ln text; begin for ln in - explain (analyze, timing off, summary off, costs off) + explain (analyze, timing off, summary off, costs off, buffers off) select * from (select ten from tenk1 where ten < 100 order by ten) ss right join (values (1),(2),(3)) v(x) on true @@ -443,7 +443,7 @@ explain (costs off) -- to increase the parallel query test coverage SAVEPOINT settings; SET LOCAL debug_parallel_query = 1; -EXPLAIN (analyze, timing off, summary off, costs off) SELECT * FROM tenk1; +EXPLAIN (analyze, timing off, summary off, costs off, buffers off) SELECT * FROM tenk1; ROLLBACK TO SAVEPOINT settings; -- provoke error in worker diff --git a/src/test/regress/sql/subselect.sql b/src/test/regress/sql/subselect.sql index af6e157aca..c53c7f724c 100644 --- a/src/test/regress/sql/subselect.sql +++ b/src/test/regress/sql/subselect.sql @@ -857,7 +857,7 @@ $$ declare ln text; begin for ln in - explain (analyze, summary off, timing off, costs off) + explain (analyze, summary off, timing off, costs off, buffers off) select * from (select pk,c2 from sq_limit order by c1,pk) as x limit 3 loop ln := regexp_replace(ln, 'Memory: \S*', 'Memory: xxx'); diff --git a/src/test/regress/sql/tidscan.sql b/src/test/regress/sql/tidscan.sql index 313e0fb9b6..1b82d5f1a5 100644 --- a/src/test/regress/sql/tidscan.sql +++ b/src/test/regress/sql/tidscan.sql @@ -68,17 +68,17 @@ DECLARE c CURSOR FOR SELECT ctid, * FROM tidscan; FETCH NEXT FROM c; -- skip one row FETCH NEXT FROM c; -- perform update -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *; FETCH NEXT FROM c; -- perform update -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *; SELECT * FROM tidscan; -- position cursor past any rows FETCH NEXT FROM c; -- should error out -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *; ROLLBACK; ^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Proposals for EXPLAIN: rename ANALYZE to EXECUTE and extend VERBOSE @ 2024-11-06 16:57 Michael Christofides <[email protected]> parent: David Rowley <[email protected]> 0 siblings, 1 reply; 20+ messages in thread From: Michael Christofides @ 2024-11-06 16:57 UTC (permalink / raw) To: David Rowley <[email protected]>; +Cc: David G. Johnston <[email protected]>; Nikolay Samokhvalov <[email protected]>; Tom Lane <[email protected]>; Robert Haas <[email protected]>; pgsql-hackers > > I'm not against analyze = on turning buffers on by default. However, I > think it would be quite painful to fix the tests if it were on without > analyze. > This would be amazing. I'm finding BUFFERS are especially helpful for giving developers a clearer idea of why their queries are slow (especially once converted to KB/MB/GB/TB). > The trouble is that EXPLAIN EXECUTE already means something. I completely agree with this. So -1 from me on the first suggestion. > Let's focus on item 2. +1 from me on item 2. I'd go further and have VERBOSE flip most other parameters to on (or to their default for non-booleans), unless specified otherwise. Specifically SUMMARY, BUFFERS, SETTINGS, WAL, SERIALIZE (TEXT), and MEMORY. Although I do think it would be best if BUFFERS and SERIALIZE were ON and TEXT by default respectively with ANALYZE, which may reduce/remove the need for them to be affected by VERBOSE. > If the VERBOSE option turns information about BUFFERS on > and off, and the BUFFERS option does the same thing, what happens if I > say EXPLAIN (VERBOSE ON, BUFFERS OFF)? Is it different if I say > EXPLAIN (BUFFERS OFF, VERBOSE ON)? I'd expect this to work like other parameters that have dependencies, for example both EXPLAIN (ANALYZE, SUMMARY OFF) and EXPLAIN (SUMMARY OFF, ANALYZE) exclude the SUMMARY, even though it is on by default with ANALYZE. So users could turn off anything they don't want, if needed. > I'm not very happy with the current situation. I agree that EXPLAIN has gotten a bit too complicated. I agree. In the past 6 versions, 5 new parameters have been added. SETTINGS in v12, WAL in v13, GENERIC_PLAN in v16, SERIALIZE in v17, and MEMORY in v17. It feels like we should have some easier way to get everything. Currently, we need to specify: EXPLAIN (ANALYZE, VERBOSE, BUFFERS, SETTINGS, WAL, SERIALIZE, MEMORY). > If you enable an option that adds an extra line of > output for every node and there are 100 nodes in the query plan, that > is a whole lot of additional clutter. This is a fair point, but I think it is worth it in the case of BUFFERS. The other parameter that adds a line per node is WAL, but the others don't add much clutter. Many people use tools these days to help read plans (I work on one, so have some biased opinions of course). Tools help folks calculate timings and spot bottlenecks , so once you're using a tool to read a plan, more information is often beneficial for minimal overhead. > This is not likely to fly for compatibility reasons. I'd be interested to hear more on this front too. One issue is that folks with auto_explain.log_verbose = on would get extra output in their logs, but I strongly suspect I'm missing some more important things. > the fresh SERIALIZE option was discussed in > https://www.postgresql.org/message-id/flat/ca0adb0e-fa4e-c37e-1cd7-91170b18cae1%40gmx.de > (2023-2024, 17) I noticed in this thread Tom was against SERIALIZE being on by default with ANALYZE, "because it would silently render EXPLAIN outputs from different versions quite non-comparable." I'm not sure I agree with the silently part, as the output from 17+ would include the serialization details, but again perhaps I'm missing something important. > Ready to do legwork here. Same here. — Michael Christofides Founder, pgMustard ^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Proposals for EXPLAIN: rename ANALYZE to EXECUTE and extend VERBOSE @ 2024-11-07 06:55 Peter Eisentraut <[email protected]> parent: Robert Haas <[email protected]> 1 sibling, 0 replies; 20+ messages in thread From: Peter Eisentraut @ 2024-11-07 06:55 UTC (permalink / raw) To: Robert Haas <[email protected]>; Nikolay Samokhvalov <[email protected]>; +Cc: pgsql-hackers On 05.11.24 19:19, Robert Haas wrote: >> 1) EXPLAIN ANALYZE Is sometimes very confusing (because there is ANALYZE). Let's rename it to EXPLAIN EXECUTE? > The trouble is that EXPLAIN EXECUTE already means something. > > robert.haas=# explain execute foo; > ERROR: prepared statement "foo" does not exist > > Granted, that would not make it impossible to make EXPLAIN (EXECUTE) a > synonym for EXPLAIN (ANALYZE), but IMHO it would be pretty confusing > if EXPLAIN EXECUTE and EXPLAIN (EXECUTE) did different things. At some point in the past, the idea of renaming EXPLAIN ANALYZE to PROFILE was thrown around. I still kind of like that idea. You'd have to keep the existing syntax around, of course. ^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Proposals for EXPLAIN: rename ANALYZE to EXECUTE and extend VERBOSE @ 2024-11-11 20:59 Guillaume Lelarge <[email protected]> parent: Michael Christofides <[email protected]> 0 siblings, 1 reply; 20+ messages in thread From: Guillaume Lelarge @ 2024-11-11 20:59 UTC (permalink / raw) To: Michael Christofides <[email protected]>; +Cc: David Rowley <[email protected]>; David G. Johnston <[email protected]>; Nikolay Samokhvalov <[email protected]>; Tom Lane <[email protected]>; Robert Haas <[email protected]>; pgsql-hackers Hi, Le mer. 6 nov. 2024 à 17:57, Michael Christofides <[email protected]> a écrit : > [...] > I agree. In the past 6 versions, 5 new parameters have been added. > SETTINGS in v12, WAL in v13, GENERIC_PLAN in v16, SERIALIZE in > v17, and MEMORY in v17. It feels like we should have some easier way > to get everything. Currently, we need to specify: EXPLAIN (ANALYZE, > VERBOSE, BUFFERS, SETTINGS, WAL, SERIALIZE, MEMORY). > > Agreed. Having an "EXPLAIN (ALL)" would be a great addition. I could tell a customer to do an "EXPLAIN (ALL)", rather than first asking the PostgreSQL release installed on the server and after that, giving the correct options for EXPLAIN. -- Guillaume. ^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Proposals for EXPLAIN: rename ANALYZE to EXECUTE and extend VERBOSE @ 2024-11-12 15:21 Robert Haas <[email protected]> parent: Guillaume Lelarge <[email protected]> 0 siblings, 1 reply; 20+ messages in thread From: Robert Haas @ 2024-11-12 15:21 UTC (permalink / raw) To: Guillaume Lelarge <[email protected]>; +Cc: Michael Christofides <[email protected]>; David Rowley <[email protected]>; David G. Johnston <[email protected]>; Nikolay Samokhvalov <[email protected]>; Tom Lane <[email protected]>; pgsql-hackers On Mon, Nov 11, 2024 at 3:59 PM Guillaume Lelarge <[email protected]> wrote: > Agreed. Having an "EXPLAIN (ALL)" would be a great addition. I could tell a customer to do an "EXPLAIN (ALL)", rather than first asking the PostgreSQL release installed on the server and after that, giving the correct options for EXPLAIN. I realize that you're probably going to hate my guts -- or hate them even more than you do already -- but I doubt that a proposal to add EXPLAIN (ALL) will go anywhere. The definitional problem is that it is not clear what to do with non-Boolean valued options, such as SERIALIZE. People who think that we were wrong not to make SERIALIZE TEXT the default in v17 will argue that EXPLAIN (ALL) should turn it on; after all, the backward-compatibility argument carries no water in that case. But people who do not like the behavior of SERIALIZE TEXT will not be happy about that. They might directly make that argument, or they might instead make the argument that ALL should do nothing about a non-Boolean valued option. But that position is really quite difficult to justify. Let's suppose that the current BUFFERS option, which is Boolean, got replaced with BUFFERS { detailed | on | off }. Well, then, by the principle that ALL only affects Boolean-valued options, it's no longer included in EXPLAIN (ALL). Nobody will be happy with that. Practically speaking, I think it will be very difficult to get agreement on what EXPLAIN (ALL) should do, and I think it is unlikely that anything will get committed no matter how much time we spend arguing about it. But I think we would get most of the same benefit from just doing what David Rowley proposed and turning on EXPLAIN (BUFFERS) by default. I'd suggest that we decide that, without ANALYZE, the option would not do anything; that is already how TIMING works. So this would be a very small patch and would probably get a lot of support from a lot of people. It also wouldn't require users to change their habits or learn any new syntax -- they could just keep typing EXPLAIN ANALYZE or EXPLAIN ANALYZE VERBOSE and all would be well. And the same principle could be applied to other EXPLAIN options if there is sufficient consensus. We could default to WAL ON, SERIALIZE TEXT, and MEMORY ON, if we wanted to do that. However, the more we try to change at once, the less likely it is that anything will happen at all. For example, I personally believe that EXPLAIN (MEMORY) should be ripped out of the server as both badly-named and mostly useless, so I'm not going to vote in favor of turning it on by default; and I wouldn't vote for enabling WAL by default because I have no experience with it to suggest that it's routinely valuable and thus worth the overhead. I would vote for SERIALIZE TEXT because I've seen that cause gross distortion of EXPLAIN ANALYZE results on many occasions. But the point is that other people will vote differently, so tying all the proposals together just increases the chances of agreeing on nothing at all. So to recap: everyone is free to propose whatever they like, and I am not in charge here, but if you want to get something committed, the proposal which I think has the highest chance of success is: propose to make BUFFERS ON the default (but a noop without ANALYZE, similar to how TIMING already works). -- Robert Haas EDB: http://www.enterprisedb.com ^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Proposals for EXPLAIN: rename ANALYZE to EXECUTE and extend VERBOSE @ 2024-11-12 15:35 Guillaume Lelarge <[email protected]> parent: Robert Haas <[email protected]> 0 siblings, 1 reply; 20+ messages in thread From: Guillaume Lelarge @ 2024-11-12 15:35 UTC (permalink / raw) To: Robert Haas <[email protected]>; +Cc: Michael Christofides <[email protected]>; David Rowley <[email protected]>; David G. Johnston <[email protected]>; Nikolay Samokhvalov <[email protected]>; Tom Lane <[email protected]>; pgsql-hackers Le mar. 12 nov. 2024 à 16:21, Robert Haas <[email protected]> a écrit : > On Mon, Nov 11, 2024 at 3:59 PM Guillaume Lelarge > <[email protected]> wrote: > > Agreed. Having an "EXPLAIN (ALL)" would be a great addition. I could > tell a customer to do an "EXPLAIN (ALL)", rather than first asking the > PostgreSQL release installed on the server and after that, giving the > correct options for EXPLAIN. > > I realize that you're probably going to hate my guts -- or hate them > even more than you do already -- but I doubt that a proposal to add > EXPLAIN (ALL) will go anywhere. I don't hate your guts :) and... > The definitional problem is that it is > not clear what to do with non-Boolean valued options, such as > SERIALIZE. People who think that we were wrong not to make SERIALIZE > TEXT the default in v17 will argue that EXPLAIN (ALL) should turn it > on; after all, the backward-compatibility argument carries no water in > that case. But people who do not like the behavior of SERIALIZE TEXT > will not be happy about that. They might directly make that argument, > or they might instead make the argument that ALL should do nothing > about a non-Boolean valued option. But that position is really quite > difficult to justify. Let's suppose that the current BUFFERS option, > which is Boolean, got replaced with BUFFERS { detailed | on | off }. > Well, then, by the principle that ALL only affects Boolean-valued > options, it's no longer included in EXPLAIN (ALL). Nobody will be > happy with that. Practically speaking, I think it will be very > difficult to get agreement on what EXPLAIN (ALL) should do, and I > think it is unlikely that anything will get committed no matter how > much time we spend arguing about it. > > ... I kinda agree with you. It would have been nice to have an "EXPLAIN (ALL)" but I completely understand the issue. > But I think we would get most of the same benefit from just doing what > David Rowley proposed and turning on EXPLAIN (BUFFERS) by default. I'd > suggest that we decide that, without ANALYZE, the option would not do > anything; that is already how TIMING works. So this would be a very > small patch and would probably get a lot of support from a lot of > people. It also wouldn't require users to change their habits or learn > any new syntax -- they could just keep typing EXPLAIN ANALYZE or > EXPLAIN ANALYZE VERBOSE and all would be well. > > That would be a nice enhancement. > And the same principle could be applied to other EXPLAIN options if > there is sufficient consensus. We could default to WAL ON, SERIALIZE > TEXT, and MEMORY ON, if we wanted to do that. However, the more we try > to change at once, the less likely it is that anything will happen at > all. For example, I personally believe that EXPLAIN (MEMORY) should be > ripped out of the server as both badly-named and mostly useless, so > I'm not going to vote in favor of turning it on by default; and I > wouldn't vote for enabling WAL by default because I have no experience > with it to suggest that it's routinely valuable and thus worth the > overhead. I would vote for SERIALIZE TEXT because I've seen that cause > gross distortion of EXPLAIN ANALYZE results on many occasions. But the > point is that other people will vote differently, so tying all the > proposals together just increases the chances of agreeing on nothing > at all. > > Agreed. > So to recap: everyone is free to propose whatever they like, and I am > not in charge here, but if you want to get something committed, the > proposal which I think has the highest chance of success is: propose > to make BUFFERS ON the default (but a noop without ANALYZE, similar to > how TIMING already works). > > Sounds like a plan. Thanks. -- Guillaume. ^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Proposals for EXPLAIN: rename ANALYZE to EXECUTE and extend VERBOSE @ 2024-11-12 21:02 Guillaume Lelarge <[email protected]> parent: Guillaume Lelarge <[email protected]> 0 siblings, 1 reply; 20+ messages in thread From: Guillaume Lelarge @ 2024-11-12 21:02 UTC (permalink / raw) To: Robert Haas <[email protected]>; +Cc: Michael Christofides <[email protected]>; David Rowley <[email protected]>; David G. Johnston <[email protected]>; Nikolay Samokhvalov <[email protected]>; Tom Lane <[email protected]>; pgsql-hackers Le mar. 12 nov. 2024 à 16:35, Guillaume Lelarge <[email protected]> a écrit : > Le mar. 12 nov. 2024 à 16:21, Robert Haas <[email protected]> a > écrit : > >> On Mon, Nov 11, 2024 at 3:59 PM Guillaume Lelarge >> <[email protected]> wrote: >> > Agreed. Having an "EXPLAIN (ALL)" would be a great addition. I could >> tell a customer to do an "EXPLAIN (ALL)", rather than first asking the >> PostgreSQL release installed on the server and after that, giving the >> correct options for EXPLAIN. >> >> I realize that you're probably going to hate my guts -- or hate them >> even more than you do already -- but I doubt that a proposal to add >> EXPLAIN (ALL) will go anywhere. > > > I don't hate your guts :) and... > > >> The definitional problem is that it is >> not clear what to do with non-Boolean valued options, such as >> SERIALIZE. People who think that we were wrong not to make SERIALIZE >> TEXT the default in v17 will argue that EXPLAIN (ALL) should turn it >> on; after all, the backward-compatibility argument carries no water in >> that case. But people who do not like the behavior of SERIALIZE TEXT >> will not be happy about that. They might directly make that argument, >> or they might instead make the argument that ALL should do nothing >> about a non-Boolean valued option. But that position is really quite >> difficult to justify. Let's suppose that the current BUFFERS option, >> which is Boolean, got replaced with BUFFERS { detailed | on | off }. >> Well, then, by the principle that ALL only affects Boolean-valued >> options, it's no longer included in EXPLAIN (ALL). Nobody will be >> happy with that. Practically speaking, I think it will be very >> difficult to get agreement on what EXPLAIN (ALL) should do, and I >> think it is unlikely that anything will get committed no matter how >> much time we spend arguing about it. >> >> > ... I kinda agree with you. It would have been nice to have an "EXPLAIN > (ALL)" but I completely understand the issue. > > >> But I think we would get most of the same benefit from just doing what >> David Rowley proposed and turning on EXPLAIN (BUFFERS) by default. I'd >> suggest that we decide that, without ANALYZE, the option would not do >> anything; that is already how TIMING works. So this would be a very >> small patch and would probably get a lot of support from a lot of >> people. It also wouldn't require users to change their habits or learn >> any new syntax -- they could just keep typing EXPLAIN ANALYZE or >> EXPLAIN ANALYZE VERBOSE and all would be well. >> >> > That would be a nice enhancement. > > >> And the same principle could be applied to other EXPLAIN options if >> there is sufficient consensus. We could default to WAL ON, SERIALIZE >> TEXT, and MEMORY ON, if we wanted to do that. However, the more we try >> to change at once, the less likely it is that anything will happen at >> all. For example, I personally believe that EXPLAIN (MEMORY) should be >> ripped out of the server as both badly-named and mostly useless, so >> I'm not going to vote in favor of turning it on by default; and I >> wouldn't vote for enabling WAL by default because I have no experience >> with it to suggest that it's routinely valuable and thus worth the >> overhead. I would vote for SERIALIZE TEXT because I've seen that cause >> gross distortion of EXPLAIN ANALYZE results on many occasions. But the >> point is that other people will vote differently, so tying all the >> proposals together just increases the chances of agreeing on nothing >> at all. >> >> > Agreed. > > >> So to recap: everyone is free to propose whatever they like, and I am >> not in charge here, but if you want to get something committed, the >> proposal which I think has the highest chance of success is: propose >> to make BUFFERS ON the default (but a noop without ANALYZE, similar to >> how TIMING already works). >> >> > Sounds like a plan. > > Sure looks easy enough to do (though it still lacks doc and tests changes). See patch attached. -- Guillaume. Attachments: [text/x-patch] 0001-BUFFERS-is-ON-by-default-when-ANALYZE-is-ON.patch (1.5K, ../../CAECtzeWpusNrZADkQ01ZZL_fFtOWtwtnw_uixqy9hgK0qUCScQ@mail.gmail.com/3-0001-BUFFERS-is-ON-by-default-when-ANALYZE-is-ON.patch) download | inline diff: From 173a6103d8b4727ed626c4fbbdab5feeb5aa7280 Mon Sep 17 00:00:00 2001 From: Guillaume Lelarge <[email protected]> Date: Tue, 12 Nov 2024 21:59:14 +0100 Subject: [PATCH] BUFFERS is ON by default when ANALYZE is ON --- src/backend/commands/explain.c | 7 +++++++ 1 file changed, 7 insertions(+) diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c index 7c0fd63b2f..d6815e01d1 100644 --- a/src/backend/commands/explain.c +++ b/src/backend/commands/explain.c @@ -198,6 +198,7 @@ ExplainQuery(ParseState *pstate, ExplainStmt *stmt, List *rewritten; ListCell *lc; bool timing_set = false; + bool buffers_set = false; bool summary_set = false; /* Parse options list. */ @@ -212,7 +213,10 @@ ExplainQuery(ParseState *pstate, ExplainStmt *stmt, else if (strcmp(opt->defname, "costs") == 0) es->costs = defGetBoolean(opt); else if (strcmp(opt->defname, "buffers") == 0) + { + buffers_set = true; es->buffers = defGetBoolean(opt); + } else if (strcmp(opt->defname, "wal") == 0) es->wal = defGetBoolean(opt); else if (strcmp(opt->defname, "settings") == 0) @@ -292,6 +296,9 @@ ExplainQuery(ParseState *pstate, ExplainStmt *stmt, /* if the timing was not set explicitly, set default value */ es->timing = (timing_set) ? es->timing : es->analyze; + /* if the buffers was not set explicitly, set default value */ + es->buffers = (buffers_set) ? es->buffers : es->analyze; + /* check that timing is used with EXPLAIN ANALYZE */ if (es->timing && !es->analyze) ereport(ERROR, -- 2.47.0 ^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Proposals for EXPLAIN: rename ANALYZE to EXECUTE and extend VERBOSE @ 2024-11-12 21:21 Robert Haas <[email protected]> parent: Guillaume Lelarge <[email protected]> 0 siblings, 1 reply; 20+ messages in thread From: Robert Haas @ 2024-11-12 21:21 UTC (permalink / raw) To: Guillaume Lelarge <[email protected]>; +Cc: Michael Christofides <[email protected]>; David Rowley <[email protected]>; David G. Johnston <[email protected]>; Nikolay Samokhvalov <[email protected]>; Tom Lane <[email protected]>; pgsql-hackers On Tue, Nov 12, 2024 at 4:02 PM Guillaume Lelarge <[email protected]> wrote: > Sure looks easy enough to do (though it still lacks doc and tests changes). See patch attached. Yep, that's very small. I'm a bit wondering if it's too small, though. standard_ExplainOneQuery() seems to do some stuff with es->buffers even before it does planning, so if the idea is that this will be a noop without ANALYZE, maybe this doesn't implement that. Also, you should probably update the default value for auto_explain.log_buffers. In general, I would recommend "git grep 'es->buffers'" and look carefully at each place where it's mentioned and decide if anything needs to be changed. And then change the stuff that needs it, and include in your email an explanation of why the other things don't need to be changed, unless it's obvious. -- Robert Haas EDB: http://www.enterprisedb.com ^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Proposals for EXPLAIN: rename ANALYZE to EXECUTE and extend VERBOSE @ 2024-11-19 18:13 Guillaume Lelarge <[email protected]> parent: Robert Haas <[email protected]> 0 siblings, 1 reply; 20+ messages in thread From: Guillaume Lelarge @ 2024-11-19 18:13 UTC (permalink / raw) To: Robert Haas <[email protected]>; +Cc: Michael Christofides <[email protected]>; David Rowley <[email protected]>; David G. Johnston <[email protected]>; Nikolay Samokhvalov <[email protected]>; Tom Lane <[email protected]>; pgsql-hackers Hello, Le mar. 12 nov. 2024 à 22:21, Robert Haas <[email protected]> a écrit : > On Tue, Nov 12, 2024 at 4:02 PM Guillaume Lelarge > <[email protected]> wrote: > > Sure looks easy enough to do (though it still lacks doc and tests > changes). See patch attached. > > Yep, that's very small. I'm a bit wondering if it's too small, though. > standard_ExplainOneQuery() seems to do some stuff with es->buffers > even before it does planning, so if the idea is that this will be a > noop without ANALYZE, maybe this doesn't implement that. Also, you > should probably update the default value for auto_explain.log_buffers. > In general, I would recommend "git grep 'es->buffers'" and look > carefully at each place where it's mentioned and decide if anything > needs to be changed. And then change the stuff that needs it, and > include in your email an explanation of why the other things don't > need to be changed, unless it's obvious. > > It took me a while to get back to it. This new patch takes care of the auto_explain extension, tests, and docs. I did quite a lot of tests, and it now looks complete to me (though I may have missed something :) ). Regards. -- Guillaume. Attachments: [text/x-patch] v2-0001-Enable-BUFFERS-by-default-with-EXPLAIN-ANALYZE.patch (76.9K, ../../CAECtzeW=yo4H-G8rBheWwiET-e+PaBpOHHaL9Yu0JpYTd557_Q@mail.gmail.com/3-v2-0001-Enable-BUFFERS-by-default-with-EXPLAIN-ANALYZE.patch) download | inline diff: From 10ae17b1e043dba2aead0d6259dd66b9b73e8a65 Mon Sep 17 00:00:00 2001 From: Guillaume Lelarge <[email protected]> Date: Tue, 12 Nov 2024 21:59:14 +0100 Subject: [PATCH v2] Enable BUFFERS by default with EXPLAIN ANALYZE This automatically enables the BUFFER option when ANALYZE is used with EXPLAIN. The BUFFER option stays disabled if ANALYZE isn't used. The auto_explain extension gets the same treatment. --- contrib/auto_explain/auto_explain.c | 4 +- doc/src/sgml/auto-explain.sgml | 2 +- doc/src/sgml/perform.sgml | 42 ++++++--- doc/src/sgml/ref/explain.sgml | 5 +- src/backend/commands/explain.c | 7 ++ src/test/regress/expected/brin_multi.out | 18 ++-- src/test/regress/expected/explain.out | 36 ++++++- .../regress/expected/incremental_sort.out | 4 +- src/test/regress/expected/memoize.out | 2 +- src/test/regress/expected/merge.out | 2 +- src/test/regress/expected/partition_prune.out | 94 +++++++++---------- src/test/regress/expected/select.out | 2 +- src/test/regress/expected/select_into.out | 4 +- src/test/regress/expected/select_parallel.out | 6 +- src/test/regress/expected/subselect.out | 2 +- src/test/regress/expected/tidscan.out | 6 +- src/test/regress/sql/brin_multi.sql | 18 ++-- src/test/regress/sql/explain.sql | 7 +- src/test/regress/sql/incremental_sort.sql | 4 +- src/test/regress/sql/memoize.sql | 2 +- src/test/regress/sql/merge.sql | 2 +- src/test/regress/sql/partition_prune.sql | 94 +++++++++---------- src/test/regress/sql/select.sql | 2 +- src/test/regress/sql/select_into.sql | 4 +- src/test/regress/sql/select_parallel.sql | 6 +- src/test/regress/sql/subselect.sql | 2 +- src/test/regress/sql/tidscan.sql | 6 +- 27 files changed, 219 insertions(+), 164 deletions(-) diff --git a/contrib/auto_explain/auto_explain.c b/contrib/auto_explain/auto_explain.c index 623a674f99..484e009577 100644 --- a/contrib/auto_explain/auto_explain.c +++ b/contrib/auto_explain/auto_explain.c @@ -27,7 +27,7 @@ static int auto_explain_log_min_duration = -1; /* msec or -1 */ static int auto_explain_log_parameter_max_length = -1; /* bytes or -1 */ static bool auto_explain_log_analyze = false; static bool auto_explain_log_verbose = false; -static bool auto_explain_log_buffers = false; +static bool auto_explain_log_buffers = true; static bool auto_explain_log_wal = false; static bool auto_explain_log_triggers = false; static bool auto_explain_log_timing = true; @@ -152,7 +152,7 @@ _PG_init(void) "Log buffers usage.", NULL, &auto_explain_log_buffers, - false, + true, PGC_SUSET, 0, NULL, diff --git a/doc/src/sgml/auto-explain.sgml b/doc/src/sgml/auto-explain.sgml index 0c4656ee30..6623d36c99 100644 --- a/doc/src/sgml/auto-explain.sgml +++ b/doc/src/sgml/auto-explain.sgml @@ -122,7 +122,7 @@ LOAD 'auto_explain'; equivalent to the <literal>BUFFERS</literal> option of <command>EXPLAIN</command>. This parameter has no effect unless <varname>auto_explain.log_analyze</varname> is enabled. - This parameter is off by default. + This parameter is on by default. Only superusers can change this setting. </para> </listitem> diff --git a/doc/src/sgml/perform.sgml b/doc/src/sgml/perform.sgml index cd12b9ce48..b353cc77b6 100644 --- a/doc/src/sgml/perform.sgml +++ b/doc/src/sgml/perform.sgml @@ -722,13 +722,19 @@ WHERE t1.unique1 < 10 AND t1.unique2 = t2.unique2; QUERY PLAN -------------------------------------------------------------------&zwsp;-------------------------------------------------------------- Nested Loop (cost=4.65..118.50 rows=10 width=488) (actual time=0.017..0.051 rows=10 loops=1) + Buffers: shared hit=36 read=6 -> Bitmap Heap Scan on tenk1 t1 (cost=4.36..39.38 rows=10 width=244) (actual time=0.009..0.017 rows=10 loops=1) Recheck Cond: (unique1 < 10) Heap Blocks: exact=10 + Buffers: shared hit=12 -> Bitmap Index Scan on tenk1_unique1 (cost=0.00..4.36 rows=10 width=0) (actual time=0.004..0.004 rows=10 loops=1) Index Cond: (unique1 < 10) + Buffers: shared hit=2 -> Index Scan using tenk2_unique2 on tenk2 t2 (cost=0.29..7.90 rows=1 width=244) (actual time=0.003..0.003 rows=1 loops=10) Index Cond: (unique2 = t1.unique2) + Buffers: shared hit=24 read=6 + Planning: + Buffers: shared hit=15 Planning Time: 0.485 ms Execution Time: 0.073 ms </screen> @@ -769,16 +775,24 @@ WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2 ORDER BY t1.fivethous; Sort (cost=713.05..713.30 rows=100 width=488) (actual time=2.995..3.002 rows=100 loops=1) Sort Key: t1.fivethous Sort Method: quicksort Memory: 74kB + Buffers: shared hit=440 -> Hash Join (cost=226.23..709.73 rows=100 width=488) (actual time=0.515..2.920 rows=100 loops=1) Hash Cond: (t2.unique2 = t1.unique2) + Buffers: shared hit=437 -> Seq Scan on tenk2 t2 (cost=0.00..445.00 rows=10000 width=244) (actual time=0.026..1.790 rows=10000 loops=1) + Buffers: shared hit=345 -> Hash (cost=224.98..224.98 rows=100 width=244) (actual time=0.476..0.477 rows=100 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 35kB + Buffers: shared hit=92 -> Bitmap Heap Scan on tenk1 t1 (cost=5.06..224.98 rows=100 width=244) (actual time=0.030..0.450 rows=100 loops=1) Recheck Cond: (unique1 < 100) Heap Blocks: exact=90 + Buffers: shared hit=92 -> Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.04 rows=100 width=0) (actual time=0.013..0.013 rows=100 loops=1) Index Cond: (unique1 < 100) + Buffers: shared hit=2 + Planning: + Buffers: shared hit=12 Planning Time: 0.187 ms Execution Time: 3.036 ms </screen> @@ -803,6 +817,7 @@ EXPLAIN ANALYZE SELECT * FROM tenk1 WHERE ten < 7; Seq Scan on tenk1 (cost=0.00..470.00 rows=7000 width=244) (actual time=0.030..1.995 rows=7000 loops=1) Filter: (ten < 7) Rows Removed by Filter: 3000 + Buffers: shared hit=345 Planning Time: 0.102 ms Execution Time: 2.145 ms </screen> @@ -826,6 +841,7 @@ EXPLAIN ANALYZE SELECT * FROM polygon_tbl WHERE f1 @> polygon '(0.5,2.0)'; Seq Scan on polygon_tbl (cost=0.00..1.09 rows=1 width=85) (actual time=0.023..0.023 rows=0 loops=1) Filter: (f1 @> '((0.5,2))'::polygon) Rows Removed by Filter: 7 + Buffers: shared hit=1 Planning Time: 0.039 ms Execution Time: 0.033 ms </screen> @@ -845,6 +861,7 @@ EXPLAIN ANALYZE SELECT * FROM polygon_tbl WHERE f1 @> polygon '(0.5,2.0)'; Index Scan using gpolygonind on polygon_tbl (cost=0.13..8.15 rows=1 width=85) (actual time=0.074..0.074 rows=0 loops=1) Index Cond: (f1 @> '((0.5,2))'::polygon) Rows Removed by Index Recheck: 1 + Buffers: shared hit=3 Planning Time: 0.039 ms Execution Time: 0.098 ms </screen> @@ -857,34 +874,27 @@ EXPLAIN ANALYZE SELECT * FROM polygon_tbl WHERE f1 @> polygon '(0.5,2.0)'; </para> <para> - <command>EXPLAIN</command> has a <literal>BUFFERS</literal> option that can be used with - <literal>ANALYZE</literal> to get even more run time statistics: + <command>EXPLAIN</command> has a <literal>BUFFERS</literal> option which is + enabled by default when <literal>ANALYZE</literal> is used. The numbers provided + by <literal>BUFFERS</literal> help to identify which parts of the query are the + most I/O-intensive. You can turn it off: <screen> -EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM tenk1 WHERE unique1 < 100 AND unique2 > 9000; +EXPLAIN (ANALYZE, BUFFERS OFF) SELECT * FROM tenk1 WHERE unique1 < 100 AND unique2 > 9000; QUERY PLAN -------------------------------------------------------------------&zwsp;-------------------------------------------------------------- Bitmap Heap Scan on tenk1 (cost=25.07..60.11 rows=10 width=244) (actual time=0.105..0.114 rows=10 loops=1) Recheck Cond: ((unique1 < 100) AND (unique2 > 9000)) Heap Blocks: exact=10 - Buffers: shared hit=14 read=3 -> BitmapAnd (cost=25.07..25.07 rows=10 width=0) (actual time=0.100..0.101 rows=0 loops=1) - Buffers: shared hit=4 read=3 -> Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.04 rows=100 width=0) (actual time=0.027..0.027 rows=100 loops=1) Index Cond: (unique1 < 100) - Buffers: shared hit=2 -> Bitmap Index Scan on tenk1_unique2 (cost=0.00..19.78 rows=999 width=0) (actual time=0.070..0.070 rows=999 loops=1) Index Cond: (unique2 > 9000) - Buffers: shared hit=2 read=3 - Planning: - Buffers: shared hit=3 Planning Time: 0.162 ms Execution Time: 0.143 ms </screen> - - The numbers provided by <literal>BUFFERS</literal> help to identify which parts - of the query are the most I/O-intensive. </para> <para> @@ -906,8 +916,12 @@ EXPLAIN ANALYZE UPDATE tenk1 SET hundred = hundred + 1 WHERE unique1 < 100; -> Bitmap Heap Scan on tenk1 (cost=5.06..225.23 rows=100 width=10) (actual time=0.065..0.141 rows=100 loops=1) Recheck Cond: (unique1 < 100) Heap Blocks: exact=90 + Buffers: shared hit=92 -> Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.04 rows=100 width=0) (actual time=0.031..0.031 rows=100 loops=1) Index Cond: (unique1 < 100) + Buffers: shared hit=2 + Planning: + Buffers: shared hit=91 Planning Time: 0.151 ms Execution Time: 1.856 ms @@ -1040,10 +1054,14 @@ EXPLAIN ANALYZE SELECT * FROM tenk1 WHERE unique1 < 100 AND unique2 > 9000 QUERY PLAN -------------------------------------------------------------------&zwsp;------------------------------------------------------------ Limit (cost=0.29..14.33 rows=2 width=244) (actual time=0.051..0.071 rows=2 loops=1) + Buffers: shared hit=16 -> Index Scan using tenk1_unique2 on tenk1 (cost=0.29..70.50 rows=10 width=244) (actual time=0.051..0.070 rows=2 loops=1) Index Cond: (unique2 > 9000) Filter: (unique1 < 100) Rows Removed by Filter: 287 + Buffers: shared hit=16 + Planning: + Buffers: shared hit=49 Planning Time: 0.077 ms Execution Time: 0.086 ms </screen> diff --git a/doc/src/sgml/ref/explain.sgml b/doc/src/sgml/ref/explain.sgml index db9d3a8549..2a7e8ef916 100644 --- a/doc/src/sgml/ref/explain.sgml +++ b/doc/src/sgml/ref/explain.sgml @@ -201,8 +201,9 @@ ROLLBACK; query processing. The number of blocks shown for an upper-level node includes those used by all its child nodes. In text - format, only non-zero values are printed. This parameter defaults to - <literal>FALSE</literal>. + format, only non-zero values are printed. + It defaults to <literal>TRUE</literal> when <literal>ANALYZE</literal> is + also enabled. Otherwise, it defaults to <literal>FALSE</literal>. </para> </listitem> </varlistentry> diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c index 7c0fd63b2f..d6815e01d1 100644 --- a/src/backend/commands/explain.c +++ b/src/backend/commands/explain.c @@ -198,6 +198,7 @@ ExplainQuery(ParseState *pstate, ExplainStmt *stmt, List *rewritten; ListCell *lc; bool timing_set = false; + bool buffers_set = false; bool summary_set = false; /* Parse options list. */ @@ -212,7 +213,10 @@ ExplainQuery(ParseState *pstate, ExplainStmt *stmt, else if (strcmp(opt->defname, "costs") == 0) es->costs = defGetBoolean(opt); else if (strcmp(opt->defname, "buffers") == 0) + { + buffers_set = true; es->buffers = defGetBoolean(opt); + } else if (strcmp(opt->defname, "wal") == 0) es->wal = defGetBoolean(opt); else if (strcmp(opt->defname, "settings") == 0) @@ -292,6 +296,9 @@ ExplainQuery(ParseState *pstate, ExplainStmt *stmt, /* if the timing was not set explicitly, set default value */ es->timing = (timing_set) ? es->timing : es->analyze; + /* if the buffers was not set explicitly, set default value */ + es->buffers = (buffers_set) ? es->buffers : es->analyze; + /* check that timing is used with EXPLAIN ANALYZE */ if (es->timing && !es->analyze) ereport(ERROR, diff --git a/src/test/regress/expected/brin_multi.out b/src/test/regress/expected/brin_multi.out index ae9ce9d8ec..f2d1465818 100644 --- a/src/test/regress/expected/brin_multi.out +++ b/src/test/regress/expected/brin_multi.out @@ -845,7 +845,7 @@ INSERT INTO brin_date_test SELECT '5874897-12-01'::date + i FROM generate_series CREATE INDEX ON brin_date_test USING brin (a date_minmax_multi_ops) WITH (pages_per_range=1); SET enable_seqscan = off; -- make sure the ranges were built correctly and 2023-01-01 eliminates all -EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF) +EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF, BUFFERS OFF) SELECT * FROM brin_date_test WHERE a = '2023-01-01'::date; QUERY PLAN ------------------------------------------------------------------------- @@ -864,7 +864,7 @@ INSERT INTO brin_timestamp_test SELECT i FROM generate_series('2000-01-01'::timestamp, '2000-02-09'::timestamp, '1 day'::interval) s(i); CREATE INDEX ON brin_timestamp_test USING brin (a timestamp_minmax_multi_ops) WITH (pages_per_range=1); SET enable_seqscan = off; -EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF) +EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF, BUFFERS OFF) SELECT * FROM brin_timestamp_test WHERE a = '2023-01-01'::timestamp; QUERY PLAN ------------------------------------------------------------------------------ @@ -874,7 +874,7 @@ SELECT * FROM brin_timestamp_test WHERE a = '2023-01-01'::timestamp; Index Cond: (a = '2023-01-01 00:00:00'::timestamp without time zone) (4 rows) -EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF) +EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF, BUFFERS OFF) SELECT * FROM brin_timestamp_test WHERE a = '1900-01-01'::timestamp; QUERY PLAN ------------------------------------------------------------------------------ @@ -892,7 +892,7 @@ INSERT INTO brin_date_test VALUES ('-infinity'), ('infinity'); INSERT INTO brin_date_test SELECT '2000-01-01'::date + i FROM generate_series(1, 40) s(i); CREATE INDEX ON brin_date_test USING brin (a date_minmax_multi_ops) WITH (pages_per_range=1); SET enable_seqscan = off; -EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF) +EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF, BUFFERS OFF) SELECT * FROM brin_date_test WHERE a = '2023-01-01'::date; QUERY PLAN ------------------------------------------------------------------------- @@ -902,7 +902,7 @@ SELECT * FROM brin_date_test WHERE a = '2023-01-01'::date; Index Cond: (a = '2023-01-01'::date) (4 rows) -EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF) +EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF, BUFFERS OFF) SELECT * FROM brin_date_test WHERE a = '1900-01-01'::date; QUERY PLAN ------------------------------------------------------------------------- @@ -921,7 +921,7 @@ INSERT INTO brin_interval_test SELECT (i || ' years')::interval FROM generate_se INSERT INTO brin_interval_test SELECT (i || ' years')::interval FROM generate_series( 177999980, 178000000) s(i); CREATE INDEX ON brin_interval_test USING brin (a interval_minmax_multi_ops) WITH (pages_per_range=1); SET enable_seqscan = off; -EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF) +EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF, BUFFERS OFF) SELECT * FROM brin_interval_test WHERE a = '-30 years'::interval; QUERY PLAN ----------------------------------------------------------------------------- @@ -931,7 +931,7 @@ SELECT * FROM brin_interval_test WHERE a = '-30 years'::interval; Index Cond: (a = '@ 30 years ago'::interval) (4 rows) -EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF) +EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF, BUFFERS OFF) SELECT * FROM brin_interval_test WHERE a = '30 years'::interval; QUERY PLAN ----------------------------------------------------------------------------- @@ -949,7 +949,7 @@ INSERT INTO brin_interval_test VALUES ('-infinity'), ('infinity'); INSERT INTO brin_interval_test SELECT (i || ' days')::interval FROM generate_series(100, 140) s(i); CREATE INDEX ON brin_interval_test USING brin (a interval_minmax_multi_ops) WITH (pages_per_range=1); SET enable_seqscan = off; -EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF) +EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF, BUFFERS OFF) SELECT * FROM brin_interval_test WHERE a = '-30 years'::interval; QUERY PLAN ----------------------------------------------------------------------------- @@ -959,7 +959,7 @@ SELECT * FROM brin_interval_test WHERE a = '-30 years'::interval; Index Cond: (a = '@ 30 years ago'::interval) (4 rows) -EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF) +EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF, BUFFERS OFF) SELECT * FROM brin_interval_test WHERE a = '30 years'::interval; QUERY PLAN ----------------------------------------------------------------------------- diff --git a/src/test/regress/expected/explain.out b/src/test/regress/expected/explain.out index d2eef8097c..930b4c6f48 100644 --- a/src/test/regress/expected/explain.out +++ b/src/test/regress/expected/explain.out @@ -77,7 +77,7 @@ select explain_filter('explain (analyze, verbose) select * from int8_tbl i8'); Execution Time: N.N ms (4 rows) -select explain_filter('explain (analyze, buffers, format text) select * from int8_tbl i8'); +select explain_filter('explain (analyze, format text) select * from int8_tbl i8'); explain_filter ----------------------------------------------------------------------------------------------- Seq Scan on int8_tbl i8 (cost=N.N..N.N rows=N width=N) (actual time=N.N..N.N rows=N loops=N) @@ -85,7 +85,7 @@ select explain_filter('explain (analyze, buffers, format text) select * from int Execution Time: N.N ms (3 rows) -select explain_filter('explain (analyze, buffers, format xml) select * from int8_tbl i8'); +select explain_filter('explain (analyze, format xml) select * from int8_tbl i8'); explain_filter -------------------------------------------------------- <explain xmlns="http://www.postgresql.org/N/explain"> + @@ -136,7 +136,15 @@ select explain_filter('explain (analyze, buffers, format xml) select * from int8 </explain> (1 row) -select explain_filter('explain (analyze, serialize, buffers, format yaml) select * from int8_tbl i8'); +select explain_filter('explain (analyze, buffers off, format text) select * from int8_tbl i8'); + explain_filter +----------------------------------------------------------------------------------------------- + Seq Scan on int8_tbl i8 (cost=N.N..N.N rows=N width=N) (actual time=N.N..N.N rows=N loops=N) + Planning Time: N.N ms + Execution Time: N.N ms +(3 rows) + +select explain_filter('explain (analyze, serialize, format yaml) select * from int8_tbl i8'); explain_filter ------------------------------- - Plan: + @@ -400,9 +408,29 @@ select explain_filter('explain (memory, analyze, format json) select * from int8 "Actual Total Time": N.N, + "Actual Rows": N, + "Actual Loops": N, + - "Disabled": false + + "Disabled": false, + + "Shared Hit Blocks": N, + + "Shared Read Blocks": N, + + "Shared Dirtied Blocks": N, + + "Shared Written Blocks": N, + + "Local Hit Blocks": N, + + "Local Read Blocks": N, + + "Local Dirtied Blocks": N, + + "Local Written Blocks": N, + + "Temp Read Blocks": N, + + "Temp Written Blocks": N + }, + "Planning": { + + "Shared Hit Blocks": N, + + "Shared Read Blocks": N, + + "Shared Dirtied Blocks": N, + + "Shared Written Blocks": N, + + "Local Hit Blocks": N, + + "Local Read Blocks": N, + + "Local Dirtied Blocks": N, + + "Local Written Blocks": N, + + "Temp Read Blocks": N, + + "Temp Written Blocks": N, + "Memory Used": N, + "Memory Allocated": N + }, + diff --git a/src/test/regress/expected/incremental_sort.out b/src/test/regress/expected/incremental_sort.out index 2df7a5db12..d597575840 100644 --- a/src/test/regress/expected/incremental_sort.out +++ b/src/test/regress/expected/incremental_sort.out @@ -39,7 +39,7 @@ declare line text; begin for line in - execute 'explain (analyze, costs off, summary off, timing off) ' || query + execute 'explain (analyze, costs off, summary off, timing off, buffers off) ' || query loop out_line := regexp_replace(line, '\d+kB', 'NNkB', 'g'); return next; @@ -55,7 +55,7 @@ declare element jsonb; matching_nodes jsonb := '[]'::jsonb; begin - execute 'explain (analyze, costs off, summary off, timing off, format ''json'') ' || query into strict elements; + execute 'explain (analyze, costs off, summary off, timing off, buffers off, format ''json'') ' || query into strict elements; while jsonb_array_length(elements) > 0 loop element := elements->0; elements := elements - 0; diff --git a/src/test/regress/expected/memoize.out b/src/test/regress/expected/memoize.out index f6b8329cd6..5ecf971dad 100644 --- a/src/test/regress/expected/memoize.out +++ b/src/test/regress/expected/memoize.out @@ -10,7 +10,7 @@ declare ln text; begin for ln in - execute format('explain (analyze, costs off, summary off, timing off) %s', + execute format('explain (analyze, costs off, summary off, timing off, buffers off) %s', query) loop if hide_hitmiss = true then diff --git a/src/test/regress/expected/merge.out b/src/test/regress/expected/merge.out index 521d70a891..28d8551063 100644 --- a/src/test/regress/expected/merge.out +++ b/src/test/regress/expected/merge.out @@ -1621,7 +1621,7 @@ $$ DECLARE ln text; BEGIN FOR ln IN - EXECUTE 'explain (analyze, timing off, summary off, costs off) ' || + EXECUTE 'explain (analyze, timing off, summary off, costs off, buffers off) ' || query LOOP ln := regexp_replace(ln, '(Memory( Usage)?|Buckets|Batches): \S*', '\1: xxx', 'g'); diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.out index 7a03b4e360..c710f316d7 100644 --- a/src/test/regress/expected/partition_prune.out +++ b/src/test/regress/expected/partition_prune.out @@ -11,7 +11,7 @@ declare ln text; begin for ln in - execute format('explain (analyze, costs off, summary off, timing off) %s', + execute format('explain (analyze, costs off, summary off, timing off, buffers off) %s', query) loop ln := regexp_replace(ln, 'Maximum Storage: \d+', 'Maximum Storage: N'); @@ -2127,7 +2127,7 @@ create table ab_a3_b3 partition of ab_a3 for values in (3); set enable_indexonlyscan = off; prepare ab_q1 (int, int, int) as select * from ab where a between $1 and $2 and b <= $3; -explain (analyze, costs off, summary off, timing off) execute ab_q1 (2, 2, 3); +explain (analyze, costs off, summary off, timing off, buffers off) execute ab_q1 (2, 2, 3); QUERY PLAN --------------------------------------------------------- Append (actual rows=0 loops=1) @@ -2140,7 +2140,7 @@ explain (analyze, costs off, summary off, timing off) execute ab_q1 (2, 2, 3); Filter: ((a >= $1) AND (a <= $2) AND (b <= $3)) (8 rows) -explain (analyze, costs off, summary off, timing off) execute ab_q1 (1, 2, 3); +explain (analyze, costs off, summary off, timing off, buffers off) execute ab_q1 (1, 2, 3); QUERY PLAN --------------------------------------------------------- Append (actual rows=0 loops=1) @@ -2163,7 +2163,7 @@ deallocate ab_q1; -- Runtime pruning after optimizer pruning prepare ab_q1 (int, int) as select a from ab where a between $1 and $2 and b < 3; -explain (analyze, costs off, summary off, timing off) execute ab_q1 (2, 2); +explain (analyze, costs off, summary off, timing off, buffers off) execute ab_q1 (2, 2); QUERY PLAN --------------------------------------------------------- Append (actual rows=0 loops=1) @@ -2174,7 +2174,7 @@ explain (analyze, costs off, summary off, timing off) execute ab_q1 (2, 2); Filter: ((a >= $1) AND (a <= $2) AND (b < 3)) (6 rows) -explain (analyze, costs off, summary off, timing off) execute ab_q1 (2, 4); +explain (analyze, costs off, summary off, timing off, buffers off) execute ab_q1 (2, 4); QUERY PLAN --------------------------------------------------------- Append (actual rows=0 loops=1) @@ -2193,7 +2193,7 @@ explain (analyze, costs off, summary off, timing off) execute ab_q1 (2, 4); -- different levels of partitioning. prepare ab_q2 (int, int) as select a from ab where a between $1 and $2 and b < (select 3); -explain (analyze, costs off, summary off, timing off) execute ab_q2 (2, 2); +explain (analyze, costs off, summary off, timing off, buffers off) execute ab_q2 (2, 2); QUERY PLAN ----------------------------------------------------------------------- Append (actual rows=0 loops=1) @@ -2211,7 +2211,7 @@ explain (analyze, costs off, summary off, timing off) execute ab_q2 (2, 2); -- As above, but swap the PARAM_EXEC Param to the first partition level prepare ab_q3 (int, int) as select a from ab where b between $1 and $2 and a < (select 3); -explain (analyze, costs off, summary off, timing off) execute ab_q3 (2, 2); +explain (analyze, costs off, summary off, timing off, buffers off) execute ab_q3 (2, 2); QUERY PLAN ----------------------------------------------------------------------- Append (actual rows=0 loops=1) @@ -2273,7 +2273,7 @@ begin; -- Test run-time pruning using stable functions create function list_part_fn(int) returns int as $$ begin return $1; end;$$ language plpgsql stable; -- Ensure pruning works using a stable function containing no Vars -explain (analyze, costs off, summary off, timing off) select * from list_part where a = list_part_fn(1); +explain (analyze, costs off, summary off, timing off, buffers off) select * from list_part where a = list_part_fn(1); QUERY PLAN ------------------------------------------------------------------ Append (actual rows=1 loops=1) @@ -2283,7 +2283,7 @@ explain (analyze, costs off, summary off, timing off) select * from list_part wh (4 rows) -- Ensure pruning does not take place when the function has a Var parameter -explain (analyze, costs off, summary off, timing off) select * from list_part where a = list_part_fn(a); +explain (analyze, costs off, summary off, timing off, buffers off) select * from list_part where a = list_part_fn(a); QUERY PLAN ------------------------------------------------------------------ Append (actual rows=4 loops=1) @@ -2298,7 +2298,7 @@ explain (analyze, costs off, summary off, timing off) select * from list_part wh (9 rows) -- Ensure pruning does not take place when the expression contains a Var. -explain (analyze, costs off, summary off, timing off) select * from list_part where a = list_part_fn(1) + a; +explain (analyze, costs off, summary off, timing off, buffers off) select * from list_part where a = list_part_fn(1) + a; QUERY PLAN ------------------------------------------------------------------ Append (actual rows=0 loops=1) @@ -2334,7 +2334,7 @@ declare ln text; begin for ln in - execute format('explain (analyze, costs off, summary off, timing off) %s', + execute format('explain (analyze, costs off, summary off, timing off, buffers off) %s', $1) loop ln := regexp_replace(ln, 'Workers Launched: \d+', 'Workers Launched: N'); @@ -2641,7 +2641,7 @@ reset parallel_tuple_cost; reset min_parallel_table_scan_size; reset max_parallel_workers_per_gather; -- Test run-time partition pruning with an initplan -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from ab where a = (select max(a) from lprt_a) and b = (select max(a)-1 from lprt_a); QUERY PLAN ------------------------------------------------------------------------- @@ -2700,7 +2700,7 @@ select * from ab where a = (select max(a) from lprt_a) and b = (select max(a)-1 (52 rows) -- Test run-time partition pruning with UNION ALL parents -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from (select * from ab where a = 1 union all select * from ab) ab where b = (select 1); QUERY PLAN ------------------------------------------------------------------------------- @@ -2744,7 +2744,7 @@ select * from (select * from ab where a = 1 union all select * from ab) ab where (37 rows) -- A case containing a UNION ALL with a non-partitioned child. -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from (select * from ab where a = 1 union all (values(10,5)) union all select * from ab) ab where b = (select 1); QUERY PLAN ------------------------------------------------------------------------------- @@ -2803,7 +2803,7 @@ union all select tableoid::regclass,a,b from ab ) ab where a = $1 and b = (select -10); -- Ensure the xy_1 subplan is not pruned. -explain (analyze, costs off, summary off, timing off) execute ab_q6(1); +explain (analyze, costs off, summary off, timing off, buffers off) execute ab_q6(1); QUERY PLAN -------------------------------------------------------- Append (actual rows=0 loops=1) @@ -2952,7 +2952,7 @@ create index tprt6_idx on tprt_6 (col1); insert into tprt values (10), (20), (501), (502), (505), (1001), (4500); set enable_hashjoin = off; set enable_mergejoin = off; -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from tbl1 join tprt on tbl1.col1 > tprt.col1; QUERY PLAN -------------------------------------------------------------------------- @@ -2973,7 +2973,7 @@ select * from tbl1 join tprt on tbl1.col1 > tprt.col1; Index Cond: (col1 < tbl1.col1) (15 rows) -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from tbl1 join tprt on tbl1.col1 = tprt.col1; QUERY PLAN -------------------------------------------------------------------------- @@ -3018,7 +3018,7 @@ order by tbl1.col1, tprt.col1; -- Multiple partitions insert into tbl1 values (1001), (1010), (1011); -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from tbl1 inner join tprt on tbl1.col1 > tprt.col1; QUERY PLAN -------------------------------------------------------------------------- @@ -3039,7 +3039,7 @@ select * from tbl1 inner join tprt on tbl1.col1 > tprt.col1; Index Cond: (col1 < tbl1.col1) (15 rows) -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from tbl1 inner join tprt on tbl1.col1 = tprt.col1; QUERY PLAN -------------------------------------------------------------------------- @@ -3103,7 +3103,7 @@ order by tbl1.col1, tprt.col1; -- Last partition delete from tbl1; insert into tbl1 values (4400); -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from tbl1 join tprt on tbl1.col1 < tprt.col1; QUERY PLAN -------------------------------------------------------------------------- @@ -3135,7 +3135,7 @@ order by tbl1.col1, tprt.col1; -- No matching partition delete from tbl1; insert into tbl1 values (10000); -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from tbl1 join tprt on tbl1.col1 = tprt.col1; QUERY PLAN ------------------------------------------------------------------- @@ -3175,7 +3175,7 @@ alter table part_cab attach partition part_abc_p1 for values in(3); prepare part_abc_q1 (int, int, int) as select * from part_abc where a = $1 and b = $2 and c = $3; -- Single partition should be scanned. -explain (analyze, costs off, summary off, timing off) execute part_abc_q1 (1, 2, 3); +explain (analyze, costs off, summary off, timing off, buffers off) execute part_abc_q1 (1, 2, 3); QUERY PLAN ---------------------------------------------------------- Seq Scan on part_abc_p1 part_abc (actual rows=0 loops=1) @@ -3200,7 +3200,7 @@ select * from listp where b = 1; -- partitions before finally detecting the correct set of 2nd level partitions -- which match the given parameter. prepare q1 (int,int) as select * from listp where b in ($1,$2); -explain (analyze, costs off, summary off, timing off) execute q1 (1,1); +explain (analyze, costs off, summary off, timing off, buffers off) execute q1 (1,1); QUERY PLAN ------------------------------------------------------------- Append (actual rows=0 loops=1) @@ -3209,7 +3209,7 @@ explain (analyze, costs off, summary off, timing off) execute q1 (1,1); Filter: (b = ANY (ARRAY[$1, $2])) (4 rows) -explain (analyze, costs off, summary off, timing off) execute q1 (2,2); +explain (analyze, costs off, summary off, timing off, buffers off) execute q1 (2,2); QUERY PLAN ------------------------------------------------------------- Append (actual rows=0 loops=1) @@ -3219,7 +3219,7 @@ explain (analyze, costs off, summary off, timing off) execute q1 (2,2); (4 rows) -- Try with no matching partitions. -explain (analyze, costs off, summary off, timing off) execute q1 (0,0); +explain (analyze, costs off, summary off, timing off, buffers off) execute q1 (0,0); QUERY PLAN -------------------------------- Append (actual rows=0 loops=1) @@ -3230,7 +3230,7 @@ deallocate q1; -- Test more complex cases where a not-equal condition further eliminates partitions. prepare q1 (int,int,int,int) as select * from listp where b in($1,$2) and $3 <> b and $4 <> b; -- Both partitions allowed by IN clause, but one disallowed by <> clause -explain (analyze, costs off, summary off, timing off) execute q1 (1,2,2,0); +explain (analyze, costs off, summary off, timing off, buffers off) execute q1 (1,2,2,0); QUERY PLAN ------------------------------------------------------------------------- Append (actual rows=0 loops=1) @@ -3240,7 +3240,7 @@ explain (analyze, costs off, summary off, timing off) execute q1 (1,2,2,0); (4 rows) -- Both partitions allowed by IN clause, then both excluded again by <> clauses. -explain (analyze, costs off, summary off, timing off) execute q1 (1,2,2,1); +explain (analyze, costs off, summary off, timing off, buffers off) execute q1 (1,2,2,1); QUERY PLAN -------------------------------- Append (actual rows=0 loops=1) @@ -3248,7 +3248,7 @@ explain (analyze, costs off, summary off, timing off) execute q1 (1,2,2,1); (2 rows) -- Ensure Params that evaluate to NULL properly prune away all partitions -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from listp where a = (select null::int); QUERY PLAN ------------------------------------------------------ @@ -3273,7 +3273,7 @@ create table stable_qual_pruning2 partition of stable_qual_pruning create table stable_qual_pruning3 partition of stable_qual_pruning for values from ('3000-02-01') to ('3000-03-01'); -- comparison against a stable value requires run-time pruning -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from stable_qual_pruning where a < localtimestamp; QUERY PLAN -------------------------------------------------------------------------------------- @@ -3286,7 +3286,7 @@ select * from stable_qual_pruning where a < localtimestamp; (6 rows) -- timestamp < timestamptz comparison is only stable, not immutable -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from stable_qual_pruning where a < '2000-02-01'::timestamptz; QUERY PLAN -------------------------------------------------------------------------------------- @@ -3297,7 +3297,7 @@ select * from stable_qual_pruning where a < '2000-02-01'::timestamptz; (4 rows) -- check ScalarArrayOp cases -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from stable_qual_pruning where a = any(array['2010-02-01', '2020-01-01']::timestamp[]); QUERY PLAN @@ -3306,7 +3306,7 @@ select * from stable_qual_pruning One-Time Filter: false (2 rows) -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from stable_qual_pruning where a = any(array['2000-02-01', '2010-01-01']::timestamp[]); QUERY PLAN @@ -3315,7 +3315,7 @@ select * from stable_qual_pruning Filter: (a = ANY ('{"Tue Feb 01 00:00:00 2000","Fri Jan 01 00:00:00 2010"}'::timestamp without time zone[])) (2 rows) -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from stable_qual_pruning where a = any(array['2000-02-01', localtimestamp]::timestamp[]); QUERY PLAN @@ -3326,7 +3326,7 @@ select * from stable_qual_pruning Filter: (a = ANY (ARRAY['Tue Feb 01 00:00:00 2000'::timestamp without time zone, LOCALTIMESTAMP])) (4 rows) -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from stable_qual_pruning where a = any(array['2010-02-01', '2020-01-01']::timestamptz[]); QUERY PLAN @@ -3335,7 +3335,7 @@ select * from stable_qual_pruning Subplans Removed: 3 (2 rows) -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from stable_qual_pruning where a = any(array['2000-02-01', '2010-01-01']::timestamptz[]); QUERY PLAN @@ -3346,7 +3346,7 @@ select * from stable_qual_pruning Filter: (a = ANY ('{"Tue Feb 01 00:00:00 2000 PST","Fri Jan 01 00:00:00 2010 PST"}'::timestamp with time zone[])) (4 rows) -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from stable_qual_pruning where a = any(null::timestamptz[]); QUERY PLAN @@ -3374,7 +3374,7 @@ create table mc3p1 partition of mc3p create table mc3p2 partition of mc3p for values from (2, minvalue, minvalue) to (3, maxvalue, maxvalue); insert into mc3p values (0, 1, 1), (1, 1, 1), (2, 1, 1); -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from mc3p where a < 3 and abs(b) = 1; QUERY PLAN -------------------------------------------------------- @@ -3394,7 +3394,7 @@ select * from mc3p where a < 3 and abs(b) = 1; -- prepare ps1 as select * from mc3p where a = $1 and abs(b) < (select 3); -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) execute ps1(1); QUERY PLAN ------------------------------------------------------------- @@ -3409,7 +3409,7 @@ execute ps1(1); deallocate ps1; prepare ps2 as select * from mc3p where a <= $1 and abs(b) < (select 3); -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) execute ps2(1); QUERY PLAN -------------------------------------------------------------- @@ -3431,7 +3431,7 @@ insert into boolvalues values('t'),('f'); create table boolp (a bool) partition by list (a); create table boolp_t partition of boolp for values in('t'); create table boolp_f partition of boolp for values in('f'); -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from boolp where a = (select value from boolvalues where value); QUERY PLAN ----------------------------------------------------------- @@ -3446,7 +3446,7 @@ select * from boolp where a = (select value from boolvalues where value); Filter: (a = (InitPlan 1).col1) (9 rows) -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from boolp where a = (select value from boolvalues where not value); QUERY PLAN ----------------------------------------------------------- @@ -3475,7 +3475,7 @@ insert into ma_test select x,x from generate_series(0,29) t(x); create index on ma_test (b); analyze ma_test; prepare mt_q1 (int) as select a from ma_test where a >= $1 and a % 10 = 5 order by b; -explain (analyze, costs off, summary off, timing off) execute mt_q1(15); +explain (analyze, costs off, summary off, timing off, buffers off) execute mt_q1(15); QUERY PLAN ----------------------------------------------------------------------------------------- Merge Append (actual rows=2 loops=1) @@ -3496,7 +3496,7 @@ execute mt_q1(15); 25 (2 rows) -explain (analyze, costs off, summary off, timing off) execute mt_q1(25); +explain (analyze, costs off, summary off, timing off, buffers off) execute mt_q1(25); QUERY PLAN ----------------------------------------------------------------------------------------- Merge Append (actual rows=1 loops=1) @@ -3514,7 +3514,7 @@ execute mt_q1(25); (1 row) -- Ensure MergeAppend behaves correctly when no subplans match -explain (analyze, costs off, summary off, timing off) execute mt_q1(35); +explain (analyze, costs off, summary off, timing off, buffers off) execute mt_q1(35); QUERY PLAN -------------------------------------- Merge Append (actual rows=0 loops=1) @@ -3542,7 +3542,7 @@ explain (analyze, verbose, costs off, summary off, timing off) execute mt_q2 (35 deallocate mt_q2; -- ensure initplan params properly prune partitions -explain (analyze, costs off, summary off, timing off) select * from ma_test where a >= (select min(b) from ma_test_p2) order by b; +explain (analyze, costs off, summary off, timing off, buffers off) select * from ma_test where a >= (select min(b) from ma_test_p2) order by b; QUERY PLAN ----------------------------------------------------------------------------------------------- Merge Append (actual rows=20 loops=1) @@ -3992,7 +3992,7 @@ create table listp (a int, b int) partition by list (a); create table listp1 partition of listp for values in(1); create table listp2 partition of listp for values in(2) partition by list(b); create table listp2_10 partition of listp2 for values in (10); -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from listp where a = (select 2) and b <> 10; QUERY PLAN --------------------------------------------------- @@ -4117,7 +4117,7 @@ create table rangep_0_to_100_3 partition of rangep_0_to_100 for values in(3); create table rangep_100_to_200 partition of rangep for values from (100) to (200); create index on rangep (a); -- Ensure run-time pruning works on the nested Merge Append -explain (analyze on, costs off, timing off, summary off) +explain (analyze on, costs off, timing off, summary off, buffers off) select * from rangep where b IN((select 1),(select 2)) order by a; QUERY PLAN ------------------------------------------------------------------------------------------------------------ diff --git a/src/test/regress/expected/select.out b/src/test/regress/expected/select.out index 33a6dceb0e..88911ca2b9 100644 --- a/src/test/regress/expected/select.out +++ b/src/test/regress/expected/select.out @@ -757,7 +757,7 @@ select * from onek2 where unique2 = 11 and stringu1 = 'ATAAAA'; (1 row) -- actually run the query with an analyze to use the partial index -explain (costs off, analyze on, timing off, summary off) +explain (costs off, analyze on, timing off, summary off, buffers off) select * from onek2 where unique2 = 11 and stringu1 = 'ATAAAA'; QUERY PLAN ----------------------------------------------------------------- diff --git a/src/test/regress/expected/select_into.out b/src/test/regress/expected/select_into.out index b79fe9a1c0..d6578d992c 100644 --- a/src/test/regress/expected/select_into.out +++ b/src/test/regress/expected/select_into.out @@ -25,7 +25,7 @@ CREATE TABLE selinto_schema.tbl_withdata1 (a) AS SELECT generate_series(1,3) WITH DATA; INSERT INTO selinto_schema.tbl_withdata1 VALUES (4); ERROR: permission denied for table tbl_withdata1 -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) CREATE TABLE selinto_schema.tbl_withdata2 (a) AS SELECT generate_series(1,3) WITH DATA; QUERY PLAN @@ -62,7 +62,7 @@ EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) -- EXECUTE and WITH NO DATA, passes. CREATE TABLE selinto_schema.tbl_nodata3 (a) AS EXECUTE data_sel WITH NO DATA; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) CREATE TABLE selinto_schema.tbl_nodata4 (a) AS EXECUTE data_sel WITH NO DATA; QUERY PLAN diff --git a/src/test/regress/expected/select_parallel.out b/src/test/regress/expected/select_parallel.out index 8c31f6460d..a809036453 100644 --- a/src/test/regress/expected/select_parallel.out +++ b/src/test/regress/expected/select_parallel.out @@ -580,7 +580,7 @@ select count(*) from bmscantest where a>1; -- test accumulation of stats for parallel nodes reset enable_seqscan; alter table tenk2 set (parallel_workers = 0); -explain (analyze, timing off, summary off, costs off) +explain (analyze, timing off, summary off, costs off, buffers off) select count(*) from tenk1, tenk2 where tenk1.hundred > 1 and tenk2.thousand=0; QUERY PLAN @@ -606,7 +606,7 @@ $$ declare ln text; begin for ln in - explain (analyze, timing off, summary off, costs off) + explain (analyze, timing off, summary off, costs off, buffers off) select * from (select ten from tenk1 where ten < 100 order by ten) ss right join (values (1),(2),(3)) v(x) on true @@ -1169,7 +1169,7 @@ explain (costs off) -- to increase the parallel query test coverage SAVEPOINT settings; SET LOCAL debug_parallel_query = 1; -EXPLAIN (analyze, timing off, summary off, costs off) SELECT * FROM tenk1; +EXPLAIN (analyze, timing off, summary off, costs off, buffers off) SELECT * FROM tenk1; QUERY PLAN ------------------------------------------------------------- Gather (actual rows=10000 loops=1) diff --git a/src/test/regress/expected/subselect.out b/src/test/regress/expected/subselect.out index 2d35de3fad..e04cea3d36 100644 --- a/src/test/regress/expected/subselect.out +++ b/src/test/regress/expected/subselect.out @@ -1675,7 +1675,7 @@ $$ declare ln text; begin for ln in - explain (analyze, summary off, timing off, costs off) + explain (analyze, summary off, timing off, costs off, buffers off) select * from (select pk,c2 from sq_limit order by c1,pk) as x limit 3 loop ln := regexp_replace(ln, 'Memory: \S*', 'Memory: xxx'); diff --git a/src/test/regress/expected/tidscan.out b/src/test/regress/expected/tidscan.out index f133b5a4ac..f6ebdf0601 100644 --- a/src/test/regress/expected/tidscan.out +++ b/src/test/regress/expected/tidscan.out @@ -189,7 +189,7 @@ FETCH NEXT FROM c; (1 row) -- perform update -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *; QUERY PLAN --------------------------------------------------- @@ -205,7 +205,7 @@ FETCH NEXT FROM c; (1 row) -- perform update -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *; QUERY PLAN --------------------------------------------------- @@ -229,7 +229,7 @@ FETCH NEXT FROM c; (0 rows) -- should error out -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *; ERROR: cursor "c" is not positioned on a row ROLLBACK; diff --git a/src/test/regress/sql/brin_multi.sql b/src/test/regress/sql/brin_multi.sql index 55349b4e1f..dda9fb73e0 100644 --- a/src/test/regress/sql/brin_multi.sql +++ b/src/test/regress/sql/brin_multi.sql @@ -619,7 +619,7 @@ CREATE INDEX ON brin_date_test USING brin (a date_minmax_multi_ops) WITH (pages_ SET enable_seqscan = off; -- make sure the ranges were built correctly and 2023-01-01 eliminates all -EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF) +EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF, BUFFERS OFF) SELECT * FROM brin_date_test WHERE a = '2023-01-01'::date; DROP TABLE brin_date_test; @@ -636,10 +636,10 @@ CREATE INDEX ON brin_timestamp_test USING brin (a timestamp_minmax_multi_ops) WI SET enable_seqscan = off; -EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF) +EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF, BUFFERS OFF) SELECT * FROM brin_timestamp_test WHERE a = '2023-01-01'::timestamp; -EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF) +EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF, BUFFERS OFF) SELECT * FROM brin_timestamp_test WHERE a = '1900-01-01'::timestamp; DROP TABLE brin_timestamp_test; @@ -655,10 +655,10 @@ CREATE INDEX ON brin_date_test USING brin (a date_minmax_multi_ops) WITH (pages_ SET enable_seqscan = off; -EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF) +EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF, BUFFERS OFF) SELECT * FROM brin_date_test WHERE a = '2023-01-01'::date; -EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF) +EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF, BUFFERS OFF) SELECT * FROM brin_date_test WHERE a = '1900-01-01'::date; DROP TABLE brin_date_test; @@ -676,10 +676,10 @@ CREATE INDEX ON brin_interval_test USING brin (a interval_minmax_multi_ops) WITH SET enable_seqscan = off; -EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF) +EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF, BUFFERS OFF) SELECT * FROM brin_interval_test WHERE a = '-30 years'::interval; -EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF) +EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF, BUFFERS OFF) SELECT * FROM brin_interval_test WHERE a = '30 years'::interval; DROP TABLE brin_interval_test; @@ -695,10 +695,10 @@ CREATE INDEX ON brin_interval_test USING brin (a interval_minmax_multi_ops) WITH SET enable_seqscan = off; -EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF) +EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF, BUFFERS OFF) SELECT * FROM brin_interval_test WHERE a = '-30 years'::interval; -EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF) +EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF, BUFFERS OFF) SELECT * FROM brin_interval_test WHERE a = '30 years'::interval; DROP TABLE brin_interval_test; diff --git a/src/test/regress/sql/explain.sql b/src/test/regress/sql/explain.sql index 3ca285a1d7..3f41396886 100644 --- a/src/test/regress/sql/explain.sql +++ b/src/test/regress/sql/explain.sql @@ -64,9 +64,10 @@ set track_io_timing = off; select explain_filter('explain select * from int8_tbl i8'); select explain_filter('explain (analyze) select * from int8_tbl i8'); select explain_filter('explain (analyze, verbose) select * from int8_tbl i8'); -select explain_filter('explain (analyze, buffers, format text) select * from int8_tbl i8'); -select explain_filter('explain (analyze, buffers, format xml) select * from int8_tbl i8'); -select explain_filter('explain (analyze, serialize, buffers, format yaml) select * from int8_tbl i8'); +select explain_filter('explain (analyze, format text) select * from int8_tbl i8'); +select explain_filter('explain (analyze, format xml) select * from int8_tbl i8'); +select explain_filter('explain (analyze, buffers off, format text) select * from int8_tbl i8'); +select explain_filter('explain (analyze, serialize, format yaml) select * from int8_tbl i8'); select explain_filter('explain (buffers, format text) select * from int8_tbl i8'); select explain_filter('explain (buffers, format json) select * from int8_tbl i8'); diff --git a/src/test/regress/sql/incremental_sort.sql b/src/test/regress/sql/incremental_sort.sql index 98b20e17e1..f1f8fae565 100644 --- a/src/test/regress/sql/incremental_sort.sql +++ b/src/test/regress/sql/incremental_sort.sql @@ -21,7 +21,7 @@ declare line text; begin for line in - execute 'explain (analyze, costs off, summary off, timing off) ' || query + execute 'explain (analyze, costs off, summary off, timing off, buffers off) ' || query loop out_line := regexp_replace(line, '\d+kB', 'NNkB', 'g'); return next; @@ -38,7 +38,7 @@ declare element jsonb; matching_nodes jsonb := '[]'::jsonb; begin - execute 'explain (analyze, costs off, summary off, timing off, format ''json'') ' || query into strict elements; + execute 'explain (analyze, costs off, summary off, timing off, buffers off, format ''json'') ' || query into strict elements; while jsonb_array_length(elements) > 0 loop element := elements->0; elements := elements - 0; diff --git a/src/test/regress/sql/memoize.sql b/src/test/regress/sql/memoize.sql index 2eaeb1477a..d5aab4e566 100644 --- a/src/test/regress/sql/memoize.sql +++ b/src/test/regress/sql/memoize.sql @@ -11,7 +11,7 @@ declare ln text; begin for ln in - execute format('explain (analyze, costs off, summary off, timing off) %s', + execute format('explain (analyze, costs off, summary off, timing off, buffers off) %s', query) loop if hide_hitmiss = true then diff --git a/src/test/regress/sql/merge.sql b/src/test/regress/sql/merge.sql index 5ddcca84f8..54929a92fa 100644 --- a/src/test/regress/sql/merge.sql +++ b/src/test/regress/sql/merge.sql @@ -1072,7 +1072,7 @@ $$ DECLARE ln text; BEGIN FOR ln IN - EXECUTE 'explain (analyze, timing off, summary off, costs off) ' || + EXECUTE 'explain (analyze, timing off, summary off, costs off, buffers off) ' || query LOOP ln := regexp_replace(ln, '(Memory( Usage)?|Buckets|Batches): \S*', '\1: xxx', 'g'); diff --git a/src/test/regress/sql/partition_prune.sql b/src/test/regress/sql/partition_prune.sql index 442428d937..8cd4a21fdc 100644 --- a/src/test/regress/sql/partition_prune.sql +++ b/src/test/regress/sql/partition_prune.sql @@ -12,7 +12,7 @@ declare ln text; begin for ln in - execute format('explain (analyze, costs off, summary off, timing off) %s', + execute format('explain (analyze, costs off, summary off, timing off, buffers off) %s', query) loop ln := regexp_replace(ln, 'Maximum Storage: \d+', 'Maximum Storage: N'); @@ -465,8 +465,8 @@ set enable_indexonlyscan = off; prepare ab_q1 (int, int, int) as select * from ab where a between $1 and $2 and b <= $3; -explain (analyze, costs off, summary off, timing off) execute ab_q1 (2, 2, 3); -explain (analyze, costs off, summary off, timing off) execute ab_q1 (1, 2, 3); +explain (analyze, costs off, summary off, timing off, buffers off) execute ab_q1 (2, 2, 3); +explain (analyze, costs off, summary off, timing off, buffers off) execute ab_q1 (1, 2, 3); deallocate ab_q1; @@ -474,21 +474,21 @@ deallocate ab_q1; prepare ab_q1 (int, int) as select a from ab where a between $1 and $2 and b < 3; -explain (analyze, costs off, summary off, timing off) execute ab_q1 (2, 2); -explain (analyze, costs off, summary off, timing off) execute ab_q1 (2, 4); +explain (analyze, costs off, summary off, timing off, buffers off) execute ab_q1 (2, 2); +explain (analyze, costs off, summary off, timing off, buffers off) execute ab_q1 (2, 4); -- Ensure a mix of PARAM_EXTERN and PARAM_EXEC Params work together at -- different levels of partitioning. prepare ab_q2 (int, int) as select a from ab where a between $1 and $2 and b < (select 3); -explain (analyze, costs off, summary off, timing off) execute ab_q2 (2, 2); +explain (analyze, costs off, summary off, timing off, buffers off) execute ab_q2 (2, 2); -- As above, but swap the PARAM_EXEC Param to the first partition level prepare ab_q3 (int, int) as select a from ab where b between $1 and $2 and a < (select 3); -explain (analyze, costs off, summary off, timing off) execute ab_q3 (2, 2); +explain (analyze, costs off, summary off, timing off, buffers off) execute ab_q3 (2, 2); -- -- Test runtime pruning with hash partitioned tables @@ -538,13 +538,13 @@ begin; create function list_part_fn(int) returns int as $$ begin return $1; end;$$ language plpgsql stable; -- Ensure pruning works using a stable function containing no Vars -explain (analyze, costs off, summary off, timing off) select * from list_part where a = list_part_fn(1); +explain (analyze, costs off, summary off, timing off, buffers off) select * from list_part where a = list_part_fn(1); -- Ensure pruning does not take place when the function has a Var parameter -explain (analyze, costs off, summary off, timing off) select * from list_part where a = list_part_fn(a); +explain (analyze, costs off, summary off, timing off, buffers off) select * from list_part where a = list_part_fn(a); -- Ensure pruning does not take place when the expression contains a Var. -explain (analyze, costs off, summary off, timing off) select * from list_part where a = list_part_fn(1) + a; +explain (analyze, costs off, summary off, timing off, buffers off) select * from list_part where a = list_part_fn(1) + a; rollback; @@ -567,7 +567,7 @@ declare ln text; begin for ln in - execute format('explain (analyze, costs off, summary off, timing off) %s', + execute format('explain (analyze, costs off, summary off, timing off, buffers off) %s', $1) loop ln := regexp_replace(ln, 'Workers Launched: \d+', 'Workers Launched: N'); @@ -650,15 +650,15 @@ reset min_parallel_table_scan_size; reset max_parallel_workers_per_gather; -- Test run-time partition pruning with an initplan -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from ab where a = (select max(a) from lprt_a) and b = (select max(a)-1 from lprt_a); -- Test run-time partition pruning with UNION ALL parents -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from (select * from ab where a = 1 union all select * from ab) ab where b = (select 1); -- A case containing a UNION ALL with a non-partitioned child. -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from (select * from ab where a = 1 union all (values(10,5)) union all select * from ab) ab where b = (select 1); -- Another UNION ALL test, but containing a mix of exec init and exec run-time pruning. @@ -678,7 +678,7 @@ union all ) ab where a = $1 and b = (select -10); -- Ensure the xy_1 subplan is not pruned. -explain (analyze, costs off, summary off, timing off) execute ab_q6(1); +explain (analyze, costs off, summary off, timing off, buffers off) execute ab_q6(1); -- Ensure we see just the xy_1 row. execute ab_q6(100); @@ -733,10 +733,10 @@ insert into tprt values (10), (20), (501), (502), (505), (1001), (4500); set enable_hashjoin = off; set enable_mergejoin = off; -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from tbl1 join tprt on tbl1.col1 > tprt.col1; -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from tbl1 join tprt on tbl1.col1 = tprt.col1; select tbl1.col1, tprt.col1 from tbl1 @@ -749,10 +749,10 @@ order by tbl1.col1, tprt.col1; -- Multiple partitions insert into tbl1 values (1001), (1010), (1011); -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from tbl1 inner join tprt on tbl1.col1 > tprt.col1; -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from tbl1 inner join tprt on tbl1.col1 = tprt.col1; select tbl1.col1, tprt.col1 from tbl1 @@ -766,7 +766,7 @@ order by tbl1.col1, tprt.col1; -- Last partition delete from tbl1; insert into tbl1 values (4400); -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from tbl1 join tprt on tbl1.col1 < tprt.col1; select tbl1.col1, tprt.col1 from tbl1 @@ -776,7 +776,7 @@ order by tbl1.col1, tprt.col1; -- No matching partition delete from tbl1; insert into tbl1 values (10000); -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from tbl1 join tprt on tbl1.col1 = tprt.col1; select tbl1.col1, tprt.col1 from tbl1 @@ -799,7 +799,7 @@ prepare part_abc_q1 (int, int, int) as select * from part_abc where a = $1 and b = $2 and c = $3; -- Single partition should be scanned. -explain (analyze, costs off, summary off, timing off) execute part_abc_q1 (1, 2, 3); +explain (analyze, costs off, summary off, timing off, buffers off) execute part_abc_q1 (1, 2, 3); deallocate part_abc_q1; @@ -819,12 +819,12 @@ select * from listp where b = 1; -- which match the given parameter. prepare q1 (int,int) as select * from listp where b in ($1,$2); -explain (analyze, costs off, summary off, timing off) execute q1 (1,1); +explain (analyze, costs off, summary off, timing off, buffers off) execute q1 (1,1); -explain (analyze, costs off, summary off, timing off) execute q1 (2,2); +explain (analyze, costs off, summary off, timing off, buffers off) execute q1 (2,2); -- Try with no matching partitions. -explain (analyze, costs off, summary off, timing off) execute q1 (0,0); +explain (analyze, costs off, summary off, timing off, buffers off) execute q1 (0,0); deallocate q1; @@ -832,13 +832,13 @@ deallocate q1; prepare q1 (int,int,int,int) as select * from listp where b in($1,$2) and $3 <> b and $4 <> b; -- Both partitions allowed by IN clause, but one disallowed by <> clause -explain (analyze, costs off, summary off, timing off) execute q1 (1,2,2,0); +explain (analyze, costs off, summary off, timing off, buffers off) execute q1 (1,2,2,0); -- Both partitions allowed by IN clause, then both excluded again by <> clauses. -explain (analyze, costs off, summary off, timing off) execute q1 (1,2,2,1); +explain (analyze, costs off, summary off, timing off, buffers off) execute q1 (1,2,2,1); -- Ensure Params that evaluate to NULL properly prune away all partitions -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from listp where a = (select null::int); drop table listp; @@ -855,30 +855,30 @@ create table stable_qual_pruning3 partition of stable_qual_pruning for values from ('3000-02-01') to ('3000-03-01'); -- comparison against a stable value requires run-time pruning -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from stable_qual_pruning where a < localtimestamp; -- timestamp < timestamptz comparison is only stable, not immutable -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from stable_qual_pruning where a < '2000-02-01'::timestamptz; -- check ScalarArrayOp cases -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from stable_qual_pruning where a = any(array['2010-02-01', '2020-01-01']::timestamp[]); -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from stable_qual_pruning where a = any(array['2000-02-01', '2010-01-01']::timestamp[]); -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from stable_qual_pruning where a = any(array['2000-02-01', localtimestamp]::timestamp[]); -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from stable_qual_pruning where a = any(array['2010-02-01', '2020-01-01']::timestamptz[]); -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from stable_qual_pruning where a = any(array['2000-02-01', '2010-01-01']::timestamptz[]); -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from stable_qual_pruning where a = any(null::timestamptz[]); @@ -898,7 +898,7 @@ create table mc3p2 partition of mc3p for values from (2, minvalue, minvalue) to (3, maxvalue, maxvalue); insert into mc3p values (0, 1, 1), (1, 1, 1), (2, 1, 1); -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from mc3p where a < 3 and abs(b) = 1; -- @@ -908,12 +908,12 @@ select * from mc3p where a < 3 and abs(b) = 1; -- prepare ps1 as select * from mc3p where a = $1 and abs(b) < (select 3); -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) execute ps1(1); deallocate ps1; prepare ps2 as select * from mc3p where a <= $1 and abs(b) < (select 3); -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) execute ps2(1); deallocate ps2; @@ -927,10 +927,10 @@ create table boolp (a bool) partition by list (a); create table boolp_t partition of boolp for values in('t'); create table boolp_f partition of boolp for values in('f'); -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from boolp where a = (select value from boolvalues where value); -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from boolp where a = (select value from boolvalues where not value); drop table boolp; @@ -950,12 +950,12 @@ create index on ma_test (b); analyze ma_test; prepare mt_q1 (int) as select a from ma_test where a >= $1 and a % 10 = 5 order by b; -explain (analyze, costs off, summary off, timing off) execute mt_q1(15); +explain (analyze, costs off, summary off, timing off, buffers off) execute mt_q1(15); execute mt_q1(15); -explain (analyze, costs off, summary off, timing off) execute mt_q1(25); +explain (analyze, costs off, summary off, timing off, buffers off) execute mt_q1(25); execute mt_q1(25); -- Ensure MergeAppend behaves correctly when no subplans match -explain (analyze, costs off, summary off, timing off) execute mt_q1(35); +explain (analyze, costs off, summary off, timing off, buffers off) execute mt_q1(35); execute mt_q1(35); deallocate mt_q1; @@ -968,7 +968,7 @@ explain (analyze, verbose, costs off, summary off, timing off) execute mt_q2 (35 deallocate mt_q2; -- ensure initplan params properly prune partitions -explain (analyze, costs off, summary off, timing off) select * from ma_test where a >= (select min(b) from ma_test_p2) order by b; +explain (analyze, costs off, summary off, timing off, buffers off) select * from ma_test where a >= (select min(b) from ma_test_p2) order by b; reset enable_seqscan; reset enable_sort; @@ -1148,7 +1148,7 @@ create table listp1 partition of listp for values in(1); create table listp2 partition of listp for values in(2) partition by list(b); create table listp2_10 partition of listp2 for values in (10); -explain (analyze, costs off, summary off, timing off) +explain (analyze, costs off, summary off, timing off, buffers off) select * from listp where a = (select 2) and b <> 10; -- @@ -1216,7 +1216,7 @@ create table rangep_100_to_200 partition of rangep for values from (100) to (200 create index on rangep (a); -- Ensure run-time pruning works on the nested Merge Append -explain (analyze on, costs off, timing off, summary off) +explain (analyze on, costs off, timing off, summary off, buffers off) select * from rangep where b IN((select 1),(select 2)) order by a; reset enable_sort; drop table rangep; diff --git a/src/test/regress/sql/select.sql b/src/test/regress/sql/select.sql index 019f1e7673..1d1bf2b931 100644 --- a/src/test/regress/sql/select.sql +++ b/src/test/regress/sql/select.sql @@ -196,7 +196,7 @@ explain (costs off) select * from onek2 where unique2 = 11 and stringu1 = 'ATAAAA'; select * from onek2 where unique2 = 11 and stringu1 = 'ATAAAA'; -- actually run the query with an analyze to use the partial index -explain (costs off, analyze on, timing off, summary off) +explain (costs off, analyze on, timing off, summary off, buffers off) select * from onek2 where unique2 = 11 and stringu1 = 'ATAAAA'; explain (costs off) select unique2 from onek2 where unique2 = 11 and stringu1 = 'ATAAAA'; diff --git a/src/test/regress/sql/select_into.sql b/src/test/regress/sql/select_into.sql index 689c448cc2..106cdde187 100644 --- a/src/test/regress/sql/select_into.sql +++ b/src/test/regress/sql/select_into.sql @@ -30,7 +30,7 @@ SET SESSION AUTHORIZATION regress_selinto_user; CREATE TABLE selinto_schema.tbl_withdata1 (a) AS SELECT generate_series(1,3) WITH DATA; INSERT INTO selinto_schema.tbl_withdata1 VALUES (4); -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) CREATE TABLE selinto_schema.tbl_withdata2 (a) AS SELECT generate_series(1,3) WITH DATA; -- WITH NO DATA, passes. @@ -49,7 +49,7 @@ EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) -- EXECUTE and WITH NO DATA, passes. CREATE TABLE selinto_schema.tbl_nodata3 (a) AS EXECUTE data_sel WITH NO DATA; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) CREATE TABLE selinto_schema.tbl_nodata4 (a) AS EXECUTE data_sel WITH NO DATA; RESET SESSION AUTHORIZATION; diff --git a/src/test/regress/sql/select_parallel.sql b/src/test/regress/sql/select_parallel.sql index 5b4a6e1088..71a75bc86e 100644 --- a/src/test/regress/sql/select_parallel.sql +++ b/src/test/regress/sql/select_parallel.sql @@ -230,7 +230,7 @@ select count(*) from bmscantest where a>1; -- test accumulation of stats for parallel nodes reset enable_seqscan; alter table tenk2 set (parallel_workers = 0); -explain (analyze, timing off, summary off, costs off) +explain (analyze, timing off, summary off, costs off, buffers off) select count(*) from tenk1, tenk2 where tenk1.hundred > 1 and tenk2.thousand=0; alter table tenk2 reset (parallel_workers); @@ -242,7 +242,7 @@ $$ declare ln text; begin for ln in - explain (analyze, timing off, summary off, costs off) + explain (analyze, timing off, summary off, costs off, buffers off) select * from (select ten from tenk1 where ten < 100 order by ten) ss right join (values (1),(2),(3)) v(x) on true @@ -450,7 +450,7 @@ explain (costs off) -- to increase the parallel query test coverage SAVEPOINT settings; SET LOCAL debug_parallel_query = 1; -EXPLAIN (analyze, timing off, summary off, costs off) SELECT * FROM tenk1; +EXPLAIN (analyze, timing off, summary off, costs off, buffers off) SELECT * FROM tenk1; ROLLBACK TO SAVEPOINT settings; -- provoke error in worker diff --git a/src/test/regress/sql/subselect.sql b/src/test/regress/sql/subselect.sql index af6e157aca..c53c7f724c 100644 --- a/src/test/regress/sql/subselect.sql +++ b/src/test/regress/sql/subselect.sql @@ -857,7 +857,7 @@ $$ declare ln text; begin for ln in - explain (analyze, summary off, timing off, costs off) + explain (analyze, summary off, timing off, costs off, buffers off) select * from (select pk,c2 from sq_limit order by c1,pk) as x limit 3 loop ln := regexp_replace(ln, 'Memory: \S*', 'Memory: xxx'); diff --git a/src/test/regress/sql/tidscan.sql b/src/test/regress/sql/tidscan.sql index 313e0fb9b6..1b82d5f1a5 100644 --- a/src/test/regress/sql/tidscan.sql +++ b/src/test/regress/sql/tidscan.sql @@ -68,17 +68,17 @@ DECLARE c CURSOR FOR SELECT ctid, * FROM tidscan; FETCH NEXT FROM c; -- skip one row FETCH NEXT FROM c; -- perform update -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *; FETCH NEXT FROM c; -- perform update -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *; SELECT * FROM tidscan; -- position cursor past any rows FETCH NEXT FROM c; -- should error out -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *; ROLLBACK; -- 2.47.0 ^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Proposals for EXPLAIN: rename ANALYZE to EXECUTE and extend VERBOSE @ 2024-11-20 00:12 Greg Sabino Mullane <[email protected]> parent: Guillaume Lelarge <[email protected]> 0 siblings, 1 reply; 20+ messages in thread From: Greg Sabino Mullane @ 2024-11-20 00:12 UTC (permalink / raw) To: Guillaume Lelarge <[email protected]>; +Cc: Robert Haas <[email protected]>; Michael Christofides <[email protected]>; David Rowley <[email protected]>; David G. Johnston <[email protected]>; Nikolay Samokhvalov <[email protected]>; Tom Lane <[email protected]>; pgsql-hackers I'm with Robert on this one. I'm a grudging +1 to buffers defaulting on, and a strong -1 to all the other proposals. Guillaume, the patch looks pretty good. I would like to see some of the example output have more than just "shared hit" and "read" though: let's throw some "dirtied" and "written" in there as well. It defaults to <literal>TRUE</literal> when <literal>ANALYZE</literal> is > also enabled. Otherwise, it defaults to <literal>FALSE</literal>. Is that second sentence really needed? Because "BUFFERS ON" will never be needed anymore (save as a no-op to allow the same explain queries to run cross-version), and BUFFERS OFF outside of analyze is meaningless. Cheers, Greg ^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Proposals for EXPLAIN: rename ANALYZE to EXECUTE and extend VERBOSE @ 2024-11-20 01:10 David Rowley <[email protected]> parent: Greg Sabino Mullane <[email protected]> 0 siblings, 1 reply; 20+ messages in thread From: David Rowley @ 2024-11-20 01:10 UTC (permalink / raw) To: Greg Sabino Mullane <[email protected]>; +Cc: Guillaume Lelarge <[email protected]>; Robert Haas <[email protected]>; Michael Christofides <[email protected]>; David G. Johnston <[email protected]>; Nikolay Samokhvalov <[email protected]>; Tom Lane <[email protected]>; pgsql-hackers On Wed, 20 Nov 2024 at 13:13, Greg Sabino Mullane <[email protected]> wrote: >> It defaults to <literal>TRUE</literal> when <literal>ANALYZE</literal> is also enabled. Otherwise, it defaults to <literal>FALSE</literal>. > > Is that second sentence really needed? Because "BUFFERS ON" will never be needed anymore (save as a no-op to allow the same explain queries to run cross-version), and BUFFERS OFF outside of analyze is meaningless. "BUFFERS ON" will be needed if the user wants to see the buffer usage in the planner when ANALYZE isn't specified. You'll see the planner access buffers when the caches are not fully populated and for get_actual_variable_range() work. Maybe the wording could just be based on the wording for the SUMMARY option, i.e. "Summary information is included by default when ANALYZE is used but otherwise is not included by default". David ^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Proposals for EXPLAIN: rename ANALYZE to EXECUTE and extend VERBOSE @ 2024-11-20 01:38 Greg Sabino Mullane <[email protected]> parent: David Rowley <[email protected]> 0 siblings, 0 replies; 20+ messages in thread From: Greg Sabino Mullane @ 2024-11-20 01:38 UTC (permalink / raw) To: David Rowley <[email protected]>; +Cc: Guillaume Lelarge <[email protected]>; Robert Haas <[email protected]>; Michael Christofides <[email protected]>; David G. Johnston <[email protected]>; Nikolay Samokhvalov <[email protected]>; Tom Lane <[email protected]>; pgsql-hackers > > Maybe the wording could just be based on the wording for the SUMMARY > option, i.e. "Summary information is included by default when ANALYZE > is used but otherwise is not included by default". > Yes, I like that. Avoids the whole TRUE/FALSE altogether. Thanks, Greg ^ permalink raw reply [nested|flat] 20+ messages in thread
end of thread, other threads:[~2024-11-20 01:38 UTC | newest] Thread overview: 20+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2024-03-04 04:52 [PATCH v16] Add CopyFromRoutine/CopyToRountine Sutou Kouhei <[email protected]> 2024-11-05 18:19 Re: Proposals for EXPLAIN: rename ANALYZE to EXECUTE and extend VERBOSE Robert Haas <[email protected]> 2024-11-05 18:24 ` Re: Proposals for EXPLAIN: rename ANALYZE to EXECUTE and extend VERBOSE Nikolay Samokhvalov <[email protected]> 2024-11-05 18:30 ` Re: Proposals for EXPLAIN: rename ANALYZE to EXECUTE and extend VERBOSE Tom Lane <[email protected]> 2024-11-05 22:54 ` Re: Proposals for EXPLAIN: rename ANALYZE to EXECUTE and extend VERBOSE Nikolay Samokhvalov <[email protected]> 2024-11-05 23:09 ` Re: Proposals for EXPLAIN: rename ANALYZE to EXECUTE and extend VERBOSE Nikolay Samokhvalov <[email protected]> 2024-11-05 23:32 ` Re: Proposals for EXPLAIN: rename ANALYZE to EXECUTE and extend VERBOSE David G. Johnston <[email protected]> 2024-11-06 00:23 ` Re: Proposals for EXPLAIN: rename ANALYZE to EXECUTE and extend VERBOSE David Rowley <[email protected]> 2024-11-06 16:57 ` Re: Proposals for EXPLAIN: rename ANALYZE to EXECUTE and extend VERBOSE Michael Christofides <[email protected]> 2024-11-11 20:59 ` Re: Proposals for EXPLAIN: rename ANALYZE to EXECUTE and extend VERBOSE Guillaume Lelarge <[email protected]> 2024-11-12 15:21 ` Re: Proposals for EXPLAIN: rename ANALYZE to EXECUTE and extend VERBOSE Robert Haas <[email protected]> 2024-11-12 15:35 ` Re: Proposals for EXPLAIN: rename ANALYZE to EXECUTE and extend VERBOSE Guillaume Lelarge <[email protected]> 2024-11-12 21:02 ` Re: Proposals for EXPLAIN: rename ANALYZE to EXECUTE and extend VERBOSE Guillaume Lelarge <[email protected]> 2024-11-12 21:21 ` Re: Proposals for EXPLAIN: rename ANALYZE to EXECUTE and extend VERBOSE Robert Haas <[email protected]> 2024-11-19 18:13 ` Re: Proposals for EXPLAIN: rename ANALYZE to EXECUTE and extend VERBOSE Guillaume Lelarge <[email protected]> 2024-11-20 00:12 ` Re: Proposals for EXPLAIN: rename ANALYZE to EXECUTE and extend VERBOSE Greg Sabino Mullane <[email protected]> 2024-11-20 01:10 ` Re: Proposals for EXPLAIN: rename ANALYZE to EXECUTE and extend VERBOSE David Rowley <[email protected]> 2024-11-20 01:38 ` Re: Proposals for EXPLAIN: rename ANALYZE to EXECUTE and extend VERBOSE Greg Sabino Mullane <[email protected]> 2024-11-05 18:45 ` Re: Proposals for EXPLAIN: rename ANALYZE to EXECUTE and extend VERBOSE Robert Haas <[email protected]> 2024-11-07 06:55 ` Re: Proposals for EXPLAIN: rename ANALYZE to EXECUTE and extend VERBOSE Peter Eisentraut <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox