14

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?

Kollp
  • 143
  • 1
  • 1
  • 4

3 Answers3

19

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.

Laurenz Albe
  • 61,070
  • 4
  • 55
  • 90
2

This thread is old but the existing answer doesn't look right.

As per https://www.postgresql.org/docs/current/sql-createtable.html

"The autovacuum daemon cannot access and therefore cannot vacuum or analyze temporary tables. For this reason, appropriate vacuum and analyze operations should be performed via session SQL commands. For example, if a temporary table is going to be used in complex queries, it is wise to run ANALYZE on the temporary table after it is populated."

Arabinda
  • 21
  • 1
1

I just added an index to a date column:

CREATE INDEX ON t (col);

I ran a query like

SELECT id
FROM t
WHERE col <= '2021-12-20'
AND col >= '2021-12-20'

The index was not used until I ran ANALYZE t.

This was on Postgres 12.7.

AndreKR
  • 607
  • 9
  • 19