CODEWITHSUNDEEP
postgresqlperformanceindexingquery-performancequery-planner
Fred Hors
Fred Hors

Reputation: 4126

Postgresql, manually use analyze $table_name after each update both on indexes or on structure to update query planner? Really?

I have been trying queries and indexes for two days to find the fastest solution and only today I discover thanks to this answer that using the "analyze $table_name;" command the EXPLAIN and then the query changes completely after adding or removing indexes.

My work consists of this:

  1. use Potgresql 13
  2. use a database that is as similar as possible to the production ones, both in terms of data type and quantity
  3. write a query that will be generated and used by the backend language
  4. try various indices (I am also learning Postgresql in the meantime)
  5. add one index and try the EXPLAIN
  6. I still see the Sequential Scan despite the new index
  7. so since the index is not used I proceed to understand better and find another solution
  8. EXCEPT ONLY TODAY I realized that it was enough to use "analyze $table_name;" to update the Postgresql query planning

QUESTIONS

  1. Is this something I always have to do?

  2. Can I set it automatically so that after each modification on the indexes or on the structure under development it is automatically analyzed?

  3. Is this analysis performed automatically in production?

Upvotes: 0

Views: 988

Answers (1)

jjanes
jjanes

Reputation: 44167

Indexes do not have statistics, they rely on the statistics of the same column(s) from the underlying table. The exception is functional/expressional indexes, which do have their own statistics since they invent new columns not in the underlying table.

Unless the index you created was an expresional index (or your stats were already out of date in the first place), there is no reason to analyze the table after the index creation.

There is no mechanism to automatically do an analyze after the creation of an expressional index. Even if there were, you would probably want to do it manually anyway, as you would like to ensure it has completed before you proceed to testing.

Upvotes: 1

Related Questions