Re: Is it worth to optimize VACUUM/ANALYZE by combining duplicate rel instances into single rel instance?

On Sat, Apr 10, 2021 at 8:03 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> Bharath Rupireddy <bharath(dot)rupireddyforpostgres(at)gmail(dot)com> writes:
> > I'm reading the code for vacuum/analyze and it looks like currently we
> > call vacuum_rel/analyze_rel for each relation specified. Which means
> > that if a relation is specified more than once, then we simply
> > vacuum/analyze it that many times. Do we gain any advantage by
> > vacuuming/analyzing a relation back-to-back within a single command? I
> > strongly feel no. I'm thinking we could do a simple optimization here,
>
> This really is not something to expend cycles and code complexity on.
> If the user wrote the same table more than once, that's their choice.

Thanks! I think we could avoid extra processing costs for cases like
VACUUM/ANALYZE foo, foo; when no explicit columns are specified. The
avoided costs can be lock acquire, relation open, vacuum/analyze,
relation close, starting new xact command, command counter increment
in case of analyze etc. This can be done with a simple patch like the
attached. When explicit columns are specified along with relations
i.e. VACUUM/ANALYZE foo(c1), foo(c2); we don't want to do the extra
complex processing to optimize the cases when c1 = c2.

Note that the TRUNCATE command currently skips processing repeated
relations (see ExecuteTruncate). For example, TRUNCATE foo, foo; and
TRUNCATE foo, ONLY foo, foo; first instance of relation foo is taken
into consideration for processing and other relation instances
(options specified if any) are ignored.

Thoughts?

With Regards,
Bharath Rupireddy.
EnterpriseDB: http://www.enterprisedb.com