Pavel Stehule wrote:
2008/12/25 Kirill Simonov <x...@gamma.dn.ua>:
Tom Lane wrote:
"Kirill Simonov" <x...@gamma.dn.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
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
