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

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 (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-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

pgsql-hackers by date

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

pgsql-patches by date

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

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