Re: ANALYZE ONLY - Mailing list pgsql-hackers

Thank you all for the replies & discussion.

It sounds like more are in favour of using the ONLY syntax attached to
the tables is the best way to go, with the main advantages being:
 - consistency with other commands
 - flexibility in allowing to specify whether to include partitions
for individual tables when supplying a list of tables

I will start working on an implementation along those lines. It looks
like we can simply replace qualified_name with relation_expr in the
production for vacuum_relation within gram.y.

One other thing I noticed when reading the code. The function
expand_vacuum_rel in vacuum.c seems to be responsible for adding the
partitions. If I am reading it correctly, it only adds child tables in
the case of a partitioned table, not in the case of an inheritance
parent:

        include_parts = (classForm->relkind == RELKIND_PARTITIONED_TABLE);
..
        if (include_parts)
        {
        .. add partitions ..

This is a little different to some other contexts where the ONLY
keyword is used, in that ONLY would be the default and only available
mode of operation for an inheritance parent.

Regards,
Mike

On Wed, 21 Aug 2024 at 20:04, Melih Mutlu <m.melihmutlu@gmail.com> wrote:
>
> David Rowley <dgrowleyml@gmail.com>, 21 Ağu 2024 Çar, 01:53 tarihinde şunu yazdı:
>>
>> On Wed, 21 Aug 2024 at 06:41, Robert Haas <robertmhaas@gmail.com> wrote:
>> > I like trying to use ONLY somehow.
>>
>> Do you mean as an ANALYZE command option, i.e. ANALYZE (only) table;
>> or as a table modifier like gram.y's extended_relation_expr?
>>
>> Making it a command option means that the option would apply to all
>> tables listed, whereas if it was more like an extended_relation_expr,
>> the option would be applied per table listed in the command.
>>
>> 1. ANALYZE ONLY ptab, ptab2; -- gather stats on ptab but not on its
>> partitions but get stats on ptab2 and stats on its partitions too.
>> 2. ANALYZE ONLY ptab, ONLY ptab2; -- gather stats on ptab and ptab2
>> without doing that on any of their partitions.
>
>
> I believe we should go this route if we want this to be called "ONLY" so that it would be consistent with other
placestoo. 
>
>> Whereas: "ANALYZE (ONLY) ptab, ptab2;" would always give you the
>> behaviour of #2.
>
>
> Havin it as an option would be easier to use when we have several partition tables. But I agree that if we call it
"ONLY", it may be confusing and the other approach would be appropriate. 
>
>>
>> If we did it as a per-table option, then we'd need to consider what
>> should happen if someone did: "VACUUM ONLY parttab;". Probably
>> silently doing nothing wouldn't be good. Maybe a warning, akin to
>> what's done in:
>>
>> postgres=# analyze (skip_locked) a;
>> WARNING:  skipping analyze of "a" --- lock not available
>
>
> +1 to raising a warning message in that case instead of staying silent. We might also not allow ONLY if ANALYZE is
notpresent in VACUUM query and raise an error. But that would require changes in grams.y and could complicate things.
Soit may not be necessary and we may be fine with just a warning. 
>
> Regards,
> --
> Melih Mutlu
> Microsoft