Re: Disallow redundant indexes

2025-04-28 Thread Sami Imseih
tect these ( besides a query in the Wiki ), but I don't think we should prevent it. While a WARNING will be a good to have, it could easily go unnoticed, but it's still good to have. I also think we should either provide a psql shortcut to detect these indexes or to add annotation to an index in the \d command, or perhaps both of these things. -- Sami Imseih Amazon Web Services (AWS)

Re: [BUG] temporary file usage report with extended protocol and unnamed portals

2025-04-26 Thread Sami Imseih
logging is the CLOSE command: i.e. ``` 2025-04-26 18:46:38.084 UTC [10415] LOG: temporary file: path "base/pgsql_tmp/pgsql_tmp10415.0", size 1014030336 2025-04-26 18:46:38.084 UTC [10415] STATEMENT: close mycursor_1; ``` I don't think there is much we can do there, or should we.

Re: [BUG] temporary file usage report with extended protocol and unnamed portals

2025-04-25 Thread Sami Imseih
Thanks for testing. I also tested it a bit more today with other patterns like different fetch sizes, named portal, etc. and I can't find an issue with this, but I could be missing something. I will go ahead and attach this change in patch form. -- Sami Imseih Amazon Web Services (AWS)

Re: Conflicting updates of command progress

2025-04-24 Thread Sami Imseih
> pgstat_progress_start_command() is called twice: First with > cmdtype=PROGRESS_COMMAND_CLUSTER, second with > PROGRESS_COMMAND_CREATE_INDEX. The first happens in cluster_rel() the second > in cluster_rel() -> rebuild_relation() -> finish_heap_swap() -> > reindex_relation() -> reindex_index(). > >

Re: Conflicting updates of command progress

2025-04-23 Thread Sami Imseih
d by a top-level command, such as the COPY case above. 2/ a top-level command triggered some other progress code implicitly, such as CLUSTER triggering CREATE INDEX code. I also like the shared memory approach because we can then not have to use a message like the one introduced in f1889729

Re: [BUG] temporary file usage report with extended protocol and unnamed portals

2025-04-23 Thread Sami Imseih
; ?column? -- 1 (1 row) postgres=*# commit; COMMIT ``` ``` 2025-04-23 11:11:47.777 CDT [67362] LOG: temporary file: path "base/pgsql_tmp/pgsql_tmp67362.0", size 1009983488 2025-04-23 11:11:47.777 CDT [67362] STATEMENT: select from foo order by a ; ``` thoughts? -- Sami Imseih Amazon Web Services (AWS)

Re: [BUG] temporary file usage report with extended protocol and unnamed portals

2025-04-22 Thread Sami Imseih
_files is a better alternative and a statement can be logged with other existing mechanisms like log_min_duration_statement. -- Sami Imseih Amazon Web Services (AWS)

Re: [BUG] temporary file usage report with extended protocol and unnamed portals

2025-04-22 Thread Sami Imseih
e statement to blame for the temp files and will cover all cases? [0] https://www.postgresql.org/message-id/CAA5RZ0ssqRTz_9T0Gy74SiTViiX3V0rSFxc4N_4GNcbEBK9wow%40mail.gmail.com -- Sami Imseih Amazon Web Services (AWS)

Re: [BUG] temporary file usage report with extended protocol and unnamed portals

2025-04-19 Thread Sami Imseih
> Sami Imseih writes: > > I think the solution proposed by Frédéric seems reasonable: to switch > > the debug_query_string > > inside PortalDrop. However, I do not like the way the > > debug_query_string changes values > > after the CreatePortal call inside exec_b

Re: [BUG] temporary file usage report with extended protocol and unnamed portals

2025-04-19 Thread Sami Imseih
CreatePortal call inside exec_bind_message; that seems incorrect. So, I believe we should temporarily switch the debug_query_string value only while running PortalDrop. Attached is what I think could be safer to do. What do you think? -- Sami Imseih Amazon Web Services (AWS) v2-0001-Fix-race

Re: n_ins_since_vacuum stats for aborted transactions

2025-04-18 Thread Sami Imseih
> I can see it being confusing. What about this wording to make it more > clear when the field is > updated? here are both of the changes in v4. -- Sami Imseih Amazon Web Services (AWS) v4-0001-Clarify-when-aborted-rows-are-tracked-for-tuple-r.patch Description: Binary data

Re: n_ins_since_vacuum stats for aborted transactions

