Партиционирование

Функции

CREATE OR REPLACE FUNCTION move_data_month(dd timestamp without time zone)

  RETURNS text AS

$BODY$

DECLARE

     dd ALIAS FOR $1;

     dname text;

     dname1 text;

     rn text;

BEGIN

    select into dname1 extract(month from ($1-interval '1 month'));

    if char_length(dname1) = 1 then

        select into dname1 '0' || extract(month from ($1-interval '1 month'));

    end if;

    DROP TABLE IF EXISTS  payments_log_move;

    CREATE TABLE payments_log_move ( ) INHERITS (payments_log);

    ALTER TABLE payments_log_move NO INHERIT payments_log;

    INSERT INTO payments_log_move select * from payments_log where "time" >= $1 - interval '1 month' AND "time" < $1;

    DELETE FROM payments_log WHERE "time" >= $1 - interval '1 month' AND "time" < $1;

    SELECT INTO dname 'payments_log_y' || extract(year from ($1-interval '1 month')) || 'm' || dname1;

    

    SELECT INTO rn (SELECT count(relname) FROM pg_class WHERE relname = dname);

    if rn = '0' THEN

        EXECUTE 'CREATE TABLE ' || dname || '( ) INHERITS (payments_log)';

    end if;

    EXECUTE 'INSERT INTO ' || dname || ' select * from payments_log_move';

    return 'OK ' || dd || ' <> ' ||dname;

END;

$BODY$

  LANGUAGE plpgsql

;

select move_data_month('2016-01-01'::timestamp);

select * from payments_log_y2013m04;

CREATE OR REPLACE FUNCTION move_data_year(dd timestamp without time zone)

  RETURNS text AS

$BODY$

DECLARE

     dd ALIAS FOR $1;

     dname text;

     dname1 text;

     rn text;

BEGIN

    DROP TABLE IF EXISTS  payments_log_move;

    CREATE TABLE payments_log_move ( ) INHERITS (payments_log);

    ALTER TABLE payments_log_move NO INHERIT payments_log;

    INSERT INTO payments_log_move select * from payments_log where "time" >= $1 - interval '1 year' AND "time" < $1;

    DELETE FROM payments_log WHERE "time" >= $1 - interval '1 year' AND "time" < $1;

    SELECT INTO dname 'payments_log_y' || extract(year from ($1-interval '1 year'));

    SELECT INTO rn (SELECT count(relname) FROM pg_class WHERE relname = dname);

    if rn = '0' THEN

        EXECUTE 'CREATE TABLE ' || dname || '( ) INHERITS (payments_log)';

    end if;

    EXECUTE 'INSERT INTO ' || dname || ' select * from payments_log_move';

    return 'OK ' || dd || ' <> ' ||dname;

END;

$BODY$

  LANGUAGE plpgsql

;

CREATE OR REPLACE FUNCTION index_part_data(year character varying)

  RETURNS text AS

$BODY$

DECLARE

     year ALIAS FOR $1;

     dname text;

     rn text;

BEGIN

    SELECT INTO dname 'payments_log_y' || year;

    SELECT INTO rn (SELECT count(relname) FROM pg_class WHERE relname = dname);

    if rn = '0' THEN

        return 'NO SUCH TABLE ' || dname;

    end if;

    EXECUTE 'CREATE INDEX pl' || year || '_idx_identifier_id_service ON payments_log_y' || year ||

        ' USING btree (identifier COLLATE pg_catalog."default", id_service)';

    EXECUTE 'CREATE INDEX pl' || year || '_idx_pl_external_time ON payments_log_y' || year ||

        ' USING btree (external_time)';

    EXECUTE 'CREATE UNIQUE INDEX pl' || year || '_id_apparat_id_trans_idx ON payments_log_y' || year ||

        ' USING btree (id_apparat, id_trans)';

    EXECUTE 'CREATE INDEX pl' || year || '_id_apparat_idx ON payments_log_y' || year ||

        ' USING btree (id_apparat)';

    EXECUTE 'CREATE INDEX pl' || year || '_id_region_idx ON payments_log_y' || year ||

        ' USING btree (id_region)';

    EXECUTE 'CREATE INDEX pl' || year || '_id_service_idx ON payments_log_y' || year ||

        ' USING btree (id_service)';

    EXECUTE 'CREATE INDEX pl' || year || '_identifier_idx ON payments_log_y' || year ||

        ' USING btree (identifier COLLATE pg_catalog."default")';

    EXECUTE 'CREATE INDEX pl' || year || '_time_app_idx ON payments_log_y' || year ||

        ' USING btree (time_app)';

    EXECUTE 'CREATE INDEX pl' || year || '_time_idx ON payments_log_y' || year ||

        ' USING btree ("time")';

    EXECUTE 'CREATE INDEX pl' || year || '_time_proc_idx ON payments_log_y' || year ||

        ' USING btree (time_proc)';

    EXECUTE 'CREATE UNIQUE INDEX payments_log_y' || year || '_id_idx ON payments_log_y' || year ||

        ' USING btree (id)';

    return 'DONE INDEXES ON ' || dname;

END;

$BODY$

  LANGUAGE plpgsql

;

select index_part_data('2010');

Анализ запросов

1. в базе

create extension pg_stat_statements;

2. в конфиге

shared_preload_libraries = 'pg_stat_statements'

pg_stat_statements.max = 1000

pg_stat_statements.track = all

select total_time/calls as AVG_COST,* from pg_stat_statements where total_time/calls > 10 and calls > 10 order by AVG_COST desc;

Оптимизация

CREATE INDEX idx_events_class_date

ON events

USING btree

(id_class_events, date);

ip-appcheck

CREATE INDEX idx_events_ip_appcheck707 ON events USING btree (id_apparat, id_class_events) WHERE id_class_events=707;

CREATE INDEX idx_events_ip_appcheck708 ON events USING btree (id_apparat, id_class_events) WHERE id_class_events=708;