6

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 Answers 2

8

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.

5
  • 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, 2019 at 10:01
  • This is also true for GIN and GiST indexes. I am not sure if it holds for all index access methods. Jun 24, 2019 at 10:05
  • Regarding the access methods, it is = for city_identifier and && for period in my specific case.
    – Kollp
    Jun 24, 2019 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. Jun 24, 2019 at 10:08
  • There must be other conditions to make it work without ANALYZE, see my alternative answer.
    – AndreKR
    Dec 20, 2021 at 5:14
0

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.

4
  • That seems unrelated. You probably didn't insert enough rows to trigger autoanalyze, or you didn't give it enough time to complete. Dec 20, 2021 at 6:45
  • @LaurenzAlbe Not sure what you mean. The table is old and already contains a million rows or so. I tried the query immediately after the 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.
    – AndreKR
    Dec 20, 2021 at 20:57
  • No, that is not how PostgreSQL works. Statistics on columns are collected whether an index exists or not, and the index is used as soon as it is created. Your table statistics must have been off for reasons that can probably no longer be figured out. Dec 21, 2021 at 8:05
  • @LaurenzAlbe Oh, I didn't consider the possibility that my column statistics already were off and the ANALYZE fixed that independently of the index. 🤔 Not sure why though.
    – AndreKR
    Dec 21, 2021 at 20:13

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.