Thread: when do I analyze after concurrent index creation?

dinesh kumar <dineshkumar02@gmail.com> writes:
> When we create an index, i believe the postgres engine it self update it's
> catalog about the index availability.

ANALYZE normally collects statistics about the contents of table columns.
Thus, adding (or removing) an index does not create any reason to
re-ANALYZE.

However ... if you have an index on an expression (not just a simple
column value), that cues ANALYZE to collect statistics about the values of
that expression.  So re-analyzing is useful after creating such an index,
to give the planner a better idea of when to use that index.

Whether you used CREATE INDEX CONCURRENTLY or some other way of creating
the index doesn't matter at all.

            regards, tom lane

Would a simple multi-column index be considered an 'expression' in this context, meaning that an ANALYZE should be
issuedafter the creation of such an index? 

---
Pete Yunker
Vice President of Data Products
Home Junction, Inc.

On Oct 18, 2013, at 11:42 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> dinesh kumar <dineshkumar02@gmail.com> writes:
>> When we create an index, i believe the postgres engine it self update it's
>> catalog about the index availability.
>
> ANALYZE normally collects statistics about the contents of table columns.
> Thus, adding (or removing) an index does not create any reason to
> re-ANALYZE.
>
> However ... if you have an index on an expression (not just a simple
> column value), that cues ANALYZE to collect statistics about the values of
> that expression.  So re-analyzing is useful after creating such an index,
> to give the planner a better idea of when to use that index.
>
> Whether you used CREATE INDEX CONCURRENTLY or some other way of creating
> the index doesn't matter at all.
>
>             regards, tom lane
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

Pete Yunker <peter.yunker@homejunction.com> writes:
> Would a simple multi-column index be considered an 'expression' in this context, meaning that an ANALYZE should be
issuedafter the creation of such an index? 

No.  Of course, if one of its columns were an expression, then that would
be of interest for ANALYZE.

There has been some talk of collecting column-correlation statistics for
sets of columns listed in multi-column indexes.  So it's possible that in
some future PG version, creating a multi-column index will be a reason to
re-ANALYZE.  But it isn't today.

            regards, tom lane