On Thu, Mar 21, 2013 at 12:30 PM, Daniel Cristian Cruz <
danielcrist...@gmail.com> wrote:
>
>
>> 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