2025-04-18 Thread Sami Imseih
On Thu, Apr 17, 2025 at 11:13 PM David Rowley wrote: > > On Sat, 12 Apr 2025 at 07:33, Sami Imseih wrote: > > What do you think of the attached? > > I looked at the v3 patch and I'm having trouble getting excited about it. > > I'd say this part is

Re: [BUG] temporary file usage report with extended protocol and unnamed portals

2025-04-18 Thread Sami Imseih
care of the problem because it ensures that the drop portal, when it occurs, is referencing the correct sql. I am not yet sure if the patch the right solution yet, it maybe the best answer. I don't have a better answer, but wanted to share this research as well. -- Sami Imseih Amazon Web Services (AWS) Test.java Description: Binary data

Re: Conflicting updates of command progress

2025-04-14 Thread Sami Imseih
herefore, only the top level command is updating progress. what do you think? [1] https://commitfest.postgresql.org/patch/5117/ -- Sami Imseih Amazon Web Services (AWS)

Re: stats.sql fails during installcheck on mac

2025-04-11 Thread Sami Imseih
> > what do you think of this? I think we should set fsync = on > > at least for the part of the test that proceeds the 2 checkpoints and > > set if back to off at the end of the tests for fsync stats. It is concerning > > the tests for the fsync stats are not being exercised in > > the buildfarm.

Re: n_ins_since_vacuum stats for aborted transactions

2025-04-11 Thread Sami Imseih
> WFM. Though is there a reason to avoid adding the "why" of the exception for > n_mod_since_analyze? I did think about that. I thought it will be understood that since this is a field that deals with ANALYZE, it will be understood that only committed changes matter here, and not worth adding mo

Re: n_ins_since_vacuum stats for aborted transactions

2025-04-11 Thread Sami Imseih
think the current explanation is clear enough, I am also not too thrilled about the "...to facilitate performance monitoring." since the cumulative stats system as a whole is known to be used to facilitate perf monitoring. What do you think of the attached? -- Sami Imseih Amazon Web Servi

Re: stats.sql fails during installcheck on mac

2025-04-11 Thread Sami Imseih
e it runs > with fsync=off, as I mentioned at the top of the thread. what do you think of this? I think we should set fsync = on at least for the part of the test that proceeds the 2 checkpoints and set if back to off at the end of the tests for fsync stats. It is concerning the tests for the f

Re: n_ins_since_vacuum stats for aborted transactions

2025-04-10 Thread Sami Imseih
> On Fri, 11 Apr 2025 at 02:01, Sami Imseih wrote: > > I created an entry for the July CF > > https://commitfest.postgresql.org/patch/5691/ > > > > ... and I realized I forgot to include David's code comment patch yesterday, > > Reattaching both patches.

Re: stats.sql fails during installcheck on mac

2025-04-10 Thread Sami Imseih
o be tightened SELECT sum(writes) AS writes, sum(fsyncs) AS fsyncs FROM pg_stat_io WHERE context = 'normal' AND object = 'wal' \gset io_sum_wal_normal_after_ -- Sami Imseih Amazon Web Services (AWS)

Re: track generic and custom plans in pg_stat_statements

2025-04-10 Thread Sami Imseih
e explain, and we may not even need it at all since generic plans are already displayed with $1, $2 parameters. Let me know if you have other comments for v5, the pg_stat_statements enhancements. — Sami Imseih Amazon Web Services (AWS)

Re: track generic and custom plans in pg_stat_statements

2025-04-10 Thread Sami Imseih
rebased in the attached v5. -- Sami Imseih Amazon Web Services (AWS) v5-0001-Add-plan_cache-counters-to-pg_stat_statements.patch Description: Binary data

stats.sql fails during installcheck on mac

2025-04-10 Thread Sami Imseih
emented when the wal_sync_method is either fdatasync, fsync or fsync_writethrough". Perhaps, the same clarification will be beneficial for the pg_stat_io.fsyncs* fields? [0] https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-WAL-VIEW [1] https://github

Re: n_ins_since_vacuum stats for aborted transactions

2025-04-10 Thread Sami Imseih
I created an entry for the July CF https://commitfest.postgresql.org/patch/5691/ ... and I realized I forgot to include David's code comment patch yesterday, Reattaching both patches. -- Sami Imseih Amazon Web Services (AWS) v1-0001-Add-code-comment-for-ins_since_vacuum.patch Descri

