Re: improve DEBUG1 logging of parallel workers for CREATE INDEX?

Le lun. 20 janv. 2025 à 04:28, Sami Imseih <samimseih(at)gmail(dot)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.1736369905@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.