Партиционирование: различия между версиями

Материал из Home Wiki
Перейти к навигации Перейти к поиску
м
(Новая страница: «=== Функции === <syntaxhighlight lang="plsql"> CREATE OR REPLACE FUNCTION move_data_month(dd timestamp without time zone)   RETURNS text AS $BODY$ DECL…»)
(не показаны 3 промежуточные версии этого же участника)
Строка 1: Строка 1:
[[Категория:Работа]]
=== Функции ===
 
= Функции =
<syntaxhighlight lang="plsql">
<syntaxhighlight lang="plsql">
CREATE OR REPLACE FUNCTION move_data_month(dd timestamp without time zone)
CREATE OR REPLACE FUNCTION move_data_month(dd timestamp without time zone)
Строка 118: Строка 116:


;
;
</syntaxhighlight>
</syntaxhighlight><syntaxhighlight lang="plsql">
 
<syntaxhighlight lang="plsql">
CREATE OR REPLACE FUNCTION index_part_data(year character varying)
CREATE OR REPLACE FUNCTION index_part_data(year character varying)


Строка 204: Строка 200:
select index_part_data('2010');
select index_part_data('2010');


= Анализ запросов =
=== Анализ запросов ===
1. в базе
1. в базе


Строка 211: Строка 207:
2. в конфиге
2. в конфиге


<syntaxhighlight lang="text">
shared_preload_libraries = 'pg_stat_statements'
shared_preload_libraries = 'pg_stat_statements'


Строка 217: Строка 212:


pg_stat_statements.track = all
pg_stat_statements.track = all
</syntaxhighlight>


<syntaxhighlight lang="sql">
select total_time/calls as AVG_COST,* from pg_stat_statements where total_time/calls > 10 and calls > 10 order by AVG_COST desc;
select total_time/calls as AVG_COST,* from pg_stat_statements where total_time/calls > 10 and calls > 10 order by AVG_COST desc;
</syntaxhighlight>


= Оптимизация =
=== Оптимизация ===
<syntaxhighlight lang="sql">
CREATE INDEX idx_events_class_date
CREATE INDEX idx_events_class_date


Строка 232: Строка 223:


(id_class_events, date);
(id_class_events, date);
</syntaxhighlight>


ip-appcheck
ip-appcheck
<syntaxhighlight lang="sql">
 
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_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;
CREATE INDEX idx_events_ip_appcheck708 ON events USING btree (id_apparat, id_class_events) WHERE id_class_events=708;
</syntaxhighlight>
[[Категория:Работа]]

Версия 05:00, 10 января 2017

Функции

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;