Re: index not used afer VACUUM ANALYZE

On Mon, 25 Aug 2003, mike wrote:

> On Mon, 25 Aug 2003, Stephan Szabo wrote:
>
> > Date: Mon, 25 Aug 2003 00:43:56 -0700 (PDT)
> > From: Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com>
> > To: mike <mike(at)Reifenberger(dot)com>
> > Cc: pgsql-bugs(at)postgresql(dot)org
> > Subject: Re: [BUGS] index not used afer VACUUM ANALYZE
> >
> > On Thu, 21 Aug 2003, mike wrote:
> >
> > > Hi,
> > > I hav a db as specified in nit.sql
> > > flows has 763488 entries.
> > >
> > > After dropping/creating/loading the db and running auswert.sh I get
> > > the attached result from query1.txt.
> > > After 'VACUUM ANALYZE' I get the results from query2.txt
> > >
> > > As you can see, the indexes are not used any longer.
> > > Why?
> >
> > It looks like the row estimates changed to say that a large % of the rows
> > match the condition. Is that true? In any case, what does EXPLAIN
>
> Partially.
> I have statistical records (763488) - various IP-Traffic - collected for one
> month.
> After collection I try to condense the data for dayly statistics.
>
> The EXPLAIN ANALYZE output is attached:
> a1.txt is before, a2.txt after VACUUM ANALYZE run.

There are two things that jump out at me, the first is that the group
aggregate estimates on the after are way higher than reality and that it
looks to me that the sort before the group aggregate is taking longer than
expected. What do you have sort_mem set to since that will affect whether
sorts are in memory and I believe whether it thinks it can use a hash
aggregate on that nubmer of rows.