Em 28 de julho de 2010 15:33, Monica Ferrari Villarino
<[email protected]> escreveu:
> Será que é possível otimizar a seguinte consulta, executada de hora em hora
> no banco:
>
> select count(*) from history;
>
> Essa consulta costuma ter uma duração que varia de 32000.000 ms a 62262.751
> ms conforme o horário em que é executada.
>
> A tabela history possui em média 87 milhões de registros.
>
> É uma tabela que sofre muito insert/update/delete.
>
> (corte)
>
Já precisei de algo parecido em mais de uma tabela (auditoria), e para
reduzir ao máximo os problemas de desempenho foi criada uma tabela de
contadores que é atualizada a cada operação.
Adaptei o script para a sua tabela, espero que possa ajudá-la.
-- INÍCIO DO SCRIPT
-- ***************************
-- PARA A COLUNA counter_type:
-- ***************************
-- 'ROW' = Número de Linhas, 'DEL' = Delete, 'UPD' = Update, 'INS' = Insert
CREATE TABLE table_counter (
table_name VARCHAR(100) not null,
counter_type CHAR(3) not null,
counter_value INTEGER not null default 0
);
--
ALTER TABLE table_counter ADD PRIMARY KEY( table_name, counter_type );
--
-- Adiciona pela primeira vez o total de registros na tabela
--
INSERT INTO table_counter( table_name, counter_type, counter_value )
VALUES( 'history', 'ROW', ( SELECT COUNT(*) FROM history ) );
--
CREATE FUNCTION trf_ins_history_count()
RETURNS TRIGGER AS
$BODY$
DECLARE
_RS RECORD;
i_count INTEGER;
BEGIN
SELECT INTO _RS counter_value FROM table_counter WHERE table_name =
'history' AND counter_type = 'INS';
IF NOT FOUND THEN
INSERT INTO table_counter( table_name, counter_type,
counter_value )
VALUES( 'history', 'INS', 0 );
i_count := 1;
ELSE
i_count := _RS.counter_value;
i_count := i_count + 1;
END IF;
-- Atualiza a quantidade de INSERTs que a tabela recebeu
UPDATE table_counter SET counter_value = i_count WHERE table_name =
'history' AND counter_type = 'INS';
-- Atualiza o número de registros
-- Deve existir um TRIGGER para DELETE que remova uma unidade a cada
registro eliminado.
UPDATE table_counter SET counter_value = counter_value +1 WHERE
table_name = 'history' AND counter_type = 'ROW';
RETURN NEW;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
--
CREATE TRIGGER tr_ins_history AFTER INSERT ON history FOR EACH ROW
EXECUTE PROCEDURE trf_ins_history_count();
--
CREATE FUNCTION trf_upd_history_count()
RETURNS TRIGGER AS
$BODY$
DECLARE
_RS RECORD;
i_count INTEGER;
BEGIN
SELECT INTO _RS counter_value FROM table_counter WHERE table_name =
'history' AND counter_type = 'UPD';
IF NOT FOUND THEN
INSERT INTO table_counter( table_name, counter_type,
counter_value )
VALUES( 'history', 'UPD', 0 );
i_count := 1;
ELSE
i_count := _RS.counter_value;
i_count := i_count + 1;
END IF;
-- Atualiza a quantidade de UPDATES que a tabela recebeu
UPDATE table_counter SET counter_value = i_count WHERE table_name =
'history' AND counter_type = 'UPD';
-- NÃO Atualiza o número de registros
RETURN NEW;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
--
CREATE TRIGGER tr_upd_history AFTER UPDATE ON history FOR EACH ROW
EXECUTE PROCEDURE trf_upd_history_count();
--
CREATE FUNCTION trf_del_history_count()
RETURNS TRIGGER AS
$BODY$
DECLARE
_RS RECORD;
i_count INTEGER;
BEGIN
SELECT INTO _RS counter_value FROM table_counter WHERE table_name =
'history' AND counter_type = 'DEL';
IF NOT FOUND THEN
INSERT INTO table_counter( table_name, counter_type,
counter_value )
VALUES( 'history', 'DEL', 0 );
i_count := 1;
ELSE
i_count := _RS.counter_value;
i_count := i_count + 1;
END IF;
-- Atualiza a quantidade de INSERTs que a tabela recebeu
UPDATE table_counter SET counter_value = i_count WHERE table_name =
'history' AND counter_type = 'DEL';
-- Atualiza o número de registros
UPDATE table_counter SET counter_value = counter_value -1 WHERE
table_name = 'history' AND counter_type = 'ROW';
RETURN NEW;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
--
CREATE TRIGGER tr_del_history AFTER DELETE ON history FOR EACH ROW
EXECUTE PROCEDURE trf_del_history_count();
-- FIM DO SCRIPT
--
TIAGO J. ADAMI
http://www.adamiworks.com
_______________________________________________
pgbr-geral mailing list
[email protected]
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
