пятница, 13 ноября 2009 г.

Оптимизация данных - Разделенные таблицы

Работа с очень большими таблицами происходит медленнее, чем с небольшими. Потому полезно создавать так называемые разделенные таблицы. В таких таблицах данные физически размещаются в нескольких таблицах-потомках, но запросы на изменение/выборку данных обращаются только к одной родительской таблице. Для этого нужно создать одну родительскую таблицу, необходимое число наследующих от нее все поля (или не все, но это редкость) подтаблиц (дочерних таблиц) с указанными ограничениями и написать набор правил (rule). Например, можно создавать подтаблицу на каждый календарный месяц, тогда месячные отчеты будут обращаться лишь к одной таблице (бывают исключения, когда требуется обрабатывать и старые данные, но это уже повод создавать некоторые дополнительные таблицы и строить отчеты уже по ним). Особенно хорош этот путь в тех случаях, когда объем индексов превосходит объем самих данных. При запросе к разделенной таблице планировщик запроса обратится только к тем подтаблицам, в которых могут находиться нужные данные, соответственно и индексы будут прочитаны только те, которые созданы для выбранных подтаблиц.

Теорию Вы можете прочитать здесь, а ниже мы рассмотрим, как на практике использовать данный метод.

Для того, чтобы использовать разделенные таблицы следует настроить следующий параметр в файле настроек нужного кластера PostgreSQL:

constraint_exclusion = on

В версии 8.1 этот параметр отключен (off), в версии 8.2 и выше не знаю, так что проверьте. После изменения значения параметра перезапустите кластер.

Вот пример родительской таблицы и 3 правила, которые работают при вставке данных в таблицу. Если в эту таблицу вставить данные за январь 2006-го года, сработает правило out_insert_out_yy06mm01 и данные будут записаны в таблицу data.out_yy06mm01. Для данных за февраль сработает правило out_insert_out_yy06mm01 и данные попадут в таблицу data.out_yy06mm01... Главное, чтоб существовали нужные правила на главную таблицу и нужные подтаблицы. Если подходящее правило не будет найдено, данные попадут в саму таблицу data."out", что скорее всего не входит в Ваши планы. Потому будьте внимательны!

CREATE TABLE data."out"
(
save_date timestamp NOT NULL DEFAULT now(),
user_id int4 NOT NULL DEFAULT 0,
object_id int8 NOT NULL,
question_id int8 NOT NULL,
answer_id int8 NOT NULL,
text text NOT NULL
)
WITHOUT OIDS;

CREATE OR REPLACE RULE out_insert_out_yy06mm01 AS
ON INSERT TO data."out"
WHERE new.save_date >= '2006-01-01'::date::timestamp without time zone
AND new.save_date < ('2006-01-01'::date + '1 mon'::interval)
DO INSTEAD
INSERT INTO data.out_yy06mm01
(save_date, user_id, object_id, question_id, answer_id, text)
VALUES
(new.save_date, new.user_id, new.object_id, new.question_id,
new.answer_id, new.text);

CREATE OR REPLACE RULE out_insert_out_yy06mm02 AS
ON INSERT TO data."out"
WHERE new.save_date >= '2006-02-01'::date::timestamp without time zone
AND new.save_date < ('2006-02-01'::date + '1 mon'::interval)
DO INSTEAD
INSERT INTO data.out_yy06mm02
(save_date, user_id, object_id, question_id, answer_id, text)
VALUES
(new.save_date, new.user_id, new.object_id, new.question_id,
new.answer_id, new.text);

А вот определения подтаблиц:

CREATE TABLE data.out_yy06mm01
(
-- Inherited: save_date timestamp NOT NULL DEFAULT now(),
-- Inherited: user_id int4 NOT NULL DEFAULT 0,
-- Inherited: object_id int8 NOT NULL,
-- Inherited: question_id int8 NOT NULL,
-- Inherited: answer_id int8 NOT NULL,
-- Inherited: text text NOT NULL,
CONSTRAINT "$1" CHECK (
save_date >= '2006-01-01'::date::timestamp without time zone
AND
save_date < ('2006-01-01'::date + '1 mon'::interval))
) INHERITS (data."out")
WITHOUT OIDS;

