Allow to specify a index name as ANALYZE parameter
Hackorum builds and tests every patch posted to the lists, not only commitfest submissions. This is Hackorum's own CI rather than the PostgreSQL project's, and it is still under testing - please report anything that looks wrong.
You can run a PostgreSQL built from this patch straight from Docker, with no checkout and no build:
docker run --rm -p 5432:5432 ghcr.io/hackorum-dev/postgres-patch:t39041psql -h localhost -U postgresBuilt from patchset v1 (message #1), July 27, 2026 at 08:07 PM.
Every patchset is also pushed to a branch of our PostgreSQL fork, so you can check out the same tree CI built. Without a PostgreSQL checkout:
git clone --branch t39041_1 https://github.com/hackorum-dev/postgres.gitIn a checkout you already have, add the fork once:
git remote add hackorum https://github.com/hackorum-dev/postgres.gitthen, for this patchset and every later one:
git fetch hackorum t39041_1 && git checkout t39041_1Patchset v1 (message #1) is on t39041_1
Hi,
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!
Regards,
--
Yugo Nagata <nagata@sraoss.co.jp>
Hi!
On Wed, Jul 11, 2018 at 12:04 PM Yugo Nagata <nagata@sraoss.co.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.
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".
------
Alexander Korotkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
On Wed, 11 Jul 2018 14:26:03 +0300
Alexander Korotkov <a.korotkov@postgrespro.ru> wrote:
On Wed, Jul 11, 2018 at 12:04 PM Yugo Nagata <nagata@sraoss.co.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@sraoss.co.jp>
On 11/07/18 11:04, Yugo Nagata wrote:
A usecase I suppose is that when a new expression index is created and that
we need only the statistics for the new index.
I wonder if this shouldn't just be done automatically.
--
Vik Fearing +33 6 46 75 15 36
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
On 7/12/18, Yugo Nagata <nagata@sraoss.co.jp> wrote:
On Wed, 11 Jul 2018 14:26:03 +0300
Alexander Korotkov <a.korotkov@postgrespro.ru> wrote: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.
Hi,
I'm interested in this feature, so I've signed up to help review.
Given the above, I thought it appropriate to mark the patch Waiting on
Author.
-John Naylor
Show quoted text
Thanks,
--
Yugo Nagata <nagata@sraoss.co.jp>
On Sun, Sep 09, 2018 at 01:25:49PM +0700, John Naylor wrote:
I'm interested in this feature, so I've signed up to help review.
Given the above, I thought it appropriate to mark the patch Waiting on
Author.
I find this feature interesting as well. The patch has been waiting on
author input for a couple of weeks unfortunately, so I am marking as
returned with feedback. Could you update the patch?
--
Michael