Re: slow information schema with thausand users, seq.scan pg_authid

>In the meantime, reducing the LEFT JOIN to pg_roles to a JOIN as per
>Peter's suggestion seems like the best short-term workaround.
>

It's solution

explain analyze 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
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;

Sort (cost=22.68..22.68 rows=2 width=193) (actual time=1.047..1.064 rows=5
loops=1)
Sort Key: n.nspname, c.relname
-> Nested Loop Left Join (cost=1.05..22.67 rows=2 width=193) (actual
time=0.480..0.983 rows=5 loops=1)
Join Filter: ("inner".oid = "outer".relnamespace)
Filter: ("inner".nspname <> ALL ('{pg_catalog,pg_toast}'::name[]))
-> Nested Loop (cost=0.00..21.34 rows=2 width=133) (actual
time=0.386..0.642 rows=5 loops=1)
-> Seq Scan on pg_class c (cost=0.00..9.29 rows=2 width=73)
(actual time=0.334..0.431 rows=5 loops=1)
Filter: ((relkind = ANY ('{S,""}'::"char"[])) AND
pg_table_is_visible(oid))
-> Index Scan using pg_authid_oid_index on pg_authid
(cost=0.00..6.01 rows=1 width=68) (actual time=0.02$
Index Cond: (pg_authid.oid = "outer".relowner)
-> Materialize (cost=1.05..1.10 rows=5 width=68) (actual
time=0.007..0.032 rows=5 loops=5)
-> Seq Scan on pg_namespace n (cost=0.00..1.05 rows=5
width=68) (actual time=0.008..0.028 rows=5 loops=1$ Total runtime: 1.294 ms

Regards
Pavel Stehule

_________________________________________________________________
Najdete si svou lasku a nove pratele na Match.com. http://www.msn.cz/