CREATE TABLE data.out_yy06mm02
(
-- Inherited: save_date timestamp NOT NULL DEFAULT now(),
-- Inherited: user_id int4 NOT NULL DEFAULT 0,
-- Inherited: object_id int8 NOT NULL,
-- Inherited: question_id int8 NOT NULL,
-- Inherited: answer_id int8 NOT NULL,
-- Inherited: text text NOT NULL,
CONSTRAINT "$1" CHECK (
save_date >= '2006-02-01'::date::timestamp without time zone
AND
save_date < ('2006-02-01'::date + '1 mon'::interval))
) INHERITS (data."out")
WITHOUT OIDS;
ALTER TABLE data.out_yy06mm02 OWNER TO merch;

Индексы должны быть созданы и для главной таблицы и для всех подтаблиц.

Теперь Вы можете вставлять данные в таблицу data."out" и выбирать их из нее же (по умолчанию при операции SELECT также показываются данные из унаследованных таблиц).

Посмотрим план выполнения простого запроса:
explain select * from data.out where object_id=-1;

получим примерно такой результат:

Result (cost=1.01..215.83 rows=3726 width=68)
-> Append (cost=1.01..215.83 rows=3726 width=68)
-> Bitmap Heap Scan on "out" (cost=1.01..8.62 rows=4 width=68)
Recheck Cond: (object_id = -1)
-> Bitmap Index Scan on out_object_id_idx
(cost=0.00..1.01 rows=4 width=0)
Index Cond: (object_id = -1)
-> Bitmap Heap Scan on out_yy06mm01 "out"
(cost=1.01..8.62 rows=4 width=68)
Recheck Cond: (object_id = -1)
-> Bitmap Index Scan on out_yy06mm01_object_id_idx
(cost=0.00..1.01 rows=4 width=0)
Index Cond: (object_id = -1)
-> Bitmap Heap Scan on out_yy06mm02 "out"
(cost=1.01..8.62 rows=4 width=68)
Recheck Cond: (object_id = -1)
-> Bitmap Index Scan on out_yy06mm02_object_id_idx
(cost=0.00..1.01 rows=4 width=0)
Index Cond: (object_id = -1)

Как видим, поиск идет по основной таблице и всем подтаблицам. Поскольку мы создали индексы по всем таблицам, везде видим Index Scan, что доказывает - все сделано правильно, при поиске используется индекс.

А теперь проверим, что при поиске по дате действительно будет выигрыш:
explain select * from data.out where save_date between '2006-01-01'::date and '2006-01-05'::date;

Result (cost=1.02..17.29 rows=8 width=68)
-> Append (cost=1.02..17.29 rows=8 width=68)
-> Bitmap Heap Scan on "out" (cost=1.02..8.64 rows=4 width=68)
Recheck Cond: ((save_date >= '2006-01-01'::date)
AND (save_date <= '2006-01-05'::date))
-> Bitmap Index Scan on out_save_date_idx
(cost=0.00..1.02 rows=4 width=0)
Index Cond: ((save_date >= '2006-01-01'::date)
AND (save_date <= '2006-01-05'::date))
-> Bitmap Heap Scan on out_yy06mm01 "out"
(cost=1.02..8.64 rows=4 width=68)
Recheck Cond: ((save_date >= '2006-01-01'::date)
AND (save_date <= '2006-01-05'::date))
-> Bitmap Index Scan on out_yy06mm01_save_date_idx
(cost=0.00..1.02 rows=4 width=0)
Index Cond: ((save_date >= '2006-01-01'::date)
AND (save_date <= '2006-01-05'::date))

Порядок, поиск идет только по главной таблице и по нужной дочерней, таблица data.out_yy06mm02 не затрагивается. При использовании десятков и сотен дочерних таблиц выигрыш огромный.

