CODEWITHSUNDEEP
sqlpostgresql
samshers
samshers

Reputation: 3670

Does Postgres "EXPLAIN ANALYZE" also update the statistics tables beside actually running the query

I am using postgres db version 13.5

From experience I am aware that EXPLAIN ANALYZE actually runs the query and gives the actual cost and the actual rows output.

And from postgres doc on ANALYZE command - I read below,

ANALYZE collects statistics about the contents of tables in the database, and stores the results in the pg_statistic system catalog.

So I want to know if EXPLAIN ANALYZE also collects statistics about the contents of tables in the database, and stores the results in the pg_statistic system catalog.'

Upvotes: 0

Views: 1210

Answers (1)

user330315
user330315

Reputation:

The manual for the EXPLAIN command states:

The ANALYZE option causes the statement to be actually executed, not only planned. Then actual run time statistics are added to the display, including the total elapsed time expended within each plan node (in milliseconds) and the total number of rows it actually returned. This is useful for seeing whether the planner's estimates are close to reality.

The manual only lists the things are are done by the commands. Typically things not listed there, are not done (which makes sense because there is whole lot more that this command does not do, then it does).

The part "whether the planner's estimates are close to reality" also indicates that EXPLAIN uses the information from pg_statistic (and other statistics tables in the system catalog), not that it updates them.

So it's pretty safe to say that explain (analyze) will not update the statistics of the involved tables.

This can only be achieved by running the analyze command or vacuum analyze (or waiting for the autovacuum daemon to analyze the tables).

Upvotes: 1

Related Questions