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

Query Planning time increased 3 times on 7.1 compared to 7.0.3

From: Christof Petig <christof(dot)petig(at)wtal(dot)de>
To: PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Query Planning time increased 3 times on 7.1 compared to 7.0.3
Date: 2001-03-05 07:27:39
Message-ID: 3AA33FEB.916C45B6@wtal.de (view raw or flat)
Thread:
Lists: pgsql-hackers
Hello,

We noticed that after upgrading to 7.1beta[245] the execution time for
some often used queries went up by a factor of 2 or more. Considering
the early beta state I was not alarmed. But since I noticed that
yesterday's snapshot still has the problem, I'd really like to tell you
about it.

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

Here is the query:

explain verbose select  gaenge  , s . artikelid  , text   from
schaertabelle s , extartbez e where maschine  = int2(109) and
schaerdatum  = '2001-01-13' and s . artikelid  = e . artikelid  and
extartbezid  = 1 and bezkomptype  = 0   order by text    limit 10;

And the plan for 7.0 and 7.1 (attached).

The data and schema is accessible via
http://home.wtal.de/petig/pg_test.sql.gz

If you omit 'int2(' the index scan collapses into a sequential scan.
(Well known problem with int2 indices)

   Christof

Oh, I'll attach the schema, too. So if you just want to take a look at
the table definition you don't have to download the data.

Attachment: schema
Description: text/plain (1.2 KB)
Attachment: query7.0
Description: text/plain (18.9 KB)
Attachment: query7.1
Description: text/plain (24.5 KB)

Responses

pgsql-hackers by date

Next:From: VicDate: 2001-03-05 08:33:35
Subject: Oops! Its bug in parser????
Previous:From: Patrick DunfordDate: 2001-03-05 07:11:28
Subject: Getting unique ID through SQL

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