Re: n_ins_since_vacuum stats for aborted transactions

2025-04-09 Thread Sami Imseih
autovacuum. I do think it's probably a good idea to add code comment that it is OK to include dead tuples from a aborted insert into the n_ins_since_vacuum counter, for the above reasons. I will also update the public documentation for the counters to mention that they include rows from aborted transactions. -- Sami Imseih

Re: n_ins_since_vacuum stats for aborted transactions

2025-04-09 Thread Sami Imseih
fields that count row changes I initially thought about adding clarification for every field, but that felt too repetitive. So, I add a Note section after pg_stat_all_tables in the public docs to describe the behavior. It may be better to apply your code comment patch and the public docs patch separ

Re: n_ins_since_vacuum stats for aborted transactions

2025-04-09 Thread Sami Imseih
nserts. tabentry->ins_since_vacuum += lstats->counts.tuples_inserted; -- Sami Imseih

Re: n_ins_since_vacuum stats for aborted transactions

2025-04-09 Thread Sami Imseih
message-id/CAA5RZ0uDyGW1omWqWkxyW8NB1qzsKmXhnoMtzTBeRzSd4DMatQ%40mail.gmail.com -- Sami Imseih

Re: n_ins_since_vacuum stats for aborted transactions

2025-04-09 Thread Sami Imseih
mm is not relevant to the formula for this case. In other words, the reason n_ins_since_vacuum was introduced is to freeze (committed) rows, so it should not need to track dead rows to do what it intends to do. -- Sami Imseih

Re: n_ins_since_vacuum stats for aborted transactions

