Have you done a VACUUM ANALYZE FULL on your database? This needs to be done periodically to inform the server of the statistics of how the data and relations are distributed across the database. Without this bad assumptions by the planner can cause degradation of performance.
Autovacuum is enabled. As well, we had problems with autovacum running reliably in 8.2, so we are still running a nightly script that runs VACUUM ANALYZE on the complete database. As for VACUUM ANALYZE FULL, the database underwent a full dump and reload, which, as I understand it, would have rebuilt the indexes, followed by an ANALYZE to update the planner. So I'm not sure a VACUUM ANALYZE FULL would have much effect. I'm also not sure how it bears on the problem stated here, where the planner shouldn't even be looking at this table in the queries we are timing.
Also, if you are using the default settings in postgres.conf then understand those are established to use the absolute minimum amount of resources possible which means not taking advantage of available memory or CPUs that may be available in your environment that would make the database server more performant.
No, we attempted to tune these, using https://pgtune.leopard.in.ua. The following values are from our install script (hence why they don't look exactly like their .conf versions). And, as someone else asked, transparent huge pages are enabled:
# DB Version: 11
# OS Type: linux
# DB Type: web
# Total Memory (RAM): 128 GB
# CPUs = threads per core * cores per socket * sockets
# CPUs num: 256
# Connections num: 250
# Data Storage: ssd
# Set via sysctl
# 64 GB in 4096 byte pages on our 128GB production system
shmall = 15777216
# 48 GB on our 128GB production system
shmmax = 51,539,607,552
# Huge Pages
# Set via sysctl
huge-pages-alloc = 0
shared-buffers = 32GB
work-mem = 1024kB
maintenance-work-mem = 2GB
max-stack-depth = 4MB
effective-io-concurrency = 200
max-parallel-workers-per-gather = 128
max-parallel-workers = 256
#
# postgresql-conf-archive
#
wal-buffers = 16MB
min-wal-size = 1GB
max-wal-size = 2GB
checkpoint-completion-target = 0.7
archive-mode = on
archive-timeout = 900
#
# postgresql-conf-query
#
# 75% of production memory
effective-cache-size = 96GB
# SSD drives
random-page-cost = 1.1
default-statistics-target = 100
I'll be providing further details in reply to another message in the thread.
