Re: Partition pruning for Star Schema

Sorry, I apologize. I though (most) Hackers were not reading General list. Regards PAscal -- Sent from: http://www.postgresql-archive.org/PostgreSQL-hackers-f1928748.html

Re: Partition-wise aggregation/grouping

Thank you for the answer This is a miss understanding of hash join behaviour on my side. That means that there is at less on line read in facts_p2 part even if the second table partition of the hash join operation is empty. I will remember it now ;o) Regards PAscal -- Sent from:

Re: Partition pruning for Star Schema

Thank You ! I will monitor this 'runtime partition pruning' patch. This will be better than using Partitioned DIM tables "Partion wise joined" with a multi level partitioned FACT table ;o) Regards PAscal -- Sent from: http://www.postgresql-archive.org/PostgreSQL-hackers-f1928748.html

Re: Issues while building PG in MS Windows, using MSYS2 and MinGW-w64

Hello, as a complement: I used MSYS2 and MinGW-w64 to build version 11devel from https://www.postgresql.org/ftp/snapshot/dev/ on my win 7 64 bit desktop. What I remember is that I found good advices in

explain (verbose off, normalized) vs query planid

Hello, as described in: http://www.postgresql-archive.org/Re-FEATURE-PATCH-pg-stat-statements-with-plans-v02-td6015488.html I'm wondering about the best way to build a query planid. It seems natural (to me) to calculate a hash value based on the normalized plan text generated by explain

Re: PoC: custom signal handler for extensions

+1 if this permits to use extension pg_query_state , that would be great ! -- Sent from: http://www.postgresql-archive.org/PostgreSQL-hackers-f1928748.html

Re: AS OF queries

> But performing this query on each access to the table seems to be bad > idea: in case of nested loop join it can cause significant degrade of > performance. this could be a pre-plan / pre-exec check, no more. > But I am not sure that this check is actually needed. > If table is changed in some

Re: AS OF queries

> Sorry, truncate is not compatible with AS OF. It is performed at file > level and deletes old old version. > So if you want to use time travel, you should not use truncate. As time travel doesn't support truncate, I would prefer it to be checked. If no check is performed, ASOF queries (with

Re: Add PGDLLIMPORT to enable_hashagg

Thank you Metin ! Regards PAscal -- Sent from: http://www.postgresql-archive.org/PostgreSQL-hackers-f1928748.html

Re: Add PGDLLIMPORT to enable_hashagg

Hello, I know an other extension that would need the same for: src/include/optimizer/paths.h geqo_threshold enable_geqo min_parallel_index_scan_size min_parallel_table_scan_size src/include/optimizer/cost.h max_parallel_workers_per_gather enable_hashjoin enable_mergejoin enable_nestloop

Re: AS OF queries

would actual syntax WITH old_foo AS (select * from foo as of '') select * from foo except select * from old_foo; work in replacement for select * from foo except select * from foo as old_foo as of ''; ? Regards PAscal -- Sent from:

Re: AS OF queries

Maybe that a simple check of the asof_timestamp value like: asof_timestamp >= now() - time_travel_period AND asof_timestamp >= latest_table_ddl would permit to raise a warning or an error message saying that query result can not be garanteed with this asof_timestamp value. latest_table_ddl

Re: pg_stat_statements query jumbling question

Yes, I Regards PAscal -- Sent from: http://www.postgresql-archive.org/PostgreSQL-hackers-f1928748.html

Re: [PROPOSAL] timestamp informations to pg_stat_statements