2025-04-09 Thread Sami Imseih
| 0 n_tup_del | 0 n_live_tup | 3000 n_dead_tup | 2000 n_mod_since_analyze | 3000 n_ins_since_vacuum | 5000 ``` -- Sami Imseih Amazon Web Services (AWS)

Re: n_ins_since_vacuum stats for aborted transactions

2025-04-09 Thread Sami Imseih
es/blob/master/src/backend/utils/activity/pgstat_relation.c#L238-L247 -- Sami Imseih Amazon Web Services (AWS)

Re: n_ins_since_vacuum stats for aborted transactions

2025-04-09 Thread Sami Imseih
t dead tuples makes more sense > on its face. IIUC, I am saying the same thing, if an inserted row is rolled back, it should only count as a dead tuple (n_dead_tup) only, and not in n_ins_since_vacuum. -- Sami Imseih Amazon Web Services (AWS)

Re: n_ins_since_vacuum stats for aborted transactions

2025-04-09 Thread Sami Imseih
tovacuum_vacuum_scale_factor|threshold calculation. The purpose of b07642dbcd8d is to trigger autovacuum for append only/mainly workloads that don't generate dead tuples. -- Sami Imseih Amazon Web Services (AWS)

n_ins_since_vacuum stats for aborted transactions

2025-04-09 Thread Sami Imseih
/current/monitoring-stats.html -- Sami Imseih Amazon Web Services (AWS)

Re: Proposal - Allow extensions to set a Plan Identifier

2025-04-07 Thread Sami Imseih
> What if, for 18, we just make DoJumble exported to be used by plugins? We will need to export InitJumble as well, but DoJumble calls _jumbleNode which only includes queryjumblefuncs.switch.c, so I don't think that will be enough for Plan. -- Sami Imseih Amazon Web Services (AWS)

Re: [PATCH] Re: Proposal to Enable/Disable Index using ALTER INDEX

2025-04-07 Thread Sami Imseih
> Attached v16 with feedback and rebased. Thanks, and I realized the original tab-complete I proposed was not entirely correct. I fixed it and also shortened the commit message. -- Sami Imseih Amazon Web Services (AWS) v17-0001-Introduce-the-ability-to-set-index-visibility-us.pa

Re: [PATCH] Re: Proposal to Enable/Disable Index using ALTER INDEX

2025-04-07 Thread Sami Imseih
!TailMatches("FOR", MatchAny, MatchAny, MatchAny)) COMPLETE_WITH("("); + else if (TailMatches("*)")) + COMPLETE_WITH("VISIBLE", "INVISIBLE"); /* CREATE OR REPLACE */ else if (Matches("CREATE&q

Re: New criteria for autovacuum

2025-04-06 Thread Sami Imseih
this may degrade the select workloads, maybe? -- Sami Imseih Amazon Web Services (AWS)

Re: rename pg_log_standby_snapshot

2025-04-06 Thread Sami Imseih
t; one. I don't really agree with this. I think breaking compatibility in the next major release makes sense here. We already have pg_log_backend_memory_contexts writing to the log, and If we end up adding a few more functions called pg_log_something that write to the log file, pg_log_standby_sn

Re: RFC: Logging plan of the running query

2025-04-05 Thread Sami Imseih
ns using the structured formats. 8/ + es->format = EXPLAIN_FORMAT_TEXT; + es->settings = true; + es->verbose = true; + es->signaled = true; + + ExplainAssembleLogOutput(es, ActiveQueryDesc, EXPLAIN_FORMAT_TEXT, 0, -1); + Can we just pass es->format to ExplainAssembleLogOutput as the 3rd argument? -- Sami Imseih Amazon Web Services (AWS)

Re: RFC: Logging plan of the running query

2025-04-05 Thread Sami Imseih
ust realized that my comment above is unwarranted. A superuser can just simply GRANT EXECUTE ON FUNCTION to pg_monitor, or whatever monitoring role if they choose. You can ignore this. -- Sami Imseih Amazon Web Services (AWS)

rename pg_log_standby_snapshot

2025-04-05 Thread Sami Imseih
tps://www.postgresql.org/message-id/flat/0e0e7ca08dff077a625c27a5e0c2ef0a%40oss.nttdata.com#6539312988e4695d2d416688a3ab34fa -- Sami Imseih Amazon Web Services (AWS)

Re: New criteria for autovacuum

2025-04-04 Thread Sami Imseih
ches" issue and confirming whether vacuum tuning had any impact to reduce "heap fetches". The existing indx_tup_fetch combines heap fetches from normal and index-only scans, so it could be useful but not perfect for this task. -- Sami Imseih Amazon Web Services (AWS)

Re: New criteria for autovacuum

2025-04-04 Thread Sami Imseih
sn't that what anti-wraparound autovac does? or, I may have missed the point here completely [0] https://www.postgresql.org/message-id/CAA5RZ0t1U38qtVAmg3epjh5RBbpT4VRB_Myfp0oGm_73w-UNRA%40mail.gmail.com -- Sami Imseih Amazon Web Services (AWS)

Re: making EXPLAIN extensible

2025-04-04 Thread Sami Imseih
> On Tue, Mar 18, 2025 at 11:21 PM Sami Imseih wrote: > > > > Do you want to propose a patch? > > > > > > yes, will attach a patch shortly. > > > > Attached is a patch to add a hook to allow extensions > > to add additional option validations. Th

Re: rename pg_log_standby_snapshot

2025-04-04 Thread Sami Imseih
t; similar to the existing function pg_create_restore_point(). I think we > > need to think about backward compatibility if we agree with moving in > > this direction. > > +1 This is slightly better. pg_create_restore_point also writes to wal and _create_ has a more generic meaning. -- Sami Imseih Amazon Web Services (AWS)

Re: New criteria for autovacuum

2025-04-03 Thread Sami Imseih
e. index-only scan with heap fetches or a regular index scan. I think having a counter specifically for heap fetches due to index-only scans could be valuable. -- Sami Imseih Amazon Web Services (AWS)

Re: [PATCH] Re: Proposal to Enable/Disable Index using ALTER INDEX

2025-04-02 Thread Sami Imseih
s patch will provide the ability for the user to create an index as initially invisible and a GUC, use_invisible_index if set to TRUE to experiment with the new index to see if it improves performance. So, I think providing this pattern to experiment with a new index will fit nicely as a new bul

Re: [PATCH] Re: Proposal to Enable/Disable Index using ALTER INDEX

2025-04-02 Thread Sami Imseih
isible indexes as one of the tools for experimentation can be added here. What do you think? [0] https://www.postgresql.org/docs/current/indexes-examine.html -- Sami Imseih Amazon Web Services (AWS)

Re: Proposal - Allow extensions to set a Plan Identifier

2025-04-02 Thread Sami Imseih
> { > + Assert(jstate->clocations); > + Here is v3 with the Assert in place -- Sami Imseih Amazon Web Services (AWS) v3-0001-Allow-plugins-to-Jumble-an-expression.patch Description: Binary data

Re: RFC: Logging plan of the running query

2025-04-01 Thread Sami Imseih
rg/message-id/flat/CAP%2BB4TD%3Diy-C2EnsrJgjpwSc7_4pd3Xh-gFzA0bwsw3q8u860g%40mail.gmail.com -- Sami Imseih Amazon Web Services (AWS)

Re: Proposal to Enable/Disable Index using ALTER INDEX (with patch)

2025-04-01 Thread Sami Imseih
marked RFC, although not sure if it will get a committer review before code freeze. [0] https://www.postgresql.org/message-id/flat/CAA5RZ0udzydObMDi65C59-oq54B9ZmjSZ1wVH3h%2Bv4XiVm6QDA%40mail.gmail.com#cfea240ffd73e947f9edd1ef1c762dae -- Sami Imseih Amazon Web Services

Re: Proposal - Allow extensions to set a Plan Identifier

2025-04-01 Thread Sami Imseih
On Mon, Mar 31, 2025 at 1:28 PM Lukas Fittl wrote: > > On Tue, Mar 25, 2025 at 8:12 PM Sami Imseih wrote: >>> >>> So this comes down to forking the Postgres code to do the job. What I >>> had in mind was a slightly different flow, where we would be able t

Re: Query ID Calculation Fix for DISTINCT / ORDER BY and LIMIT / OFFSET

2025-03-26 Thread Sami Imseih
t_statements_reset() IS NOT NULL AS t 1 |2 | WITH t(f) AS ( + @@ -230,7 +228,7 @@ | | ) + | | SELECT f FROM t ORDER BY f 1 |1 | select $1::jsonb ? $2 -(17 rows) +(15 rows) but when v9-0002 is applied, the regress tests then succeed. -- Sami Imseih Amazon Web Services (AWS)

Re: Proposal - Allow extensions to set a Plan Identifier

2025-03-25 Thread Sami Imseih
19. What about for 18 we think about exposing AppendJumble, JUMBLE_FIELD, JUMBLE_FIELD_SINGLE and JUMBLE_STRING? — Sami Imseih Amazon Web Services (AWs) >

Re: query_id: jumble names of temp tables for better pg_stat_statement UX

2025-03-25 Thread Sami Imseih
me meaning as “semantically equivalent”, this is why I used the same terminology here. But, I have no issue with the simplified rewrite either. The patch LGTM as well. Regards, Sami Imseih Amazon Web Services (AWS)

Re: Proposal - Allow extensions to set a Plan Identifier

2025-03-25 Thread Sami Imseih
> On 3/25/25 00:47, Sami Imseih wrote: > > I know it was mentioned above by both Michael and Andrei that > > AppendJumble should not be exposed. I am not sure I agree with > > that. I think it should be exposed, along with > > JUMBLE_FIELD, JUMBLE_FIELD_SINGLE and JUMBLE

Re: Squash constant lists in query jumbling by default

2025-03-25 Thread Sami Imseih
> At the same time the proposal to do squashing by default > does not seem to be strictly dependent on that, so maybe they could be > considered as isolated ideas. Here is a patch to remove the GUC, if we settle on doing so. -- Sami Imseih Amazon Web Services (AWS) v1-0001-R

Re: Squash constant lists in query jumbling by default

2025-03-25 Thread Sami Imseih
fEXfEfNw3uRN3D3oXkFPQ_s%2BhuLLHMKR_%2BMCk8RPQ%40mail.gmail.com#c357c56c3924642e8ef73cc1c8a0286e -- Sami Imseih Amazon Web Services (AWS)

Re: query_id: jumble names of temp tables for better pg_stat_statement UX

2025-03-25 Thread Sami Imseih
ecutions of queries, two apparently-identical queries will be considered the same. However, if the alias for a table is different for semantically similar queries, these queries will be considered distinct" -- Sami Imseih Amazon Web Services (AWS)

Re: query_id: jumble names of temp tables for better pg_stat_statement UX

2025-03-25 Thread Sami Imseih
-cases that rely on the existing behavior. But I do agree that pg_s_s bloat is a big pain point, so this change should be positive overall. Let's see if there are enough complaints to force us to reconsider. -- Sami Imseih Amazon Web Services (AwS)

Re: query_id: jumble names of temp tables for better pg_stat_statement UX

2025-03-24 Thread Sami Imseih
his here. Looking at the patches, I could not find anything else besides what was pointed out by Christoph earlier. -- Sami Imseih Amazon Web Services (AWS)

Re: query_id: jumble names of temp tables for better pg_stat_statement UX

2025-03-24 Thread Sami Imseih
> Sami Imseih writes: > > I agree that some may want stats to merge for the same tables, > > and others may not. I will suggest this with some hesitation, but why not > > make this behavior configurable via a GUC? > > We recently introduced query_id_squash_values for c

Re: Proposal - Allow extensions to set a Plan Identifier

2025-03-24 Thread Sami Imseih
> On Mon, Mar 24, 2025 at 02:36:05PM -0500, Sami Imseih wrote: > >> Ideally we can also land the jumble funcs work in the other thread > >> to allow extensions to re-use the > >> in-core logic for jumbling expressions found in plan node trees. > > > > IIU

Re: query_id: jumble names of temp tables for better pg_stat_statement UX

2025-03-24 Thread Sami Imseih
UC? We recently introduced query_id_squash_values for controlling the merge of an IN list, maybe this is another queryId behavior we should provide a configuration for? -- Sami Imseih Amazon Web Services (AWS)

Re: query_id: jumble names of temp tables for better pg_stat_statement UX

2025-03-24 Thread Sami Imseih
10 | 1788423388555345932 | select /*+ user s1 */ * from foo | 2 10 | -8935568138104064674 | select pg_stat_statements_reset() | 1 10 | -8663970364987885379 | set search_path = $1 | 2 10 | -6563543739552933350 | reset search_path | 1 (4 rows) ``` -

