when do I analyze after concurrent index creation?
Hi,
I have a very basic question.
If I create index concurrently, then do I need to analyze the table? If
yes, when?
Please let me know.
Thanks.
Hi,
When we create an index, i believe the postgres engine it self update it's
catalog about the index availability.
"ANALYZE" helps you to find the right plan according to the number of rows
got selected. I don't think, "ANALYZE" take care of updating the index
entries.
I might be wrong here, hope someone will give you better information.
Thanks,
Dinesh
manojadinesh.blogspot.com
On Fri, Oct 18, 2013 at 1:13 AM, AI Rumman <rummandba@gmail.com> wrote:
Show quoted text
Hi,
I have a very basic question.
If I create index concurrently, then do I need to analyze the table? If
yes, when?
Please let me know.Thanks.
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
Would a simple multi-column index be considered an 'expression' in this context, meaning that an ANALYZE should be issued after 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
--
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 issued after 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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Fri, Oct 18, 2013 at 9:12 PM, 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.
Thank you Tom.
Whether you used CREATE INDEX CONCURRENTLY or some other way of creating
the index doesn't matter at all.regards, tom lane
Regards,
Dinesh
manojadinesh.blogspot.com