strange performance loss - Mailing list pgsql-admin
From | Magnus Harlander |
---|---|
Subject | strange performance loss |
Date | |
Msg-id | 199810280947.KAA00888@umpf.genua.de Whole thread Raw |
List | 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: