Are you using autovacuum? If so, you probably need to tune it more aggressively. For the short term, running an ANALYSE on those tables should at least get you more accurate query plans.I've done it; with default_statistics_target on 1000, 100 and 200 (left it on 200, which was production config too).
You are doing an manual analyze each time you change default_statistics_target, right?
Can you do an "analyze verbose aula_confirmacao" and see if the output is as expected?
what happens if you do:
explain (analyze, buffers)
select count(*) from aula_confirmacao where
inicio::DATE BETWEEN DATE_TRUNC('YEAR', CURRENT_TIMESTAMP) AND CURRENT_TIMESTAMP;
From your explain plan http://explain.depesz.com/s/GDJn, step 9, the row estimates for that simple query should be off by a factor of 23 (101508/4442), yet there is no apparent reason for that to give a bad estimate, other than bad statistics. There are no filters so cross-column correlations can't be throwing it off, so why is it so bad?
Also, it would be nice to see:
select * from pg_stats where tablename = 'idx_aula_confirmacao_2' \x\g\x
(which I assume is a function-based index)
Cheers,
Jeff
