Le lun. 20 janv. 2025 à 04:28, Sami Imseih <samims...@gmail.com> a écrit :
> > > > Hmm. I am reading Tom's opinion that goes toward not going in this > > > > direction for more commands, with the point to extend EXPLAIN to show > > > > this kind of information: > > > > > https://www.postgresql.org/message-id/1692530.1736369...@sss.pgh.pa.us > > > > > > That sounds like the ability to do something like EXPLAIN CREATE INDEX > ... > > > is that correct? > > > > > > Yes, and I suppose this would be quite some work to do. Though I see how > > interesting it would be, and a nice project to hack on. > > I have been contemplating this and do see how something like EXPLAIN > could be useful, as it would give users the ability to examine > what a command will do, i.e. EXPLAIN CREATE INDEX will tell you > how many parallel workers are planned and EXPLAIN (ANALYZE) CREATE > INDEX will actually execute the command and provide the actual parallel > workers launched. Tom also mentioned the example of a user wanting to know > if > a table rewrite will occur. There are other cases where this could be > useful. > > FWIW, Oracle does support EXPLAIN PLAN FOR CREATE INDEX and > EXPLAIN PLAN FOR REBUILD (for index rebuilds) [1][2], but this > functionality > is not officially documented [3]. Oracle also provides a documented > function to > estimate the size of an index [4]. I do not have access to an Oracle > instance to > verify the state of current versions of Oracle, but I do vaguely remember > this > from when I worked on Oracle many years ago :) > > Now, I also realized that v1 does not include logging for ALTER TABLE > commands > that add an index, such as "ALTER TABLE foo ADD CONSTRAINT foo_uq UNIQUE > (c1)" > or attaching a partition and ensuring the index is built. So this > needs to be added to > the current proposal. > > So far these are 2 options being discussed: > > 1/ > The current proposal of: > > CREATE INDEX (VERBOSE) and ALTER TABLE (VERBOSE) > and output debugging at INFO level. > > I guess the major issue is that it won't scale fine. > 2/ > EXPLAIN > > This is a lot more work. Currently QueryDesc has all the info we > need to generate the execution plan. But, we will likely need a > new struct that can track the debugging info to relay it back > up to explain. And this has to be generic enough for all cases. > > Also, implementing an EXPLAIN and EXPLAIN ANALYZE for such commands > may not even be feasible. Plans are different in that they are generated > up-front and we can choose to execute them or not. This is not the same > for the other types of commands being discussed. > > I am not convinced it's worth the effort. > > > thoughts? other approaches? > > You might be interested by this thread "Thinking about EXPLAIN ALTER TABLE": https://www.postgresql.org/message-id/CAM-w4HNm1M5J-ow8UjTcqRe3JPxkVCrGe56tRpPUSePSdGcZ_w%40mail.gmail.com I didn't had the time to read the whole thread, but it might be quite interesting. > > [1] https://oracle-randolf.blogspot.com/2009/02/explain-plan-on-ddls.html > [2] > https://stackoverflow.com/questions/827123/how-can-i-estimate-the-size-of-an-oracle-index > [3] > https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/EXPLAIN-PLAN.html > [4] > https://docs.oracle.com/en/database/oracle/oracle-database/21/arpls/DBMS_SPACE.html#GUID-B6EB7527-BC67-4394-9E7A-01F2790C409A > > Regards, > > Sami > -- Guillaume.
