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

From: Peter Eisentraut <peter_e(at)gmx(dot)net>
To: "Pavel Stehule" <pavel(dot)stehule(at)hotmail(dot)com>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: slow information schema with thausand users, seq.scan pg_authid
Date: 2006-02-06 13:49:13
Message-ID: 200602061449.13600.peter_e@gmx.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers pgsql-patches

Pavel Stehule wrote:
> 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;

I suggest that your problem is the join order (unless you have 500000
tables as well). Moreover, using left joins instead of inner joins
seems to be quite useless unless you plan to have tables that are not
owned by anyone and are not in a schema.

> there is any possibility creating index for pg_authid?

It already has indexes.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Andrew - Supernews 2006-02-06 14:13:21 Re: slow information schema with thausand users, seq.scan pg_authid
Previous Message Richard Hills 2006-02-06 13:42:55 Re: Shared memory and memory context question

Browse pgsql-patches by date

  From Date Subject
Next Message Andrew - Supernews 2006-02-06 14:13:21 Re: slow information schema with thausand users, seq.scan pg_authid
Previous Message Daniel Verite 2006-02-06 11:15:52 Re: Number format problem