Skip site navigation (1) Skip section navigation (2)

Re: Query Planning time increased 3 times on 7.1 compared to 7.0.3

From: Christof Petig <christof(at)petig-baender(dot)de>
To: Zeugswetter Andreas SB <ZeugswetterA(at)wien(dot)spardat(dot)at>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Query Planning time increased 3 times on 7.1 compared to 7.0.3
Date: 2001-03-05 22:17:24
Message-ID: 3AA41073.A1C05FF3@petig-baender.de (view raw or flat)
Thread:
Lists: pgsql-hackers
Zeugswetter Andreas SB wrote:

> > Here is one of the queries, it takes about half a second on our computer
> > (PII 233 with 256MB RAM) to execute and returns typically 1-4 rows via
> > two index scans with high selectivity. So it looks to me that planning
> > time outwages execution time by far. 7.0 took about 0.15
> > seconds (which is still much).
>
> The plans show two different indexes and different statistics for the
> two different versions. No wonder, you see different response times.
>
> Is the "vacuum [analyze]" up to date in both versions ?

I cannot guarantee, that I did vacuum analyze right before I issued the
explain verbose (one is on a busy server, one on a development machine) but I
have seen the _visible_ slowdown of 7.1 compared to 7.0 too often to be a
lack of vacuum analyze.

It seems that in most cases a sort is involved. 0.5 seconds is far too much
for a two row return via two index scans.

But I will try again (empty database, vacuum analyze, issue query) and
report.

What startled me most was that both versions agree that index scan is the
fastest method but it took 0.2 secs on one and 0.5 secs on the other.
The tables do not carry soooo much data.

-------------

Tom Lane wrote:

> I get the desired plan after doing VACUUM ANALYZE ...

Both Versions agree on the best plan, but to me it looks like 7.0 gets this
clue first (in about half/third of the time).
And that unmodified programs take twice of the time with 7.1 _after_ a fresh
db load and analyze is strange.

Christof





In response to

pgsql-hackers by date

Next:From: Tom LaneDate: 2001-03-05 22:30:26
Subject: How to shoot yourself in the foot: kill -9 postmaster
Previous:From: Franck MartinDate: 2001-03-05 22:14:21
Subject: RE: CORBA and PG

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group