Thread

1. Query Planning time increased 3 times on 7.1 compared to 7.0.3

   Christof Petig <christof.petig@wtal.de> — 2001-03-05T07:27:39Z

   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.

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

   Christof Petig <christof@petig-baender.de> — 2001-03-05T14:00:47Z

   Justin Clift wrote:
   
   > Hi Christof,
   >
   > I'm not aware of the problem with int2 indexes collapsing.  Can you give
   > me some more info, and I'll put it on the techdocs.postgresql.org
   > website.
   
   Oh, I'm sorry for my strange wording.
   
   I said that the index search collapses to a sequential scan if you do not
   cast the number to int2.
   
   Because an int2 index is not used to look up an int4.
   And untyped numbers are int4 or numeric the int2 index is never used unless
   explicitely specified (by a type cast).
   Yes this is a known bug in PostgreSQL 7.1 and below. Hopefully this will
   get addressed in 7.2?
   Why don't I code it? I'm busy working on ecpg (dyn. SQL) at the moment.
   
   Christof
   
   
   
   

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

   Christof Petig <christof@petig-baender.de> — 2001-03-05T15:07:04Z

   Justin Clift wrote:
   
   > Hi Christof,
   >
   > I'm not aware of the problem with int2 indexes collapsing.  Can you give
   > me some more info, and I'll put it on the techdocs.postgresql.org
   > website.
   
   Oh, I'm sorry for my strange wording.
   
   I said that the index search collapses to a sequential scan if you do
   not
   cast the number to int2.
   
   Because an int2 index is not used to look up an int4.
   And untyped numbers are int4 or numeric the int2 index is never used
   unless
   explicitely specified (by a type cast).
   Yes this is a known bug in PostgreSQL 7.1 and below. Hopefully this will
   get addressed in 7.2?
   Why don't I code it? I'm busy working on ecpg (dyn. SQL) at the moment.
   
   Christof
   
   

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

   Tom Lane <tgl@sss.pgh.pa.us> — 2001-03-05T16:26:38Z

   Christof Petig <christof.petig@wtal.de> writes:
   > 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.
   
   I get the desired plan after doing VACUUM ANALYZE ...
   
   			regards, tom lane
   
   

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

   Christof Petig <christof@petig-baender.de> — 2001-03-07T15:08:47Z

   Tom Lane wrote:
   
   > Christof Petig <christof.petig@wtal.de> writes:
   > > 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.
   >
   > I get the desired plan after doing VACUUM ANALYZE ...
   >
   >                         regards, tom lane
   
   I apologize. I must have been smoking something when I did the vacuum
   analyze. And my nightly script did not work. 7.1 is much faster.
   
   Christof