Re: Allow to specify a index name as ANALYZE parameter

On Wed, 11 Jul 2018 14:26:03 +0300
Alexander Korotkov <a(dot)korotkov(at)postgrespro(dot)ru> wrote:

> On Wed, Jul 11, 2018 at 12:04 PM Yugo Nagata <nagata(at)sraoss(dot)co(dot)jp> wrote:
> > When we specify column names for ANALYZE, only the statistics for those columns
> > are collected. Similarly, is it useful if we have a option to specify an index
> > for ANALYZE to collect only the statistics for expression in the specified index?
> >
> > A usecase I suppose is that when a new expression index is created and that
> > we need only the statistics for the new index. Although ANALYZE of all the indexes
> > is usually fast because ANALYZE uses a random sampling of the table rows, ANALYZE
> > on the specific index may be still useful if there are other index whose "statistics
> > target" is large and/or whose expression takes time to compute, for example.
> >
> > Attached is the WIP patch to allow to specify a index name as ANALYZE parameter.
> > Any documatation is not yet included. I would appreciate any feedback!
>
> I think this makes sense. Once we can collect statistics individually
> for regular columns, we should be able to do the same for indexed
> expression. Please, register this patch on the next commitfest.

Thank you for your comment! I registered this to CF 2018-09.

> Regarding current implementation I found message "ANALYZE option must
> be specified when a column list is provided" to be confusing.
> Perhaps, you've missed some negation in this message, since in fact
> you disallow analyze with column list.
>
> However, since multicolumn index may contain multiple expression, I
> think we should support specifying columns for ANALYZE index clause.
> We could support specifying columns by their numbers in the same way
> we did for "ALTER INDEX index ALTER COLUMN colnum SET STATISTICS
> number".

Make sense. I'll fix the patch to support specifying columns of index.

Thanks,

--
Yugo Nagata <nagata(at)sraoss(dot)co(dot)jp>