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/
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
