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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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)

Browse pgsql-admin by date

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