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

Re: BUG #4596: information_schema.table_privileges is way too slow

From: Kirill Simonov <xi(at)gamma(dot)dn(dot)ua>
To: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #4596: information_schema.table_privileges is way too slow
Date: 2008-12-25 13:36:48
Message-ID: 49538C70.5030403@gamma.dn.ua (view raw or flat)
Thread:
Lists: pgsql-bugs
Pavel Stehule wrote:
> 2008/12/25 Kirill Simonov <xi(at)gamma(dot)dn(dot)ua>:
>> Tom Lane wrote:
>>> "Kirill Simonov" <xi(at)gamma(dot)dn(dot)ua> writes:
>>>> It takes about 5 minutes to perform the query
>>>> SELECT * FROM information_schema.table_privileges
>>>> on an empty database (i.e. with system tables only).
>>> Not here.  What non-default settings might you be using?
>>>
>> Indeed, it is slow because there are a lot of rows in pg_authid (about 700).
>>  Is there a possibility to make table_privileges faster with a large number
>> of roles?
>>
>> Thanks,
>> Kirill
> 
> two years ago I tested 50000 users without problems. Try to vacuum and
> reindex your system tables
> 

Neither VACUUM nor REINDEX SYSTEM did help.  The problem could be 
reproduced on a freshly installed Postgres:

-- add a function to generate dummy roles.
create language plpgsql;
create function create_dummy_role(start int, finish int) returns void as $$
begin
     for i in start..finish loop
         execute 'create role dummy_' || cast(i as text);
     end loop;
end;
$$ language plpgsql;

-- no extra roles
select count(*) from information_schema.table_privileges;
 >>> Time: 11.467 ms

-- 10 roles
select create_dummy_role(1, 10);
select count(*) from information_schema.table_privileges;
 >>> Time: 161.539 ms

-- 100 roles
select create_dummy_role(11, 100);
select count(*) from information_schema.table_privileges;
 >>> Time: 7807.675 ms

-- 1000 roles
select create_dummy_role(101, 1000);
select count(*) from information_schema.table_privileges;
 >>> Time: 543030.948 ms


Thanks,
Kirill

In response to

Responses

pgsql-bugs by date

Next:From: Pavel StehuleDate: 2008-12-25 14:27:16
Subject: Re: BUG #4596: information_schema.table_privileges is way too slow
Previous:From: Pavel StehuleDate: 2008-12-25 10:53:36
Subject: Re: BUG #4596: information_schema.table_privileges is way too slow

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