Postgres 12 - under which cirumstances should I activate the query and index statistics collector

Question

TL;DR - I don't have the collector activated (yet) and it seems to not be the default; how bad is this and should I activate it?

• I've just created a new index on a table where a query was slow.

• I've done so by running Explain on the query and creating indices on various combinations of the tables' / queries' columns.

• I forfeited the indices not being used after running Explain again, keeping the one which was used

• Then I decided to check on the statistics gathered for that table. Turns out that SELECT schemaname, relname, last_analyze FROM pg_stat_all_tables WHERE relname = '<MY TABLE>'; was returning NULL as for the last analyze.

• Checking the postgresql.conf I identified that currently I'm not analyzing at all ...

...
#------------------------------------------------------------------------------
# STATISTICS
#------------------------------------------------------------------------------

# - Query and Index Statistics Collector -

#track_activities = on
#track_counts = on
#track_io_timing = off
#track_functions = none         # none, pl, all
#track_activity_query_size = 1024   # (change requires restart)
#stats_temp_directory = 'pg_stat_tmp'
...

My questions

1. Does the query planner still have enough intell to know when to go for an index vs. doing a full-table scan etc.?
2. When should one consider activating the Query and Index Statistics Collector? (Always, only in certain cirumstances, ...)

Answer 1

The statistics collector is already activated, don't worry.

Note that the statistics that the statistics collector gathers are not the table statistics that the optimizer uses, but data about the workload of the database.

The table statistics are collected by autovacuum, which is also enabled by default.

There is a connection between both, in that autovacuum relies on the statistics gathered by the statistics collector to know when it should ANALYZE a table.

Answer 2

Are you confused by the settings in the conf file being commented out? Being commented out doesn't mean it is off, it means it has its compiled-in default value, which is on for track_activities and track_counts. (I would also turn on track_io_timings--it isn't used by the planner, but provides good info for you).

The data in "pg_stat_all_tables" is not very durable. It is lost every time the server crashes or undergoes an immediate shutdown, or pg_stat_reset() is called. Now all of those things should be very rare on a production server, but maybe you aren't using a production server for your testing, and anyway they aren't always as rare as they should be. If you look at pg_stats instead, that will give you a better idea of whether analyze has truly ever been run on the table.