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 usedThen 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
- Does the query planner still have enough intell to know when to go for an index vs. doing a full-table scan etc.?
- When should one consider activating the Query and Index Statistics Collector? (Always, only in certain cirumstances, ...)
track_activity_query_size
is only about the length of the queries reported inpg_stat_activity
it has nothing to do with statistics.track_io_timings
is also not about the optimizer statistics, it's only there to show you I/O timings in the output ofexplain (analyze)