|
|
| Строка 1: |
Строка 1: |
| = Функции =
| | [https://www.wolf-a.ru Вольф Александр] |
| <syntaxhighlight lang="plsql">
| |
| 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
| |
| | |
| ;
| |
| </syntaxhighlight>
| |
| | |
| select move_data_month('2016-01-01'::timestamp);
| |
| | |
| select * from payments_log_y2013m04;
| |
| | |
| <syntaxhighlight lang="plsql">
| |
| 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
| |
| | |
| ;
| |
| </syntaxhighlight>
| |
| | |
| <syntaxhighlight lang="plsql">
| |
| 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
| |
| | |
| ;
| |
| </syntaxhighlight>
| |
| | |
| select index_part_data('2010');
| |
| | |
| = Анализ запросов =
| |
| 1. в базе
| |
| | |
| create extension pg_stat_statements;
| |
| | |
| 2. в конфиге
| |
| | |
| <syntaxhighlight lang="text">
| |
| shared_preload_libraries = 'pg_stat_statements'
| |
| | |
| pg_stat_statements.max = 1000
| |
| | |
| 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;
| |
| </syntaxhighlight>
| |
| | |
| = Оптимизация =
| |
| <syntaxhighlight lang="sql">
| |
| CREATE INDEX idx_events_class_date
| |
| | |
| ON events
| |
| | |
| USING btree
| |
| | |
| (id_class_events, date);
| |
| </syntaxhighlight>
| |
| | |
| 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_appcheck708 ON events USING btree (id_apparat, id_class_events) WHERE id_class_events=708;
| |
| </syntaxhighlight>
| |
| | |
| [[Категория:Работа]]
| |