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

strange performance loss

From: Magnus Harlander <Magnus_Harlander(at)genua(dot)de>
To: pgsql-admin(at)postgresql(dot)org
Cc: Magnus_Harlander(at)genua(dot)de
Subject: strange performance loss
Date: 1998-10-28 09:47:25
Message-ID: 199810280947.KAA00888@umpf.genua.de (view raw or flat)
Thread:
Lists: pgsql-admin
Hi,

I just upgraded from 6.2.1 to 6.3.2 a few days ago. Everything worked fine. 
Today i realized some backends died over night. I found out it was during
a vacuum run. So I restarted the postmaster and rerun a vacuum. Now something
 strange happend. I have a dramatic loss in  performance on my production
database tnt2. I have
two almost identical databases (both reloaded from a 6.2.1 pg_dump) xtest 
for testing and tnt2 for production.
There is a large table (fastindex) with some 100k tuples and a view of
three tables pbf with some 10k tuples. There is an index on one column (key)
in fastindex. I get the following explains on both databases:

tnt2=> explain select distinct p.* from FastIndex i, PBF p where i.Key = 'klueper' and i.Person_Id = p.Person_Id;
NOTICE:  QUERY PLAN:

Unique  (cost=0.00 size=0 width=0)
  ->  Sort  (cost=0.00 size=0 width=0)
        ->  Nested Loop  (cost=0.00 size=1 width=201)
              ->  Seq Scan on p  (cost=0.00 size=0 width=197)
              ->  Index Scan on i  (cost=30.12 size=403 width=4)

EXPLAIN
tnt2=> \c xtest
connecting to new database: xtest
xtest=> explain select distinct p.* from FastIndex i, PBF p where i.Key = 'klueper' and i.Person_Id = p.Person_Id;
NOTICE:  QUERY PLAN:

Unique  (cost=0.00 size=0 width=0)
  ->  Sort  (cost=0.00 size=0 width=0)
        ->  Nested Loop  (cost=0.00 size=1 width=201)
              ->  Seq Scan on p  (cost=0.00 size=0 width=197)
              ->  Index Scan on i  (cost=1.05 size=1 width=4)


There is a factor of 30 difference in the cost of the Index scan on i.key.
It also takes significant longer to select on tnt2 than xtest.

Does anybody have an explanation for this and an idea on how to fix it?

More information follows and it shows that the index in tnt2 contains more
than 1000 pages (relpages) while its 0 in xtest? How can this happen? Is this
the source of my problem?

Thanx Magnus


xtest=> select * from pg_class where relname = 'fastindex';
Field      | Value
-- RECORD 0 --
relname    | fastindex
reltype    | 0
relowner   | 101
relam      | 0
relpages   | 1292
reltuples  | 125247
relhasindex| t
relisshared| f
relkind    | r
relnatts   | 6
relchecks  | 0
reltriggers| 0
relhasrules| f
(1 row)

xtest=> select * from pg_class where relname = 'i_1_fastindex';
Field      | Value
-- RECORD 0 --
relname    | i_1_fastindex
reltype    | 0
relowner   | 101
relam      | 405
relpages   | 0
reltuples  | 125247
relhasindex| f
relisshared| f
relkind    | i
relnatts   | 1
relchecks  | 0
reltriggers| 0
relhasrules| f
(1 row)

tnt2=> select * from pg_class where relname = 'fastindex';
Field      | Value
-- RECORD 0 --
relname    | fastindex
reltype    | 0
relowner   | 101
relam      | 0
relpages   | 1292
reltuples  | 125231
relhasindex| t
relisshared| f
relkind    | r
relnatts   | 6
relchecks  | 0
reltriggers| 0
relhasrules| f
relacl     | {"=arwR"}
(1 row)

Field      | Value
-- RECORD 0 --
relname    | i_1_fastindex
reltype    | 0
relowner   | 101
relam      | 405
relpages   | 1432
reltuples  | 125231
relhasindex| f
relisshared| f
relkind    | i
relnatts   | 1
relchecks  | 0
reltriggers| 0
relhasrules| f
(1 row)



pgsql-admin by date

Next:From: Jan WieckDate: 1998-10-28 19:39:27
Subject: Re: [ADMIN] Security for web server access?
Previous:From: Laurent GachesDate: 1998-10-27 22:21:30
Subject: (no subject)

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