Re: index not used afer VACUUM ANALYZE

On Tue, 26 Aug 2003, mike wrote:

> 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.

But does it change the amount of time the query actually takes to run?
seqscans are not always slower nor are they necessarily the actual problem
here. The problem seems to be choosing a group aggregate + sort which is
taking alot of time, if you look at the real time on the steps below that
it's approximately the same for seqscan or index scan.