Re: Proposal - Allow extensions to set a Plan Identifier

2025-03-24 Thread Sami Imseih
> Ideally we can also land the jumble funcs work in the other thread to allow > extensions to re-use the > in-core logic for jumbling expressions found in plan node trees. IIUC, there should be a refactor I am working on at this moment to make that possible [0] >> > FWIW, Lukas did start a Wiki

Re: Proposal - Allow extensions to set a Plan Identifier

2025-03-24 Thread Sami Imseih
er to stick this all in a macro, but I did it that way because we have similar pattern for queryId, with some repeating patterns for looping through a list of Query or PlannedStmt. As this patch already got committed, feel free to propose a patch for this improvement. -- Sami Imseih Amazon Web Services (AWS)

Re: Proposal - Allow extensions to set a Plan Identifier

2025-03-23 Thread Sami Imseih
to > commit planId generation code? But, extensions don't necessarily need to rely on the core queryId. They can generate their own queryId. We have it documented for compute_query_id as such [0] "Note that an external module can alternatively be used if the in-core query identifier

Re: Proposal - Allow extensions to set a Plan Identifier

2025-03-22 Thread Sami Imseih
wiki/Plan_ID_Jumbling [1] https://www.postgresql.org/message-id/CA%2BTgmoZ8sTodL-orXQm51_npNxuDAS86BS5kC8t0LULneShRbg%40mail.gmail.com -- Sami Imseih Amazon Web Services (AWS)

