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

slow information schema with thausand users, seq.scan pg_authid

From: "Pavel Stehule" <pavel(dot)stehule(at)hotmail(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: slow information schema with thausand users, seq.scan pg_authid
Date: 2006-02-06 09:58:43
Message-ID: BAY20-F12D9A6A033C304A8F46782F90E0@phx.gbl (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-patches
Hello,

I know so db 500 000 users isn't normal situation, but I need it. After 
user's generation all selects on system's catalog are slow. For example: 
list of sequences

SELECT n.nspname as "Schema",
  c.relname as "Name",
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 
'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as "Type",
  r.rolname as "Owner"
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_roles r ON r.oid = c.relowner
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('S','')
      AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
      AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;

                                                               QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Sort  (cost=47532.09..47544.59 rows=5000 width=193) (actual 
time=30333.490..30333.504 rows=5 loops=1)
   Sort Key: n.nspname, c.relname
   ->  Hash Left Join  (cost=1.06..46947.04 rows=5000 width=193) (actual 
time=45.918..30333.390 rows=5 loops=1)
         Hash Cond: ("outer".relnamespace = "inner".oid)
         Filter: ("inner".nspname <> ALL ('{pg_catalog,pg_toast}'::name[]))
         ->  Nested Loop Left Join  (cost=0.00..46795.97 rows=5000 
width=133) (actual time=28.648..30316.020 rows=5 loops=1)
               Join Filter: ("inner".oid = "outer".relowner)
               ->  Seq Scan on pg_class c  (cost=0.00..9.59 rows=2 width=73) 
(actual time=16.212..165.521 rows=5 loops=1)
                     Filter: ((relkind = ANY ('{S,""}'::"char"[])) AND 
pg_table_is_visible(oid))
               ->  Seq Scan on pg_authid  (cost=0.00..12143.06 rows=500006 
width=118) (actual time=12.702..4306.537 rows=500006 loops=5)
         ->  Hash  (cost=1.05..1.05 rows=5 width=68) (actual 
time=0.070..0.070 rows=5 loops=1)
               ->  Seq Scan on pg_namespace n  (cost=0.00..1.05 rows=5 
width=68) (actual time=0.013..0.035 rows=5 loops=1)
Total runtime: 30376.547 ms

there is any possibility creating index for pg_authid?

best regards
Pavel Stehule

_________________________________________________________________
Chcete sdilet sve obrazky a hudbu s prateli? http://messenger.msn.cz/


Responses

pgsql-hackers by date

Next:From: lrotgerDate: 2006-02-06 10:05:05
Subject: Actual expression of a constraint
Previous:From: andrewDate: 2006-02-06 09:24:26
Subject: Re: look up tables while parsing queries

pgsql-patches by date

Next:From: Daniel VeriteDate: 2006-02-06 11:15:52
Subject: Re: Number format problem
Previous:From: ITAGAKI TakahiroDate: 2006-02-06 04:27:21
Subject: TODO-Item: B-tree fillfactor control

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