count(*) в PostgreSQL очень медленная операция. Чтобы её ускорить, применяют разные трюки, например, описанный в Reduce cost of select count(*) queries with trigger-based method. При вставке и удалении строк увеличиваем или уменьшаем значение соответствующего столбца в специальной таблице. Подробности в статье, там же оценка разницы в скорости получения count. Я несколько автоматизировал процесс регистрации таблиц.
#!sql
CREATE TABLE public.table_cnt (table_oid Oid PRIMARY KEY, count int);
CREATE FUNCTION public.count_increment() RETURNS TRIGGER AS $_$
BEGIN
UPDATE public.table_cnt SET count = count + 1 WHERE table_oid = TG_RELID;
RETURN NEW;
END $_$ LANGUAGE 'plpgsql';
CREATE FUNCTION public.count_decrement() RETURNS TRIGGER AS $_$
BEGIN
UPDATE public.table_cnt SET count = count - 1 WHERE table_oid = TG_RELID;
RETURN NEW;
END $_$ LANGUAGE 'plpgsql';
delete from public.table_cnt;
SELECT table_schema,table_name,
'BEGIN WORK;'||
'LOCK TABLE '||table_schema || '.' || table_name||';'||
'INSERT INTO public.table_cnt VALUES (''' ||table_schema ||
'.' || table_name || '''::regclass, (select count(*) from '
||table_schema || '.' || table_name||'));'||
'CREATE TRIGGER ' ||table_schema || '_' || table_name||
'_increment_trig AFTER INSERT ON ' || table_schema ||
'.' || table_name ||
' FOR EACH ROW EXECUTE PROCEDURE count_increment();'||
'CREATE TRIGGER ' ||table_schema || '_' || table_name||
'_decrement_trig AFTER DELETE ON ' || table_schema ||
'.' || table_name ||
' FOR EACH ROW EXECUTE PROCEDURE count_decrement();'||
'COMMIT WORK;'||
'' as sql_query
FROM information_schema.tables
WHERE
table_type = 'BASE TABLE'
and table_schema not like 'information_schema'
and table_schema not like 'pg_catalog'
and (table_schema <>'public' and table_name <>'table_cnt')
ORDER BY table_schema,table_name
Использовать можно, например, так:
#!perl
sub count_for_table {
my $dbh = $shift;
my $schema = shift;
my $table = shift;
# Сначала попробуем получить count из кэша:
my $query = <<END;
SELECT '$schema.$table'::regclass, count
FROM public.table_cnt
WHERE table_oid = '$schema.$table'::regclass
END
my ($sc, $count) = $dbh->selectrow_array($query);
return $count if $sc;
# Если не получилось - таблица не зарегистрирована, - то получаем count естественным путём:
($count) = $dbh->selectrow_array(<<END);
SELECT count(*)
FROM "$schema"."$table"
END
return $count;
}
Leave a Reply
You must be logged in to post a comment.