Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
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?
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.
(city_identifier, period) (of which period is a tstzrange). Since both are just columns, the analyze is not needed here as well?
= for city_identifier and && for period in my specific case.
&& 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.
Jun 24, 2019 at 10:08
ANALYZE, see my alternative answer.
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.
CREATE INDEX. From your answer I expected it to pick up the index right after it was created, but apparently an ANALYZE was necessary in my case.
ANALYZE fixed that independently of the index. 🤔 Not sure why though.
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."
