Re: Query hitting empty tables taking 48 minutes

> On Jun 8, 2018, at 10:23 AM, David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
>
> ​Not sure what the right answer is but its seems your database (those tables at least) are mis-configured for the workload being ​executed against them. Significantly increasing the aggressiveness of the auto-vacuum process and/or inserting manual vacuum analyze commands into your application at appropriate times are probably necessary.
>

I’m fine with changing up table parameters, which is the option that would make sense for us (thanks for pointing that out). I have the auto vacuum threshold high because of other huge tables, and was not aware of the per table settings. I’ll use this excuse one time, I inherited this setup, now I own it :-)

I’m concerned about a query that’s going against two tables that have had 300k entries in them (ie now empty and 2 entries) taking so long. Even if those tables where full, the query should of taken no time at all. The machine has 64GB memory, 12 physical cores (+12 hyper threads) and the storage is on a ZFS pool with 5 mirrored vdevs of 7.2k SAS drives. The entire db size is 2.63GB, easily fitting into memory. This is a production appliance, and is build to handle the load. Obviously needs some intelligent tuning though.

nspname relname n_tup_ins n_tup_upd n_tup_del n_live_tup n_dead_tup reltuples av_threshold last_vacuum last_analyze av_needed pct_dead
ds3 blob 303498 2559 303496 2 0 2 5000 2018-06-08 04:35:00.000000 NULL false 0
ds3 job_entry 303659 815 303659 0 0 0 5000 2018-06-08 04:35:00.000000 NULL false 0

Best,
Robert