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 16:59:04
Message-ID: 4953BBD8.8050103@gamma.dn.ua (view raw or flat)
Thread:
Lists: pgsql-bugs
Pavel Stehule wrote:
> 
> really it's should be slow, it's cross join pg_class, pg_authid, pg_authid
> 
> for fast respond you have to specify minimally table_name and grantor fields.
> 
> select * from information_schema.table_privileges where table_name =
> 'foo' and grantor = 'pavel';

Yes, I realize why it's slow.  I'm introspecting the database schema, 
that's why I need the whole contents of "table_privileges".  I suppose I 
could obtain the same data from "pg_class.relacl", but I hoped to do it 
in a portable way.

Thanks,
Kirill


> 2008/12/25, Kirill Simonov <xi(at)gamma(dot)dn(dot)ua>:
>> 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


In response to

Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2008-12-25 23:12:26
Subject: Re: BUG #4596: information_schema.table_privileges is way too slow
Previous:From: Pavel StehuleDate: 2008-12-25 15:25: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