В том случае, если требуется запретить вставку данных в родительскую таблицу вне указанного для подтаблиц диапазона, можно добавить такие правила для родительской таблицы:

CREATE OR REPLACE RULE out_insert_out_before AS
ON INSERT TO data."out"
WHERE new.save_date < '2006-01-01'::date::timestamp without time zone
DO INSTEAD
SELECT elog('Попытка вставить данные вне допустимого диапазона');

CREATE OR REPLACE RULE out_insert_out_after AS
ON INSERT TO data."out"
WHERE new.save_date >= '2008-01-01'::date::timestamp without time zone
DO INSTEAD
SELECT elog('Попытка вставить данные вне допустимого диапазона');

Теперь данные, для которых нет подтаблицы будут игнорироваться с выдачей отладочного предупреждения в лог PostgreSQL. Команда

insert into data.out (object_id, user_id, question_id, answer_id, save_date, text)
values (-1, -1, -1, -1, '2008-01-01'::timestamp, '');

вернет пустую строку, при этом в логе можно наблюдать запись

NOTICE: Попытка вставить данные вне допустимого диапазона

Здесь функция elog определена следующим образом:

CREATE OR REPLACE FUNCTION public.elog(text)
RETURNS void AS
$BODY$

elog NOTICE $1

$BODY$
LANGUAGE 'pltcl' IMMUTABLE;

При этом обязанность проверять корректность создаваемых правил ложится на разработчика, будьте внимательны, при ошибке в правилах данные могут попасть не в ту таблицу или пропасть совсем.


Создание разделенных таблиц

Заметил в форумах нездоровую активность изобретателей велосипедов, которые предлагают всем желающим на этих изобретениях прокатиться. Потому выкладываю пример создания частей разделенной таблицы и соответствующих правил на эти части.


CREATE OR REPLACE FUNCTION data.build_partitional()
RETURNS void AS
$BODY$
set schema {data}

set table_parent {out}
# части данных, разбитые по месяцам
foreach year {06 07} {
foreach month {01 02 03 04 05 06 07 08 09 10 11 12} {
set table "${schema}.${table_parent}_yy${year}mm${month}"
set name "${table_parent}_yy${year}mm${month}"
spi_exec "CREATE TABLE $table \
( CONSTRAINT \"\$1\" CHECK (save_date >= '20${year}-${month}-01'::date::timestamp \
without time zone \
AND save_date < ('20${year}-${month}-01'::date + '1 \
month'::interval)::timestamp without time zone) \
) INHERITS (${schema}.${table_parent}) \
WITHOUT OIDS"

spi_exec "CREATE OR REPLACE RULE out_insert_$name AS \
ON INSERT TO ${schema}.${table_parent} \
WHERE new.save_date >= '20${year}-${month}-01'::date::timestamp without time zone \
AND new.save_date < ('20${year}-${month}-01'::date + '1 month'::interval)::timestamp without time zone \
DO INSTEAD INSERT INTO $table (save_date, user_id, object_id, question_id, \
answer_id, text) \
VALUES (new.save_date, new.user_id, new.object_id, new.question_id, \
new.answer_id, new.text)"

}
}
$BODY$
LANGUAGE 'pltcl' VOLATILE SECURITY DEFINER;


После создания подтаблиц создать требуемые индексы можно следующей функцией.


CREATE OR REPLACE FUNCTION data.build_partitional_index()
RETURNS void AS
$BODY$
set schema {data}

set table_parent {out}
set table "${schema}.${table_parent}"
set name "${table_parent}"

# части данных, разбитые по месяцам
foreach year {06 07} {
foreach month {01 02 03 04 05 06 07 08 09 10 11 12} {
set table "${schema}.${table_parent}_yy${year}mm${month}"
set name "${table_parent}_yy${year}mm${month}"

spi_exec "CREATE INDEX ${name}_answer_id_idx ON $table USING btree (answer_id)"
}
}
$BODY$
LANGUAGE 'pltcl' VOLATILE SECURITY DEFINER;

Комментариев нет:


(C) Alexey Pechnikov aka MBG, mobigroup.ru