Ключевые слова:postgresql, sql, database, array, tcl, pltcl, index, tune, optimization, (найти похожие документы)
From: Печников Алексей <http://postgrestips.blogspot.com>;
Date: Mon, 10 Mar 2008 18:21:07 +0000 (UTC)
Subject: Массивы, индексы и разделенные таблицы в PostgreSQL
Оригинал: http://postgrestips.blogspot.com/2007/06/array.htmlhttp://postgrestips.blogspot.com/2007/06/partitial-table.htmlhttp://postgrestips.blogspot.com/2007/06/cluster.htmlМассивы
Встроенная реализация массивов является всего лишь примером создания
многомерных типов данных и мало пригодна в реальных приложениях.
Напишите свою реализацию или возьмите готовую, подходящую для Ваших
задач. Например, для разработчиков на pltcl может оказаться полезным
используемый автором подход, при котором строка (тип text) может
представлять собой тиклевский список. Создав набор функций на pltcl
для работы с таким типом данных, мы получаем значительные
преимущества, поскольку к строке можно применить все стандартные
функции базы данных и кроме того, можно интерпретировать ее как
список, обрабатывая собственными функциями. Изложенный метод имеет
свои ограничения, но тем не менее хранить атрибутивную информацию
таким способом и удобнее и выгоднее, чем в формате xml. Заметим, что
список может иметь произвольную вложенность и скорость работы с ним не
зависит от длины списка (не учитывая время на чтение с диска). Приведу
набор функций, часто используемых при работе со списком, в большинстве
случаев Вам их будет достаточно.
Запись в лог
select elog('log message');
CREATE OR REPLACE FUNCTION public.elog(text)
RETURNS void AS
$BODY$
elog NOTICE $1
$BODY$
LANGUAGE 'pltcl' IMMUTABLE;
Набор записей из массива
select array2items('{a,b,c}'::text[]);
CREATE OR REPLACE FUNCTION public.array2items(in_array anyarray)
RETURNS SETOF anyelement AS
$BODY$
SELECT ($1)[s] FROM generate_series(1,array_upper($1, 1)) AS s;
$BODY$
LANGUAGE 'sql' IMMUTABLE;
Список из массива
Внимание: ранее использовалась функция, но для массивов с кучей
различных кавычек и скобочек происходили сбои, так что пришлось
сделать идеологически правильным, но менее удобным для использования
способом. Агрегатные функции см. ниже.
select list_accum(array2items) from array2items('{a,b,c}'::text[]);
Массив из списка
select list2array('a b c');
select * from array2items((select list2array('c v { a b}'))::text[]);
CREATE OR REPLACE FUNCTION public.list2array(list text)
RETURNS text[] AS
$BODY$
if {$1 eq ""} {return "{}"}
return [concat "{" [join $1 ", "] "}"]
$BODY$
LANGUAGE 'pltcl' IMMUTABLE;
Удаление элемента из списка
select ldelete('1 2 3 4 5','5');
CREATE OR REPLACE FUNCTION public.ldelete(list text, value text)
RETURNS text AS
$BODY$
set ix [lsearch -exact $1 $2]
if {$ix >= 0} {
return [lreplace $1 $ix $ix]
} else {
return $1
}
$BODY$
LANGUAGE 'pltcl' IMMUTABLE;
Замена элемента в списке
select lreplace('1 2 3 4 5','5', '7');
CREATE OR REPLACE FUNCTION public.lreplace(list text, old_value text, new_value text)
RETURNS text AS
$BODY$
set ix [lsearch -exact $1 $2]
if {$ix >= 0} {
return [lreplace $1 $ix $ix $3]
} else {
return $1
}
$BODY$
LANGUAGE 'pltcl' IMMUTABLE;
Добавление элемента в список
select lappend('1 2 3 4 5','10');
CREATE OR REPLACE FUNCTION public.lappend(list text, value text)
RETURNS text AS
$BODY$
return [lappend 1 $2]
$BODY$
LANGUAGE 'pltcl' IMMUTABLE;
Объединение списков
select lconcat('a b c', '1 2 3');
CREATE OR REPLACE FUNCTION public.lconcat(list1 text, list2 text)
RETURNS text AS
$BODY$
return [concat $1 $2]
$BODY$
LANGUAGE 'pltcl' IMMUTABLE;
Сортировка списка
select lsort('cd 1 99 2 100 ab bc');
CREATE OR REPLACE FUNCTION public.lsort(list text)
RETURNS text AS
$BODY$
return [lsort -dictionary $1]
$BODY$
LANGUAGE 'pltcl' IMMUTABLE;
Выбор уникальных значений из списка
Возвращаемые значения также отсортированы аналогично lsort
select lunique('cd 1 99 2 100 ab bc 1 2');
CREATE OR REPLACE FUNCTION public.lunique(list text)
RETURNS text AS
$BODY$
return [lsort -dictionary -unique $1]
$BODY$
LANGUAGE 'pltcl' IMMUTABLE;
1-й элемент списка
select lfirst('1 2 3 4 5 6 7 8 9 10');
CREATE OR REPLACE FUNCTION public.lfirst(list text)
RETURNS text AS
$BODY$
return [lindex $1 0]
$BODY$
LANGUAGE 'pltcl' IMMUTABLE;
2-й элемент списка
select lsecond('1 2 3 4 5 6 7 8 9 10');
CREATE OR REPLACE FUNCTION public.lsecond(list text)
RETURNS text AS
$BODY$
return [lindex $1 1]
$BODY$
LANGUAGE 'pltcl' IMMUTABLE;
i-й элемент списка
Внимание: нумерация элементов с нуля!
select lindex('5 10 15', 2);
CREATE OR REPLACE FUNCTION public.lindex(list text, index int4)
RETURNS text AS
$BODY$
return [lindex $1 $2]
$BODY$
LANGUAGE 'pltcl' IMMUTABLE;
Длина списка
select llength('1 2 3 4 5');
CREATE OR REPLACE FUNCTION public.llength(list text)
RETURNS int4 AS
$BODY$
return [llength $1]
$BODY$
LANGUAGE 'pltcl' IMMUTABLE;
Набор записей из списка
select list2items('a b c');
CREATE OR REPLACE FUNCTION public.list2items(in_list text)
RETURNS SETOF text AS
$BODY$
SELECT lindex($1, s) FROM generate_series(0,llength($1)-1) AS s;
$BODY$
LANGUAGE 'sql' IMMUTABLE;
Агрегаты
select list_accum(id) from merch.answers;
CREATE AGGREGATE public.list_accum(
BASETYPE=text,
SFUNC=lappend,
STYPE=text,
INITCOND=''
);
CREATE AGGREGATE public.list_accum_list(
BASETYPE=text,
SFUNC=lconcat,
STYPE=text,
INITCOND=''
);
Обратите внимание, что мы использовали только стандартные тиклевские
функции. А если еще загрузить в PostgreSQL вот такие функции (см.
pltcl_loadmod, кстати, там есть баг - этот скрипт все функции от
суперпользователя создает, что приводит к неприятным последствиям в
крупных системах, так что если кого заинтересует, выложу пропатченную
версию):
proc lfilter {list script} {
set res {}
foreach e $list {if {[uplevel 1 $script $e]} {lappend res $e}}
set res
}
proc lin {list e} {expr {[lsearch -exact $list $e]>=0}}
proc lintersection {set1 set2} {lfilter $set1 {lin $set2}}
proc joinquote {list {joinstring { }}} {
foreach x $list {
lappend list2 "'[quote $x]'"
}
return [join $list2 $joinstring]
}
можно расширить наш набор следующими pltcl функциями:
Пересечение списков
select lintersection('a b c d e g', 'a d f g');
CREATE OR REPLACE FUNCTION public.lintersection(lista text, listb text)
RETURNS text AS
$BODY$
return [lintersection $1 $2]
$BODY$
LANGUAGE 'pltcl' IMMUTABLE;
Проверка существования элемента в списке
select lin('a b c', 'd');
CREATE OR REPLACE FUNCTION public.lin(list text, item text)
RETURNS bool AS
$BODY$
return [lin $1 $2]
$BODY$
LANGUAGE 'pltcl' IMMUTABLE;
Индексы
При создании индексов следует руководствоваться разумной
необходимостью - создавать индексы только там, где они необходимы.
Индексы занимают значительное место на диске, в результате чего работа
с ними может
стать не эффективной. В случае, когда индекс требуется при выполнении
группировки, поиске, объединении таблиц и других операциях, он должен
быть создан. Следует использовать тип индекса btree, не hash, так как
последний может приводить к повреждению таблиц при конкурентных
запросах.
Рассмотрим работу индексов на примере таблицы, с которой мы уже
познакомились в статье Разделенные таблицы (см. ниже). Напомню определение
таблицы:
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;
Функциональные индексы в примерах
Для эффективного поиска по полю save_date типа "дата и время" создадим
индекс
CREATE INDEX out_save_date_idx
ON data."out"
USING btree
(save_date);
Теперь проверим план выполнения следующего запроса:
explain select * from data.out where save_date between
'2006-01-01'::timestamp and '2006-01-05'::timestamp;
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 00:00:00'::timestamp without time zone)
AND (save_date <= '2006-01-05 00:00:00'::timestamp without time zone))
-> Bitmap Index Scan on out_save_date_idx
(cost=0.00..1.02 rows=4 width=0)
Index Cond: (
(save_date >= '2006-01-01 00:00:00'::timestamp without time zone)
AND (save_date <= '2006-01-05 00:00:00'::timestamp without time zone))
Как видим, все работает именно так, как ожидалось - при выборке данных
используется индекс out_save_date_idx.
Обратите внимание, что индекс по save_date будет работать только когда
условие поиска записано с типом данных timestamp. Мы указали условие
поиска '2006-01-01'::timestamp и получили эффективный план выполнения
запроса.
Теперь рассмотрим немного другой запрос, который на первый взгляд
должен работать точно так же, как и предыдущий:
select * from data.out where save_date between '2006-01-01'::text and '2006-01-05'::text;
Вот план его выполнения:
Result (cost=0.00..156844.44 rows=26670 width=68)
-> Append (cost=0.00..156844.44 rows=26670 width=68)
-> Seq Scan on "out" (cost=0.00..26.00 rows=4 width=68)
Filter: (((save_date)::text >= '2006-01-01'::text)
AND ((save_date)::text <= '2006-01-05'::text))
Как видим, в этом случае индекс не используется. Для эффективного
выполнения такого запроса потребуется создать функциональный индекс:
CREATE INDEX out_date_astext_idx
ON data."out"
USING btree
((to_char (save_date,'YYYY-MM-DD')));
и переписать запрос следующим образом
select * from data.out where to_char (save_date,'YYYY-MM-DD') between
'2006-01-01'::text and '2006-01-05'::text;
Только после этого планировщик сможет воспользоваться индексом:
Result (cost=1.03..156827.11 rows=26670 width=68)
-> Append (cost=1.03..156827.11 rows=26670 width=68)
-> Bitmap Heap Scan on "out" (cost=1.03..8.67 rows=4 width=68)
Recheck Cond: (
(to_char(save_date, 'YYYY-MM-DD'::text) >= '2006-01-01'::text)
AND (to_char(save_date, 'YYYY-MM-DD'::text) <= '2006-01-05'::text))
-> Bitmap Index Scan on out_date_astext_idx
(cost=0.00..1.03 rows=4 width=0)
Index Cond: (
(to_char(save_date, 'YYYY-MM-DD'::text) >= '2006-01-01'::text)
AND (to_char(save_date, 'YYYY-MM-DD'::text) <= '2006-01-05'::text))
Анализ плана выполнения показывает, что теперь используется индекс
out_date_astext_idx. В сущности, мы получили именно то, что и
следовало ожидать - пока мы не объяснили планировщику запроса, в каком
виде представлены наши данные, он не мог эффективно спланировать
запрос.
Мы рассмотрели одну из часто встречающихся задач, требующую создания
функционального индекса. Несмотря на простоту описанного примера,
очень распространена ошибка, когда в качестве условия поиска
используют данные другого типа, отличного от типа данных поля. При
этом индекс по нужному полю не используется, вызывая недоумение
разработчиков. Надеюсь, теперь Вы в такой ситуации вспомните, что в
этом случае требуется создать функциональный индекс.
Примечание: в версии PostgreSQL 8.1.9 планировщик запроса умеет
использовать индекс по полю типа timestamp даже тогда, когда условие
имеет тип date. А вот в предыдущих версиях PostgreSQL для эффективного
выполнения такого запроса нужно было создавать индекс save_date::date
или приводить условия к типу timestamp и при этом запросто можно было
ошибиться на 1 день :-)
Разделенные таблицы
Работа с очень большими таблицами происходит медленнее, чем с
небольшими. Потому полезно создавать так называемые разделенные
таблицы. В таких таблицах данные физически размещаются в нескольких
таблицах-потомках, но запросы на изменение/выборку данных обращаются
только к одной родительской таблице. Для этого нужно создать одну
родительскую таблицу, необходимое число наследующих от нее все поля
(или не все, но это редкость) подтаблиц (дочерних таблиц) с указанными
ограничениями и написать набор правил (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 не затрагивается. При использовании десятков
и сотен дочерних таблиц выигрыш огромный.
Вы думаете, это все? Нет, это только начало :-) А пока рекомендую
сделать кластеризацию.
Кластеризация
В больших таблицах хранится много данных, быстро найти нужные из них
помогают индексы. Но количество индексных записей растет вместе с
количеством записей в таблице и при извлечении нужных данных
приходится обработать много индексных записей. С целью ускорения
выборки нужных индексных записей применяется операция кластеризации
(cluster) таблиц по указанному индексу, которая упорядочивает индексы
так, что поиск по индексам не требует перебора всех страниц индексов
на диске и так же упорядочивает сами данные. На больших таблицах
кластеризация позволяет в десятки и более раз ускорить выборку данных.
Тестовые запросы обычно не показывают заметного ускорения на
кластеризованной таблице - дело в том, что кластеризация особенно
ускоряет получение данных. Так что запрос
select * from test where value=10 limit 1;
будет выполняться точно так же, зато при выборке большого набора
данных выигрыш будет существенный за счет последовательного чтения
требуемых данных с диска. Например, вот такой запрос уже может стать
эффективнее
select * from test where value=10 limit 10;
Самое важное состоит в том, чтобы правильно определить поле, по
которому нужно ускорить выборку. Например, если часто требуется
строить отчет по определенному пользователю, стоит кластеризовать
таблицу по индексу на идентификатор пользователя, а если требуется
строить отчеты за выбранную дату - кластеризовать по индексу на дату.
С другой стороны, если данные в таблицу записывались в определенном
порядке, так что значения какого-то поля шли последовательно, то
кластеризация по индексу на другое поле может замедлить определенные
выборки данных. Выбирать нужное поле лучше всего, проводя
кластеризацию по каждому индексу поочередно и ориентируясь на
результаты выполнения тестов производительности. Поскольку зависимости
от других таблиц нет, то эта задача не представляет особых трудностей.
Правда, бывают исключения - когда в одной таблице много значимых
полей, по которым проводятся выборки, кластеризация помогает лишь для
одного вида выборок. В этом случае стоит разделить таблицу на
несколько или создать таблицы функционалов (заранее вычисленные
функций от данных, требующиеся в выборках).
Обычно целью кластеризации является устранение самого узкого места в
рабочей системе и этим занимаются непосредственно перед настройкой
параметров PostgreSQL для обеспечения максимальной производительности.
Из собственного опыта могу сказать, что описанным выше способом
удалось ускорить построение определенного вида отчетов в 20 или 30 раз
при выборке порядка 0,1% - 1% записей из таблицы с общим числом
записей порядка 1 миллиона (одна из подтаблиц разделенной таблицы).
Медленная выборка была связана именно с тем, что выбирались записи,
разбросанные в файле таблицы на диске. Размер таблицы на диске
составлял порядка 100 Мб и еще вдвое больше места занимали индексы.
Выигрыш заметен уже при 100 тысячах записей в таблице. При
миллионе записей у меня выигрыш был более чем в 10 раз при
выборке порядка 100 записей.