Re: pg_stat_statements and "IN" conditions

2025-03-22 Thread Sami Imseih
lanner turns the Param into a Const during eval_const_expressions_mutator. If it's as simple as I think it is, I hope we can get this committed for 18. If not, and a longer discussion is needed, a new thread can be started for this. -- Sami Imseih Amazon Web Services (AWS) v1-0001-Allow-que

Re: Proposal - Allow extensions to set a Plan Identifier

2025-03-21 Thread Sami Imseih
the likelihood they find an empty plan_id. Overall, v3 LGTM -- Sami Imseih Amazon Web Services (AWS)

Re: making EXPLAIN extensible

2025-03-21 Thread Sami Imseih
, it can be added into pg_overexplain overtime. overall this LGTM, and I don't really have other comments. -- Sami Imseih Amazon Web Services (AWS)

Re: Proposal - Allow extensions to set a Plan Identifier

2025-03-20 Thread Sami Imseih
; Hmm. Shouldn't we document all such expectations, > which are similar to a query ID? I had a lazy comment :) * For the same reasons as the query identifiers (see above), but, I went ahead and commented it similar to how we document pgstat_report_qu

Re: making EXPLAIN extensible

2025-03-20 Thread Sami Imseih
n or # of characters, but for [0] https://www.postgresql.org/message-id/8e8b6f17-e1e6-4b16-84d4-37ded802c787%40gmail.com -- Sami Imseih Amazon Web Services (AWS)

Re: making EXPLAIN extensible