+1 Having the time of first occurence of a statement is very usefull for trouble shouting, it permits for exemple to retrieve the order of operations in some complex cases (and thoses informations aren't taken by any third party collecting tool, that will only be able to provide a time range of

Re: All Taxi Services need Index Clustered Heap Append

Hello, Would the following custom solution: - a pre-loaded table rows being sorted by id and ts containing null values for other columns, enough free space per block to permit updates in place, - having a (btree or brin) index on (id,ts), - loaded using UPDATEs in spite of INSERTs

Re: [PROPOSAL] timestamp informations to pg_stat_statements

> No, the entries are not removed randomly. We track "usage" for each > entry (essentially +1 for each time the query got executed, with a decay > factor applied on each eviction (and we evict 5% at a time). OK I apologize, I hadn't looked in source code in details, and effectively the "Usage"

Re: [FEATURE PATCH] pg_stat_statements with plans (v02)

> I've created a draft patch that provides access to plans in a view > called pg_stat_statements_plans. ++ I like it ! > There is no column that indicates whether the plan is "good" or "bad", > because that is evident from the execution time of both plans and because > that would require

Re: [FEATURE PATCH] pg_stat_statements with plans (v02)

[...] > I've taken a look at your patch. I agree that having a plan identifier > would be great, but I'm not a big fan of the jumbling. That's a lot of > hashing that needs to be done to decide wether two plans are > essentially equivalent or not. As there is no planid available yet in core, I

Re: Planning counters in pg_stat_statements

Hello, When testing this patch on my WIN1252 database with my java front end, using 11devel snapshot I get org.posgresql.util.PSQLException: ERROR: character with byte sequence 0x90 in encoding "WIN1252" has no equivalent in encoding "UTF8" When using psql with client_encoding = WIN1252,

Re: Diagonal storage model

Great Idea ! thank you Konstantin -- Sent from: http://www.postgresql-archive.org/PostgreSQL-hackers-f1928748.html

Re: Planning counters in pg_stat_statements

I forgot to recompile core ... now only utility statements (with 0 plans) seems truncated. Regards PAscal -- Sent from: http://www.postgresql-archive.org/PostgreSQL-hackers-f1928748.html

Poc: pg_stat_statements with planid

Hello Hackers As described in http://www.postgresql-archive.org/pg-stat-statements-HLD-for-futur-developments-td6012381.html here is a proposal to add a planid identifier to pg_stat_statements. The new key for the view is userid, dbid, queryid, planid. planid is calculated using code from

Re: Planning counters in pg_stat_statements

+1 Shouldn't this be added in next CF ? nb: As plan_time is not included into total_time, could it be added to usage (for statement eviction calculation) ? I will try to include plan_time into my proposed version of pgss with planid.

RE: pg_stat_statements HLD for futur developments

Envoyé : jeudi 22 mars 2018 09:32:13 À : legrand legrand Cc : pgsql-hack...@postgresql.org Objet : Re: pg_stat_statements HLD for futur developments Hello, > As a new user of PostgreSQL, I have started using pg_stat_statements, and > was pleased but a little surprised: > > First of all, t

Re: [FEATURE PATCH] pg_stat_statements with plans (v02)

Hello, I'm very interested in pg_stat_statements usage, and I'm very happy to see you adding plans to it. Reading other pg_stat_statements threads on this forum, there are also activ developments to add: - planing duration, - first date, - last_update date, - parameters for normalized queries, -

pg_stat_statements HLD for futur developments

Hello Kackers, As a new user of PostgreSQL, I have started using pg_stat_statements, and was pleased but a little surprised: First of all, the normalized form of the query string makes it impossible to be used in EXPLAIN commands. Second, normalized constants and parameters values where missing

Re: Sample values for pg_stat_statements

+1 If pgss had a PlanId column (just after QueryId), that would be wonderfull ;o) Question: Is there a simple way to "un-normalize" the query (I mean rebuild the original query as it was before normalization) ? Regards PAscal -- Sent from:

Multiple Wait Events for extensions

Hello, I'm playing with adding into my pg_stat_statements extension a wait event for pgss time duration (pgss_store) Adding pgstat_report_wait_start(PG_WAIT_EXTENSION) gives wait type = "Extension" / event name "Extension" and that's perfect. Now I would like to add a second wait event (for

Re: Multiple Wait Events for extensions

Would a hard coded solution as described here after possible for mid-term ? note: actual result from pgstat_report_wait_star(PG_WAIT_EXTENSION); is preserved. Regards PAscal pgstat.h /* -- * Wait Events - Extension * * Use this category when an extension is waiting. *

Re: Multiple Wait Events for extensions

Michael Paquier-2 wrote > On Wed, Oct 24, 2018 at 11:18:13AM -0700, legrand legrand wrote: >> Would a hard coded solution as described here after possible for >> mid-term? > > I don't think I would commit that as we would want a better solution > with custom names, but pa

Re: Multiple Wait Events for extensions

Michael Paquier-2 wrote > On Wed, Oct 24, 2018 at 11:18:13AM -0700, legrand legrand wrote: >> Would a hard coded solution as described here after possible for >> mid-term? > > I don't think I would commit that as we would want a better solution > with custom names, but pa

RE: [Proposal] Add accumulated statistics for wait event

Hello, You are right, sampling has to be "tuned" regarding the event(s) you want to catch. Sampling of 1 second interval is good with treatments that take hours, and not enough for a minute or a second analysis. May I invite you to try it, using PASH-viewer (github) with pgsentinel (github).

Re: proposal: simple query profile and tracing API

Hello Pavel, What about using wait events and a trigger on pg_stat_activity ? just : * create a functions to get current query signature (queryid) for a pid (not the top_level_query given for pl/pgsql blocks or triggers but the active one) * add some kind of active events to track planning

Re: proposal: simple query profile and tracing API

Pavel Stehule wrote > út 13. 11. 2018 v 20:38 odesílatel Tomas Vondra < > tomas.vondra@ >> napsal: > > My idea is very simple. > > 1. continual collect of data - planning start, execution start, waiting > start, waiting end, query end > > 2. run a some callback function after query is

Re: Delta Materialized View Refreshes?

denty wrote > (Seems I can't attach via the web interface, so copy/paste patch below.) > > -- > Sent from: > http://www.postgresql-archive.org/PostgreSQL-hackers-f1928748.html Sending attachments from this web site (that is not an official postgres website) has been disabled as requested by

Re: [Proposal] Add accumulated statistics for wait event

Bertrand DROUVOT wrote > Hello Guys, > > As you mentioned Oracle like active session history sampling in this > thread, I just want to let you know that I am working on a brand new > extension to provide this feature. > > You can find the extension here: https://github.com/pgsentinel/pgsentinel

Planning counters in pg_stat_statements (using pgss_store)

Starting from https://www.postgresql.org/message-id/CAEepm%3D2vORBhWQZ1DJmKXmCVi%2B15Tgrv%2B9brHLanWU7XE_FWxQ%40mail.gmail.com Here is a patch trying to implement what was proposed by Tom Lane: "What we could/should do instead, I think, is have pgss_planner_hook make its own pgss_store() call to

Re: explain plans with information about (modified) gucs

Tomas Vondra-4 wrote > Hello Sergei, > >> This patch correlates with my proposal >> "add session information column to pg_stat_statements" >> https://www.postgresql.org/message-id/3aa097d7-7c47-187b-5913-db8366cd4491%40gmail.com >> They both address the problem to identify the factors that make

Re: explain plans with information about (modified) gucs

what would you think about adding search_path to that list ? Regards PAscal -- Sent from: http://www.postgresql-archive.org/PostgreSQL-hackers-f1928748.html

Re: {PROPOSAL] add session information column to pg_stat_statements

I'm also very interested by collecting "search_path" information for statements, but this information may not be unique for pg_stat_statements key (dbid,userid,queryid) ... How would this 1-N relation be handled ? 1/ just catch initial session_info for each (dbid,userid,queryid), 2/ adding a

Re: [PROPOSAL] extend the object names to the qualified names in pg_stat_statements

Alvaro Herrera-9 wrote > On 2018-Nov-28, Tom Lane wrote: > >> This would also entail rather significant overhead to find out schema >> names and interpolate them into the text. > > True. I was thinking that the qualified-names version of the query > would be obtained via ruleutils or some

RE: Planning counters in pg_stat_statements (using pgss_store)

Hi, >> >> case avg_tps pct_diff >> 089 278 -- >> 188 745 0,6% >> 288 282 1,1% >> 386 660 2,9% >> >> This means that even in this extrem test case, the worst degradation is less >> than 3% >> (this overhead can be removed using

Re: minimizing pg_stat_statements performance overhead

Hi, the part that hurts in terms or performances is: if (jstate.clocations_count > 0) pgss_store(pstate->p_sourcetext, query->queryId, query->stmt_location, query->stmt_len,

Re: Planning counters in pg_stat_statements (using pgss_store)

>> - trailing whitespaces and comments wider than 80 characters >> not fixed > why? In case it's not clear, I'm talking about the .c file, not the > regression tests. I work on a poor msys install on windows 7, where perl is broken ;o( So no pgindent available. Will fix that

RE: minimizing pg_stat_statements performance overhead

Your fix is probably the best one. Maybe this could be considered as a bug and back ported to previous versions ... Regards PAscal -- Sent from: http://www.postgresql-archive.org/PostgreSQL-hackers-f1928748.html

RE: Planning counters in pg_stat_statements (using pgss_store)

Hi, Here is a rebased and corrected version . Columns naming has not been modified, I would propose to change it to: - plans: ok - planning_time --> plan_time - calls: ok - total_time --> exec_time - {min,max,mean,stddev}_time: ok - new total_time (being the sum of plan_time and exec_time)

RE: Feature improvement: can we add queryId for pg_catalog.pg_stat_activity view?

>> Would it make sense to add it in auto explain ? >> I don't know for explain itself, but maybe ... > I'd think that people interested in getting the queryid in the logs > would configure the log_line_prefix to display it consistently rather > than having it in only a subset of cases, so that's

RE: Feature improvement: can we add queryId for pg_catalog.pg_stat_activity view?

>> Shoudn't you add this to commitfest ? > I added it last week, see https://commitfest.postgresql.org/23/2069/ Oups, sorry for the noise

Re: minimizing pg_stat_statements performance overhead

Robert Haas wrote > On Tue, Apr 2, 2019 at 5:37 AM Christoph Berg > myon@ > wrote: >> Re: Raymond Martin 2019-04-01 > BN8PR21MB121708579A3782866DF1F745B1550@.outlook > >> > Thanks again Fabien. I am attaching the patch to this email in the hope >> of getting it approved during the next

RE: minimizing pg_stat_statements performance overhead

my test case: drop table a; create table a (); DO $$ DECLARE i int; BEGIN for i in 1..20 loop execute 'alter table a add column a'||i::text||' int'; end loop; END $$; select pg_stat_statements_reset(); set pg_stat_statements.track='none'; DO $$ DECLARE i int; j int; BEGIN for j in 1..20 loop

RE: minimizing pg_stat_statements performance overhead

Hi, it seems that your patch is not readable. If you want it to be included in a commitfest, you should add it by yourself in https://commitfest.postgresql.org/ Not sure that there is any room left in pg12 commitfest. Regard PAscal -- Sent from:

Re: DWIM mode for psql

Andreas Karlsson wrote > On 3/31/19 10:52 PM, Thomas Munro wrote:> Building on the excellent work > begun by commit e529cd4ffa60, I would >> like to propose a do-what-I-mean mode for psql. Please find a POC >> patch attached. It works like this: >> >> postgres=# select datnaam from pg_database

Re: Planning counters in pg_stat_statements (using pgss_store)

Hi, I have played with this patch, it works fine. rem the last position of the "new" total_time column is confusing +CREATE VIEW pg_stat_statements AS + SELECT *, total_plan_time + total_exec_time AS total_time +FROM pg_stat_statements(true); I wanted to perform some benchmark between

RE: Planning counters in pg_stat_statements (using pgss_store)

here is a new version: - "track_planning" GUC added to permit to keep previous behavior unchanged - columns names have been changed / added: total_plan_time, total_exec_time, total_time - trailing whitespaces and comments wider than 80 characters not fixed

Re: Planning counters in pg_stat_statements (using pgss_store)

As there are now 3 locking times on pgss hash struct, one day or an other, somebody will ask for a GUC to disable this feature (to be able to run pgss unchanged with only one lock as today). With this GUC, pgss_store should be able to store the query text and accumulated execution duration in

RE: Planning counters in pg_stat_statements (using pgss_store)

> This patch has multiple trailing whitespace, indent and coding style > issues. You should consider running pg_indent before submitting a > patch. I attach the diff after running pgindent if you want more > details about the various issues. fixed > - * Track statement execution times

Re: any plan to support shared servers like Oracle in PG?

There already are solutions regarding this feature in Postgres using "connection pooler" wording see pgpool: http://www.pgpool.net/mediawiki/index.php/Main_Page pgbouncer: https://pgbouncer.github.io/ there are also discussions to include this as a core feature

Re: Feature improvement: can we add queryId for pg_catalog.pg_stat_activity view?

Hello, This is available in https://github.com/legrandlegrand/pg_stat_sql_plans extension with a specific function pgssp_backend_queryid(pid) that permits to join pg_stat_activity with pg_stat_sql_plans (that is similar to pg_stat_statements) and also permits to collect samples of wait events per

[survey] New "Stable" QueryId based on normalized query text

Hello, There are many projects that use alternate QueryId distinct from the famous pg_stat_statements jumbling algorithm. https://github.com/postgrespro/aqo query_hash https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraPostgreSQL.Optimize.ViewPlans.html sql_hash

Re: Feature improvement: can we add queryId for pg_catalog.pg_stat_activity view?

Great, thank you Julien ! Would it make sense to add it in auto explain ? I don't know for explain itself, but maybe ... Regards PAscal -- Sent from: http://www.postgresql-archive.org/PostgreSQL-hackers-f1928748.html

Re: [survey] New "Stable" QueryId based on normalized query text

Julien Rouhaud wrote > On Wed, Mar 20, 2019 at 8:39 PM legrand legrand > > legrand_legrand@ > wrote: >> >> Yes, I would like first to understand what are the main needs, > > I don't really see one implementation that suits every need, as > probably not ever

Re: [survey] New "Stable" QueryId based on normalized query text

maybe this patch (with a GUC) https://www.postgresql.org/message-id/55e51c48.1060...@uptime.jp would be enough for thoses actually using a text normalization function. -- Sent from: http://www.postgresql-archive.org/PostgreSQL-hackers-f1928748.html

Re: [survey] New "Stable" QueryId based on normalized query text

Julien Rouhaud wrote > On Wed, Mar 20, 2019 at 10:18 PM legrand legrand > > legrand_legrand@ > wrote: >> >> On my personal point of view, I need to get the same Queryid between >> (OLAP) >> environments >> to be able to compare Production, Pre-produ

RE: [survey] New "Stable" QueryId based on normalized query text

> From: "Tsunakawa, Takayuki" >> From: legrand legrand [mailto:legrand_legrand@] >> There are many projects that use alternate QueryId >> distinct from the famous pg_stat_statements jumbling algorithm. >I'd like to welcome the standard QueryID that DBAs a

Re: Feature improvement: can we add queryId for pg_catalog.pg_stat_activity view?

Hi Jim, Robert, As this is a distinct subject from adding QueryId to pg_stat_activity, would it be possible to continue the discussion "new QueryId definition" (for postgres open source software) here: https://www.postgresql.org/message-id/1553029215728-0.p...@n3.nabble.com Thanks in advance.

Re: [survey] New "Stable" QueryId based on normalized query text

Julien Rouhaud wrote > On Wed, Mar 20, 2019 at 10:30 PM legrand legrand > > legrand_legrand@ > wrote: >> >> maybe this patch (with a GUC) >> https://www.postgresql.org/message-id/ > 55E51C48.1060102@ >> would be enough for thoses actually using a tex

Re: [survey] New "Stable" QueryId based on normalized query text

> From "Kyotaro HORIGUCHI-2" >>At Wed, 20 Mar 2019 00:23:30 +, "Tsunakawa, Takayuki" >>> From: legrand legrand [mailto:legrand_legrand@] >>> norm.9: comments aware >> Is this to distinguish queries that have different comments for optimizer

Re: Planning counters in pg_stat_statements (using pgss_store)

Hi Sergei, Thank you for this review ! >Did you register patch in CF app? I did not found entry. I think it is related to https://commitfest.postgresql.org/16/1373/ but I don't know how to link it with. Yes, there are many things to improve, but before to go deeper, I would like to know if

Re: Planning counters in pg_stat_statements (using pgss_store)

Thank you Sergei for your comments, > Did you register patch in CF app? I did not found entry. created today: https://commitfest.postgresql.org/22/1999/ > Currently we have pg_stat_statements 1.7 version and this patch does not > apply... will rebase and create a 1.8 version > -

RE: minimizing pg_stat_statements performance overhead

CF entry created https://commitfest.postgresql.org/23/2092/ Regards PAscal -- Sent from: http://www.postgresql-archive.org/PostgreSQL-hackers-f1928748.html

Re: Adaptive query optimization

>>I tried to create much simpler version of AQO based on auto_explain >>extension. >>This extension provide all necessary infrastructure to analyze >>statements with long execution time. >>I have added two new modes to auto_explain: >>1. Auto generation of multicolumn statistics for variables

Re: Adaptive query optimization

Hello, this seems very interesting and make me think about 2 other projets: - https://github.com/trustly/pg_badplan - https://github.com/ossc-db/pg_plan_advsr As I understand all this, there are actually 3 steps: - compare actual / estimated rows - suggests some statistics gathering modification

Re: How to install login_hook in Postgres 10.5

Hello, This extension https://github.com/splendiddata/login_hook seems very interesting ! But I didn't test it myself and maybe the best place to ask support is there https://github.com/splendiddata/login_hook/issues For information there is something equivalent in core "[PATCH] A hook for

Re: Logging the feature of SQL-level read/write commits

Hello, may be you can find more informations regarding WAL concepts in Write Ahead Logging — WAL http://www.interdb.jp/pg/pgsql09.html It seems very complicated to change WAL format ... Maybe there are other solutions to answer your need, I found many interesting solutions in postgres archives

RE: Re: Logging the feature of SQL-level read/write commits

Hi, good point ! wal2Json seems to correspond to your needs, this is first designed for Change Data Capture, taht could generate a (very) big size of logs. You didn't tell us much about your use case ... and maybe, if the number of data modifications is not too big, and the number of tables

Re: [survey] New "Stable" QueryId based on normalized query text

my understanding is * pg_stat_statements.track = 'none' or 'top' (default) or 'all' to make queryId optionally computed * a new GUC: pg_stat_statements.queryid_based = 'oids' (default) or 'names' or 'fullnames' to choose the queryid computation algorithm am I rigth ? -- Sent from:

Re: Feature improvement: can we add queryId for pg_catalog.pg_stat_activity view?

> However having the nested queryid in > pg_stat_activity would be convenient to track > what is a long stored functions currently doing. +1 And this could permit to get wait event sampling per queryid when pg_stat_statements.track = all Regards PAscal -- Sent from:

Re: [survey] New "Stable" QueryId based on normalized query text

Hi Jim, Its never too later, as nothing has been concluded about that survey ;o) For information, I thought It would be possible to get a more stable QueryId, by hashing relation name or fully qualified names. With the support of Julien Rouhaud, I tested with this kind of code: case

Re: Feature improvement: can we add queryId for pg_catalog.pg_stat_activity view?

Kyotaro Horiguchi-4 wrote > At Sun, 4 Aug 2019 00:04:01 -0700 (MST), legrand legrand > legrand_legrand@ > wrote in < > 1564902241482-0.post@.nabble >> >> > However having the nested queryid in >> > pg_stat_activity would be convenient to track >>

Re: How to install login_hook in Postgres 10.5

Hello, shouldn't we update associated commitfest entry https://commitfest.postgresql.org/15/1318/ to give it a chance to be included in pg13 ? Regards PAscal -- Sent from: https://www.postgresql-archive.org/PostgreSQL-hackers-f1928748.html

Re: Hooks for session start and end, take two

Hello, Thank you for the work done on this subject. After starting to play with it, I have a question and a remark: > - previous hook calls were only called for normal backends, which was > incorrect as we define the backend so as we apply no backend-related > filtering for the hook. Does that

Re: Hooks for session start and end, take two

Michael Paquier-2 wrote > On Thu, Sep 26, 2019 at 09:57:57AM -0700, legrand legrand wrote: >> Does that mean that all processes seen in pg_stat_activity like >> - autovacuum launcher >> - logical replication launcher >> - background writer >> - checkpointer >

Re: Proposal: Better query optimization for "NOT IN" clause

Hello, Just for information there are some works regarding how to include this in core, that may interest you ;o) see "NOT IN subquery optimization" https://www.postgresql.org/message-id/flat/1550706289606-0.post%40n3.nabble.com commitfest entry: https://commitfest.postgresql.org/24/2023/ and

Re: Columns correlation and adaptive query optimization

Hello Konstantin, What you have proposed regarding join_selectivity and multicolumn statistics is a very good new ! Regarding your auto_explain modification, maybe an "advisor" mode would also be helpfull (with auto_explain_add_statistics_threshold=-1 for exemple). This would allow to track

Re: How to install login_hook in Postgres 10.5

pavan95 wrote > Hello Community, > > While I was searching for logon trigger in postgres similar to that of > Oracle, I came across "login_hook", which can be installed as a Postgres > database extension to mimic a logon trigger. > > But I tried to install but failed. Error is that it could not

Re: Implementing Incremental View Maintenance

Hello, regarding my initial post: > For each insert into a base table there are 3 statements: > - ANALYZE pg_temp_3.pg_temp_81976 > - WITH updt AS ( UPDATE public.mv1 AS mv SET __ivm_count__ = ... > - DROP TABLE pg_temp_3.pg_temp_81976 For me there where 3 points to discuss: - create/drop

Re: Implementing Incremental View Maintenance

Hello, First of all many thanks for this Great feature replacing so many triggers by a so simple syntax ;o) I was wondering about performances and add a look at pg_stat_statements (with track=all) with IVM_v9.patch. For each insert into a base table there are 3 statements: - ANALYZE

Re: PoC: custom signal handler for extensions

Hello Maksim, reading about https://www.postgresql-archive.org/Allow-auto-explain-to-log-plans-before-queries-are-executed-td6124646.html makes me think (again) about your work and pg_query_state ... Is there a chance to see you restarting working on this patch ? Regards PAscal -- Sent from:

Re: WIP: Aggregation push-down

Antonin Houska-2 wrote > Alvaro Herrera > alvherre@ > wrote: > >> This stuff seems very useful. How come it sits unreviewed for so long? > > I think the review is hard for people who are not interested in the > planner > very much. And as for further development, there are a few design >

Re: Planning counters in pg_stat_statements (using pgss_store)

>> I like the idea of adding a check for a non-zero queryId in the new >> pgss_planner_hook() (zero queryid shouldn't be reserved for >> utility_statements ?). > Some assert hit later, I can say that it's not always true. For > instance a CREATE TABLE AS won't run parse analysis for the

Re: Planning counters in pg_stat_statements (using pgss_store)

Julien Rouhaud wrote > On Sun, Mar 1, 2020 at 3:55 PM legrand legrand > > legrand_legrand@ > wrote: >> >> >> I like the idea of adding a check for a non-zero queryId in the new >> >> pgss_planner_hook() (zero queryid shouldn't be reserved for >&g

Re: Using stat collector for collecting long SQL

Hi > There is a often problem with taking source of long SQL strings. The > pg_stat_activity field query is reduced to some short limit and is not too > practical to increase this limit. I thought it was "old story", since that track_activity_query_size can be increased widely:

Re: Implementing Incremental View Maintenance

>> thank you for patch v14, that fix problems inherited from temporary tables. >> it seems that this ASSERT problem with pgss patch is still present ;o( >> > > Sorry but we are busy on fixing and improving IVM patches. I think fixing > the assertion failure needs non trivial changes to other part

Re: Planning counters in pg_stat_statements (using pgss_store)

Hi Julien, >> But I would have prefered this new feature to work the same way with or >> without track_planning activated ;o( > Definitely, but fixing the issue in pgss (ignoring planner calls when > we don't have a query text) means that pgss won't give an exhaustive > view of activity anymore,

Re: Planning counters in pg_stat_statements (using pgss_store)

Never mind ... Please consider PG13 shortest path ;o) My one is parse->queryId != UINT64CONST(0) in pgss_planner_hook(). It fixes IVM problem (verified), and keep CTAS equal to pgss without planning counters (verified too). Regards PAscal -- Sent from:

Re: Allow auto_explain to log plans before queries are executed

Hi, that feature for dumping plans with auto explain is already available in https://github.com/legrandlegrand/pg_stat_sql_plans This is an hybrid extension combining auto_explain and pg_stat_statements, adding a planid and tracking metrics even on error, ..., ... With

Re: Implementing Incremental View Maintenance

> I have tried to use an other patch with yours: > "Planning counters in pg_stat_statements (using pgss_store)" > setting > shared_preload_libraries='pg_stat_statements' > pg_stat_statements.track=all > restarting the cluster and creating the extension > When trying following syntax: > create

Re: WIP: Aggregation push-down

Hello, Thank you for this great feature ! I hope this will be reviewed/validated soon ;o) Just a comment: set enable_agg_pushdown to true; isn't displayed in EXPLAIN (SETTINGS) syntax. The following modification seems to fix that: src/backend/utils/misc/guc.c

Re: POC: rational number type (fractions)

Hello, It seems you are not the first to be interested in such feature. There was a similar extension used in "incremental view maintenance" testing: https://github.com/nuko-yokohama/pg_fraction didn't tryed it myself. Regards PAscal -- Sent from:

Re: Implementing Incremental View Maintenance

Yugo Nagata wrote > Thank you for your suggestion! I agree that the feature to switch between > normal materialized view and incrementally maintainable view is useful. > We will add this to our ToDo list. Regarding its syntax, > I would not like to add new keyword like NONINCREMENTAL, so how

  1   2   >