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

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

From: "Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com>
To: "Kirill Simonov" <xi(at)gamma(dot)dn(dot)ua>
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 15:25:36
Message-ID: 162867790812250725o24e421afxc1b6341650b5886a@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-bugs
Hello

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';

regards
Pavel Stehule

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
>
>
> Thanks,
> Kirill
>

In response to

Responses

pgsql-bugs by date

Next:From: Kirill SimonovDate: 2008-12-25 16:59:04
Subject: Re: BUG #4596: information_schema.table_privileges is way too slow
Previous:From: Pavel StehuleDate: 2008-12-25 14:27:16
Subject: Re: BUG #4596: information_schema.table_privileges is way too slow

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