2025-03-20 Thread Sami Imseih
> > On 19/3/2025 21:51, Sami Imseih wrote: > >> Why do you think this hook is not redundant? > > what is it redundant with? > > > >> It would be better to add the parameter "type: EXPLAIN_ONLY | > >> ANALYZE_ONLY | BOTH" to the RegisterEx

Re: making EXPLAIN extensible

2025-03-19 Thread Sami Imseih
ension options? let's say ExtensionAOptionA and ExtensionAoptionB. How would that work with the way you are suggesting? -- Sami Imseih Amazon Web Services (AWS)

Re: making EXPLAIN extensible

2025-03-19 Thread Sami Imseih
> On Wed, Mar 19, 2025 at 11:38 AM Sami Imseih wrote: > > ... as I made the hook signature match that of > > ParseExplainOptionList, so both pstate and the options list > > are now available to the hook. > > This version LGTM, except it's not pgindent-clean. u

Re: making EXPLAIN extensible

2025-03-18 Thread Sami Imseih
error if the validation fails. -- Sami Imseih Amazon Web Services (AWS) v1-0001-Add-new-hooks-for-performing-additional-EXPLAIN-o.patch Description: Binary data

Re: pg_stat_statements and "IN" conditions

2025-03-18 Thread Sami Imseih
I want to mention that the current patch does not deal with external parameters ( prepared statements ) [0] [1]. This could be a follow-up, as it may need some further discussion. It is important to address this case, IMO. [0] https://www.postgresql.org/message-id/CAA5RZ0uGfxXyzhp9N5nfsS%2BZqF5ng

Re: making EXPLAIN extensible

2025-03-18 Thread Sami Imseih
> Do you want to propose a patch? yes, will attach a patch shortly. -- Sami

Re: making EXPLAIN extensible

2025-03-17 Thread Sami Imseih
> You only > need the second hook if you want to check the values of options > against the values of other options. +1 I did not think of adding a new hook, because there must be a really good reason to add a new hook. I think it's justified for this case. It's better than my approach since the ex

Re: track generic and custom plans in pg_stat_statements

