index not used afer VACUUM ANALYZE

Hi,
I should state that I use 7.4beta1 under FreeBSD -current.

On Thu, 21 Aug 2003, mike wrote:

Date: Thu, 21 Aug 2003 18:28:53 +0200 (CEST)
From: mike <mike@reifenberger.com>
To: pgsql-bugs@postgresql.org
Subject: index not used afer VACUUM ANALYZE

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?

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

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

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
ANALYZE rather than EXPLAIN say for the various queries?

On Mon, 25 Aug 2003, Stephan Szabo wrote:

Date: Mon, 25 Aug 2003 00:43:56 -0700 (PDT)
From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: mike <mike@Reifenberger.com>
Cc: pgsql-bugs@postgresql.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.

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

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@megazone.bigpanda.com>
To: mike <mike@Reifenberger.com>
Cc: pgsql-bugs@postgresql.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.

On Mon, 25 Aug 2003, Stephan Szabo wrote:

Date: Mon, 25 Aug 2003 08:52:34 -0700 (PDT)
From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: mike <mike@Reifenberger.com>
Cc: pgsql-bugs@postgresql.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@megazone.bigpanda.com>
To: mike <mike@Reifenberger.com>
Cc: pgsql-bugs@postgresql.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.Reifenberger@plaut.de | Priv: Michael@Reifenberger.com
http://www.plaut.de | http://www.Reifenberger.com

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@megazone.bigpanda.com>
To: mike <mike@Reifenberger.com>
Cc: pgsql-bugs@postgresql.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@megazone.bigpanda.com>
To: mike <mike@Reifenberger.com>
Cc: pgsql-bugs@postgresql.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.

Hi,
On Tue, 26 Aug 2003, Stephan Szabo wrote:
...

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.

Ok, with plenty of sort_mem (327680) the seqscan seems to be faster.
Using 64000 shared_buffers it's not that amazing since nothing hits the disks.
As last times aus1.txt is before aus2.txt after the VACUUM ANALYZE.

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

On Wed, 27 Aug 2003, mike wrote:

Hi,
On Tue, 26 Aug 2003, Stephan Szabo wrote:
...

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.

Ok, with plenty of sort_mem (327680) the seqscan seems to be faster.

Eek. That's really big. Was that the swap over point, or was that just
an arbitrary large value. Basically it looks like the difference really
is whether it uses the hash aggregate on the first and third query or not.
The question is figuring out at what point that switches over, and how bad
it is to use that much ram for this query (btw: what does say top/free/ps
say while the query is running for large sort_mem vs small sort_mem)?