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

Michael,

Your complete explanation is very helpful!
I appreciate it
Thank you so much!

Regards!

El jue., 30 abr. 2020 a las 10:52, Michael Lewis (<mlewis(at)entrata(dot)com>)
escribió:

> 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.
>

--
Moisés López Calderón
Mobile: (+521) 477-752-22-30
Twitter: @moylop260
hangout: moylop260(at)vauxoo(dot)com
http://www.vauxoo.com - Odoo Gold Partner
Twitter: @vauxoo