problems with access into system catalogs - Mailing list pgsql-admin

Hi,

I have 2 systems with CentOS 5.5 (2.6.18) x86_64, postgresql-9.0.6 64bit

both systems contains the same content. But one system make troubles.
some system tables (eg pg_catalog.pg_class or pg_attribute) contain much
dead rows and all simple query take much time on one system. the other
one is fast.

one problem is this query:
psql MYDB -c "explain analyze SELECT column_name FROM
information_schema.columns WHERE table_schema='public' AND
table_name='person'"

fast system:
QUERY PLAN

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  Nested Loop  (cost=96.39..196.63 rows=1 width=64) (actual
time=10.953..12.785 rows=24 loops=1)
    ->  Hash Left Join  (cost=96.39..196.34 rows=1 width=68) (actual
time=10.937..12.541 rows=24 loops=1)
          Hash Cond: (t.typbasetype = bt.oid)
          Join Filter: (t.typtype = 'd'::"char")
          ->  Nested Loop  (cost=0.00..99.93 rows=1 width=73) (actual
time=0.821..2.359 rows=24 loops=1)
                ->  Nested Loop  (cost=0.00..99.62 rows=1 width=68)
(actual time=0.811..2.097 rows=24 loops=1)
                      Join Filter: (pg_has_role(c.relowner,
'USAGE'::text) OR has_column_privilege(c.oid, a.attnum, 'SELECT, INSERT,
UPDATE, REFERENCES'::text))
                      ->  Nested Loop  (cost=0.00..85.94 rows=1 width=8)
(actual time=0.749..1.904 rows=1 loops=1)
                            Join Filter: (c.relnamespace = nc.oid)
                            ->  Seq Scan on pg_namespace nc
(cost=0.00..4.00 rows=1 width=4) (actual time=0.082..0.502 rows=1 loops=1)
                                  Filter: ((NOT
pg_is_other_temp_schema(oid)) AND
(((nspname)::information_schema.sql_identifier)::text = 'public'::text))
                            ->  Seq Scan on pg_class c (cost=0.00..81.91
rows=3 width=12) (actual time=0.658..1.389 rows=1 loops=1)
                                  Filter: ((c.relkind = ANY
('{r,v}'::"char"[])) AND
(((c.relname)::information_schema.sql_identifier)::text = 'person'::text))
                      ->  Index Scan using
pg_attribute_relid_attnum_index on pg_attribute a  (cost=0.00..13.61
rows=4 width=74) (actual time=0.048..0.103 rows=24 loops=1)
                            Index Cond: ((a.attrelid = c.oid) AND
(a.attnum > 0))
                            Filter: (NOT a.attisdropped)
                ->  Index Scan using pg_type_oid_index on pg_type t
(cost=0.00..0.29 rows=1 width=13) (actual time=0.004..0.005 rows=1 loops=24)
                      Index Cond: (t.oid = a.atttypid)
          ->  Hash  (cost=77.14..77.14 rows=1540 width=4) (actual
time=10.082..10.082 rows=1540 loops=1)
                Buckets: 1024  Batches: 1  Memory Usage: 55kB
                ->  Hash Join  (cost=4.57..77.14 rows=1540 width=4)
(actual time=0.317..7.597 rows=1540 loops=1)
                      Hash Cond: (bt.typnamespace = nbt.oid)
                      ->  Seq Scan on pg_type bt (cost=0.00..51.40
rows=1540 width=8) (actual time=0.003..2.894 rows=1540 loops=1)
                      ->  Hash  (cost=3.14..3.14 rows=114 width=4)
(actual time=0.294..0.294 rows=95 loops=1)
                            Buckets: 1024  Batches: 1  Memory Usage: 4kB
                            ->  Seq Scan on pg_namespace nbt
(cost=0.00..3.14 rows=114 width=4) (actual time=0.004..0.147 rows=95
loops=1)
    ->  Index Scan using pg_namespace_oid_index on pg_namespace nt
(cost=0.00..0.28 rows=1 width=4) (actual time=0.003..0.004 rows=1 loops=24)
          Index Cond: (nt.oid = t.typnamespace)
  Total runtime: 13.130 ms
(29 rows)



but the problematic:
QUERY PLAN

---------------------------------------------------------------------------------------------------------------------------------------------------------------------
  Hash Left Join  (cost=153770.68..475719.06 rows=33 width=64) (actual
time=716.941..1993.033 rows=24 loops=1)
    Hash Cond: (t.typbasetype = bt.oid)
    Join Filter: (t.typtype = 'd'::"char")
    ->  Hash Join  (cost=58705.27..374606.83 rows=33 width=69) (actual
time=373.927..1649.818 rows=24 loops=1)
          Hash Cond: (t.typnamespace = nt.oid)
          ->  Nested Loop  (cost=58700.55..374601.66 rows=33 width=73)
(actual time=373.519..1649.339 rows=24 loops=1)
                ->  Hash Join  (cost=58700.55..374338.49 rows=33
width=68) (actual time=373.496..1649.061 rows=24 loops=1)
                      Hash Cond: (a.attrelid = c.oid)
                      Join Filter: (pg_has_role(c.relowner,
'USAGE'::text) OR has_column_privilege(c.oid, a.attnum, 'SELECT, INSERT,
UPDATE, REFERENCES'::text))
                      ->  Seq Scan on pg_attribute a
(cost=0.00..294729.54 rows=5575330 width=74) (actual
time=0.011..1279.604 rows=8452 loops=1)
                            Filter: ((NOT attisdropped) AND (attnum > 0))
                      ->  Hash  (cost=58700.34..58700.34 rows=17
width=8) (actual time=358.699..358.699 rows=1 loops=1)
                            Buckets: 1024  Batches: 1  Memory Usage: 1kB
                            ->  Nested Loop  (cost=0.00..58700.34
rows=17 width=8) (actual time=0.786..358.693 rows=1 loops=1)
                                  Join Filter: (c.relnamespace = nc.oid)
                                  ->  Seq Scan on pg_namespace nc
(cost=0.00..4.12 rows=1 width=4) (actual time=0.184..0.523 rows=1 loops=1)
                                        Filter: ((NOT
pg_is_other_temp_schema(oid)) AND
(((nspname)::information_schema.sql_identifier)::text = 'public'::text))
                                  ->  Seq Scan on pg_class c
(cost=0.00..58671.00 rows=2018 width=12) (actual time=0.590..358.151
rows=1 loops=1)
                                        Filter: ((c.relkind = ANY
('{r,v}'::"char"[])) AND
(((c.relname)::information_schema.sql_identifier)::text = 'person'::text))
                ->  Index Scan using pg_type_oid_index on pg_type t
(cost=0.00..7.96 rows=1 width=13) (actual time=0.005..0.006 rows=1 loops=24)
                      Index Cond: (t.oid = a.atttypid)
          ->  Hash  (cost=3.21..3.21 rows=121 width=4) (actual
time=0.364..0.364 rows=121 loops=1)
                Buckets: 1024  Batches: 1  Memory Usage: 5kB
                ->  Seq Scan on pg_namespace nt  (cost=0.00..3.21
rows=121 width=4) (actual time=0.009..0.189 rows=121 loops=1)
    ->  Hash  (cost=69682.20..69682.20 rows=1547136 width=4) (actual
time=341.761..341.761 rows=1569 loops=1)
          Buckets: 131072  Batches: 2  Memory Usage: 29kB
          ->  Hash Join  (cost=4.72..69682.20 rows=1547136 width=4)
(actual time=0.370..339.147 rows=1569 loops=1)
                Hash Cond: (bt.typnamespace = nbt.oid)
                ->  Seq Scan on pg_type bt  (cost=0.00..48404.36
rows=1547136 width=8) (actual time=0.005..334.474 rows=1569 loops=1)
                ->  Hash  (cost=3.21..3.21 rows=121 width=4) (actual
time=0.342..0.342 rows=121 loops=1)
                      Buckets: 1024  Batches: 1  Memory Usage: 5kB
                      ->  Seq Scan on pg_namespace nbt (cost=0.00..3.21
rows=121 width=4) (actual time=0.004..0.162 rows=121 loops=1)
  Total runtime: 1993.769 ms
(33 rows)



I tried to cluster, reindex and vacuum (+full) pg_class without success
psql MYDB -c "vacuum verbose pg_catalog.pg_class"

fast system:
INFO:  "pg_class": found 1 removable, 2106 nonremovable row versions in
45 out of 45 pages
DETAIL:  0 dead row versions cannot be removed yet.

other:
INFO:  "pg_class": found 0 removable, 1547667 nonremovable row versions
in 31587 out of 31587 pages
DETAIL:  1545530 dead row versions cannot be removed yet.

a "select count(*) from pg_catalog.pg_class" returns only 2137

how can i remove then dead rows and fasten acccess to system catalogs?


Thomas