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

[Kirill Simonov <xi(at)gamma(dot)dn(dot)ua>]

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