2025-03-15 Thread Sami Imseih
> Thank you for your patch. It is really useful for tracking the history > of generic and custom plan usage. Thanks for the review! > 1. Is there any reason for the double check of cplan != NULL? It seems > unnecessary, and we could simplify it to: > > -if (cplan && cplan->status == PLAN_CACHE_ST

Re: Query ID Calculation Fix for DISTINCT / ORDER BY and LIMIT / OFFSET

2025-03-13 Thread Sami Imseih
> FWIW, another idea I have on top of my mind is the addition of a > counter in JumbleState that we increment each time we enter > _jumbleNode(), then simply call JUMBLE_FIELD_SINGLE() after the > incrementation. And we can rely on this counter to be unique each > time we jumble a node.. With thi

Re: Query ID Calculation Fix for DISTINCT / ORDER BY and LIMIT / OFFSET

2025-03-13 Thread Sami Imseih
> Then we have the same problem with another Node using this Foo node > two times in a row, depending on how it's used by the query parsing > and transform: hmm, it's hard to imagine such a case being real-world and useful for query jumbling purposes. Also, I think if we introduce something like J

Re: making EXPLAIN extensible

2025-03-13 Thread Sami Imseih
E_PLANS and ANALYZE cannot be used together postgres=# explain (analyze, remote_plans) select * from t_r1; ERROR: EXPLAIN options REMOTE_PLANS and ANALYZE cannot be used together Regards, Sami From d4350df06cfeb0d9d5d7fe99b898c1a7ef237c97 Mon Sep 17 00:00:00 2001 From: Sami Imseih Date: Thu, 1

Re: Query ID Calculation Fix for DISTINCT / ORDER BY and LIMIT / OFFSET

2025-03-13 Thread Sami Imseih
ery jumbling is weightless compared to > the overall query processing yeah, that is a good point. At least benchmarking the above on a SELECT only pgbench did not reveal any regression. -- Sami Imseih Amazon Web Services (AWS)

Re: making EXPLAIN extensible

2025-03-12 Thread Sami Imseih
ext formats, which is not desirable behavior. I get that also. I have no strong feelings for this, but wanted to see what others think. thanks! -- Sami Imseih

Re: making EXPLAIN extensible

2025-03-12 Thread Sami Imseih
ary : es->analyze; What do you think? [0] https://www.postgresql.org/message-id/flat/CAP%2BB4TD%3Diy-C2EnsrJgjpwSc7_4pd3Xh-gFzA0bwsw3q8u860g%40mail.gmail.com [1] https://github.com/postgres/postgres/blob/master/src/backend/commands/explain.c#L2013 Thanks -- Sami Imseih Amazon Web Services (AWS)

Re: Query ID Calculation Fix for DISTINCT / ORDER BY and LIMIT / OFFSET

2025-03-11 Thread Sami Imseih
> and simple. However, Sami seems to have concerns about the overhead of > doing this. Is that warranted at all? Potentially, there could be a > decent number of NULL fields. It'll probably be much cheaper than the > offsetof idea I came up with. I have not benchmarked the overhead, so maybe there

Re: Query ID Calculation Fix for DISTINCT / ORDER BY and LIMIT / OFFSET

2025-03-11 Thread Sami Imseih
> transformation. For these reasons, variant A where we put the > LimitOption between the two int8 expression nodes feels like the > "okay" approach here. But we must document this expectation in the > structure, and check for more grammar variants of LIMIT and OFFSET > clauses in pgss. Please s

Re: Query ID Calculation Fix for DISTINCT / ORDER BY and LIMIT / OFFSET

2025-03-11 Thread Sami Imseih
> It seems to me that if this fixes the issue, that the next similar one > is already lurking in the shadows waiting to jump out on us. Yes, this is true that there may be other cases, but I am not sure if it's worth carrying all the extra bytes in the jumble to deal with a few cases like this. Th

Re: track generic and custom plans in pg_stat_statements

2025-03-10 Thread Sami Imseih
> I don't quite understand why do we need to differentiate between > PLAN_CACHE_STATUS_GENERIC_PLAN_BUILD and > PLAN_CACHE_STATUS_GENERIC_PLAN_REUSE? > We could simply keep PLAN_CACHE_STATUS_GENERIC_PLAN_REUSE. > I don't think users would see much of a difference in either > pg_stat_statements or

Re: Query ID Calculation Fix for DISTINCT / ORDER BY and LIMIT / OFFSET

2025-03-06 Thread Sami Imseih
lse { JUMBLE_NODE(limitOffset); } if (!expr->limitCount) { JUMBLE_NULL(); } else { JUMBLE_NODE(limitCount); } """ What do you think? Maybe someone can suggest a better

Re: track generic and custom plans in pg_stat_statements

2025-03-05 Thread Sami Imseih
> > Please see v2 > oops, had to fix a typo in meson.build. Please see v3. -- Sami v3-0001-add-plan_cache-counters-to-pg_stat_statements.patch Description: Binary data

Re: track generic and custom plans in pg_stat_statements

2025-03-05 Thread Sami Imseih
> + Total number of non-utility statements executed using a generic plan > > I'm not sure we need to specify non-utility here. > fair, I did not have strong feeling about this either. Please see v2 Thanks! -- Sami Imseih Amazon Web Services (AWS) v2-0001-add-plan_cache-counters-to-pg_stat_statements.patch Description: Binary data

Re: explain plans for foreign servers

2025-03-05 Thread Sami Imseih
have any > mechanism for addressing that at al FWIW, I had the same thought [0] and planned on doing the investigation. [0] https://www.postgresql.org/message-id/CAA5RZ0tLrNOw-OgPkv49kbNmZS4nFn9vzpN5HXX_xvOaM9%3D5ww%40mail.gmail.com -- Sami Imseih

track generic and custom plans in pg_stat_statements

2025-03-05 Thread Sami Imseih
information can be added to EXPLAIN output and perhaps pg_stat_database. Maybe that's a good idea also? This patch bumps the version pf pg_stat_statements. -- Sami Imseih Amazon Web Services (AWS) [0] https://www.postgresql.org/message-id/add1e591fbe8874107e75d04328859ec%40oss.nttdata.com

should num_custom_plans be reset after plan invalidation?

2025-03-05 Thread Sami Imseih
a plan cache invalidation might be a better approach. I've attached an example for reference. The fix seems straightforward, but since generic plans may already not handle skewed data optimally, I want to see if others have thoughts on this being something to fix. -- Sami Imseih Amazo

Re: should num_custom_plans be reset after plan invalidation?

2025-03-05 Thread Sami Imseih
> Yes, it is. There's little reason to expect that the invalidation > would change our decision, and re-planning five times to confirm that > is a high price to pay. Re-planning 5 times did not sound appealing to me either, but I was on the fence as to what the proper behavior should be. Thanks

  1   2   3   >