Re: The query plan get all columns but I'm using only one column.

>
> In staging environment we have disabled autovacuum since that it is a
> testing environment and the database are restored very often.
> But in production environment it is enabled autovacuum=on
>
> The weird case is that production was slow and staging environment was
> faster.
>

You haven't specified how you are doing backup and restore, but unless it
is a byte-for-byte file copy method, then there would be no bloat on the
restored staging environment so no need to vacuum. You would want to ensure
you take a new statistics sample with analyze database after restore if you
aren't.

In your production system, if your configs for autovacuum settings have not
been changed from the default parameters, it probably is not keeping up at
all if the system is moderately high in terms of update/delete
transactions. You can check pg_stat_activity for active vacuums, change the
parameter to log autovacuums longer than X to 0 and review the logs, or
check pg_stat_user_tables to see how many autovacuums/analyze have been
done since you last reset those stats.

If you have tables that are in the millions or hundreds or millions of
rows, then I would recommend decreasing autovacuum_vacuum_scale_factor from
20% down to 1% or perhaps less and similar
for autovacuum_analyze_scale_factor. You can do this on individual tables
if you have mostly small tables and just a few large ones. Else, increase
the threshold settings as well. The default value
for autovacuum_vacuum_cost_delay changed from 20ms to 2ms in PG12 so that
may also be prudent to do likewise if you upgraded to PG12 and kept your
old settings, assuming your I/O system can handle it.

Otherwise, if you have a period of time when the activity is low for your
database(s), then a last resort can be a daily scheduled vacuum analyze on
all tables. Note- do not do vacuum FULL which requires an exclusive lock on
the table to re-write it entirely. You are just looking to mark space
re-usable for future transactions, not recover the disk space back to the
OS to be consumed again if autovacuum still can't keep up. pg_repack
extension would be an option if you need to recover disk space while online.