Re: index not used afer VACUUM ANALYZE

On Mon, 25 Aug 2003, Stephan Szabo wrote:

> Date: Mon, 25 Aug 2003 08:52:34 -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 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.
>

sort_men was at the default.
But setting it to 10240 doesn't seem to change the seqscan on flows.

Bye/2
---
Michael Reifenberger, Business Unit Manager SAP-Basis, Plaut Consulting
Comp: Michael(dot)Reifenberger(at)plaut(dot)de | Priv: Michael(at)Reifenberger(dot)com
http://www.plaut.de | http://www.Reifenberger.com