1

In my transaction, I am creating a temporary table:

create temporary table x on commit drop as
select ...

I also add an index on that table:

create index on x(some_column);

Is it now necessary to run analyze on that table? Or do I only need to analyze the table for updates/deletes after index creation?

In other words, my question is: does the creation of an index already imply an analyze execution?

2

If the index is just on simple columns like in your case, it is not necessary to ANALYZE the table after you create the index.
That is because statistics on the value distribution of the table columns are always collected, no matter if there is an index on the column or not.

However, if you are indexing an expression like upper(some_column) or (CAST(some_column AS date)), you should run ANALYZE after creating the index.
PostgreSQL will then also collect statistics on the value distribution of the indexed expression. This happens automatically whenever autoanalyze runs, but it is a good idea to do it manually right after creating the index so you have good statistics right away.

  • Thank you, that makes sense to me. Does this knowledge hold for any kind of index type (BTREE, GIN, GIST, ...)? In my specific case, I have a GIST index on (city_identifier, period) (of which period is a tstzrange). Since both are just columns, the analyze is not needed here as well? – Kollp Jun 24 at 10:01
  • This is also true for GIN and GiST indexes. I am not sure if it holds for all index access methods. – Laurenz Albe Jun 24 at 10:05
  • Regarding the access methods, it is = for city_identifier and && for period in my specific case. – Kollp Jun 24 at 10:06
  • An index access method is something like GIN or GiST. && is an operator. Try it yourself and see if you find statistics in pg_stats. But yes, it is like I wrote in my answer. – Laurenz Albe Jun 24 at 10:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.