Обрезка разделов Postgres

У меня в Postgres большой стол.

Имя таблицы bigtable, а столбцы:

integer    |timestamp   |xxx |xxx |...|xxx
category_id|capture_time|col1|col2|...|colN

Я разделил таблицу по модулю 10 на category_id и date части столбца capture_time.

Таблицы разделов выглядят так:

CREATE TABLE myschema.bigtable_d000h0(
    CHECK ( category_id%10=0 AND capture_time >= DATE '2012-01-01' AND capture_time < DATE '2012-01-02')
) INHERITS (myschema.bigtable);

CREATE TABLE myschema.bigtable_d000h1(
    CHECK ( category_id%10=1 AND capture_time >= DATE '2012-01-01' AND capture_time < DATE '2012-01-02')
) INHERITS (myschema.bigtable);

Когда я запускаю запрос с category_id и capture_time в предложении where, разделы не удаляются должным образом.

explain select * from bigtable where capture_time >= '2012-01-01' and  capture_time < '2012-01-02' and category_id=100;

"Result  (cost=0.00..9476.87 rows=1933 width=216)"
"  ->  Append  (cost=0.00..9476.87 rows=1933 width=216)"
"        ->  Seq Scan on bigtable  (cost=0.00..0.00 rows=1 width=210)"
"              Filter: ((capture_time >= '2012-01-01 00:00:00'::timestamp without time zone) AND (capture_time < '2012-01-02 00:00:00'::timestamp without time zone) AND (category_id = 100))"
"        ->  Seq Scan on bigtable_d000h0 bigtable  (cost=0.00..1921.63 rows=1923 width=216)"
"              Filter: ((capture_time >= '2012-01-01 00:00:00'::timestamp without time zone) AND (capture_time < '2012-01-02 00:00:00'::timestamp without time zone) AND (category_id = 100))"
"        ->  Seq Scan on bigtable_d000h1 bigtable  (cost=0.00..776.93 rows=1 width=218)"
"              Filter: ((capture_time >= '2012-01-01 00:00:00'::timestamp without time zone) AND (capture_time < '2012-01-02 00:00:00'::timestamp without time zone) AND (category_id = 100))"
"        ->  Seq Scan on bigtable_d000h2 bigtable  (cost=0.00..974.47 rows=1 width=216)"
"              Filter: ((capture_time >= '2012-01-01 00:00:00'::timestamp without time zone) AND (capture_time < '2012-01-02 00:00:00'::timestamp without time zone) AND (category_id = 100))"
"        ->  Seq Scan on bigtable_d000h3 bigtable  (cost=0.00..1351.92 rows=1 width=214)"
"              Filter: ((capture_time >= '2012-01-01 00:00:00'::timestamp without time zone) AND (capture_time < '2012-01-02 00:00:00'::timestamp without time zone) AND (category_id = 100))"
"        ->  Seq Scan on bigtable_d000h4 bigtable  (cost=0.00..577.04 rows=1 width=217)"
"              Filter: ((capture_time >= '2012-01-01 00:00:00'::timestamp without time zone) AND (capture_time < '2012-01-02 00:00:00'::timestamp without time zone) AND (category_id = 100))"
"        ->  Seq Scan on bigtable_d000h5 bigtable  (cost=0.00..360.67 rows=1 width=219)"
"              Filter: ((capture_time >= '2012-01-01 00:00:00'::timestamp without time zone) AND (capture_time < '2012-01-02 00:00:00'::timestamp without time zone) AND (category_id = 100))"
"        ->  Seq Scan on bigtable_d000h6 bigtable  (cost=0.00..1778.18 rows=1 width=214)"
"              Filter: ((capture_time >= '2012-01-01 00:00:00'::timestamp without time zone) AND (capture_time < '2012-01-02 00:00:00'::timestamp without time zone) AND (category_id = 100))"
"        ->  Seq Scan on bigtable_d000h7 bigtable  (cost=0.00..315.82 rows=1 width=216)"
"              Filter: ((capture_time >= '2012-01-01 00:00:00'::timestamp without time zone) AND (capture_time < '2012-01-02 00:00:00'::timestamp without time zone) AND (category_id = 100))"
"        ->  Seq Scan on bigtable_d000h8 bigtable  (cost=0.00..372.06 rows=1 width=219)"
"              Filter: ((capture_time >= '2012-01-01 00:00:00'::timestamp without time zone) AND (capture_time < '2012-01-02 00:00:00'::timestamp without time zone) AND (category_id = 100))"
"        ->  Seq Scan on bigtable_d000h9 bigtable  (cost=0.00..1048.16 rows=1 width=215)"
"              Filter: ((capture_time >= '2012-01-01 00:00:00'::timestamp without time zone) AND (capture_time < '2012-01-02 00:00:00'::timestamp without time zone) AND (category_id = 100))"

Однако, если я добавлю точные критерии по модулю (category_id%10=0) в предложение where, он будет работать отлично.

explain select * from bigtable where capture_time >= '2012-01-01' and  capture_time < '2012-01-02' and category_id=100 and category_id%10=0;

"Result  (cost=0.00..2154.09 rows=11 width=215)"
"  ->  Append  (cost=0.00..2154.09 rows=11 width=215)"
"        ->  Seq Scan on bigtable  (cost=0.00..0.00 rows=1 width=210)"
"              Filter: ((capture_time >= '2012-01-01 00:00:00'::timestamp without time zone) AND (capture_time < '2012-01-02 00:00:00'::timestamp without time zone) AND (category_id = 100) AND ((category_id % 10) = 0))"
"        ->  Seq Scan on bigtable_d000h0 bigtable  (cost=0.00..2154.09 rows=10 width=216)"
"              Filter: ((capture_time >= '2012-01-01 00:00:00'::timestamp without time zone) AND (capture_time < '2012-01-02 00:00:00'::timestamp without time zone) AND (category_id = 100) AND ((category_id % 10) = 0))"

Есть ли способ сделать так, чтобы сокращение разделов работало правильно, не добавляя условие по модулю в каждый запрос?


postgresql partitioning partition-problem database-partitioning
person Dojo    schedule 03.04.2012    source источник
comment
Какую версию вы используете? Я думаю, что в планировщике были некоторые улучшения в отношении разбиения на разделы в 9.x.   -  person a_horse_with_no_name    schedule 03.04.2012
comment
Вы можете сделать ограничение менее подробным: CHECK (category_id%10=1 AND date_trunc('month', capture_time) = '2012-01-01'::date)   -  person Clodoaldo Neto    schedule 03.04.2012
comment
@a_horse_with_no_name Я использую 9.1   -  person Dojo    schedule 03.04.2012
comment
@Clodoaldo puing по времени работает нормально. только часть по модулю не работает должным образом. Кстати, он более подробный, потому что данные могут быть разделены по неделям или месяцам для диапазонов дат, которые не используются активно.   -  person Dojo    schedule 03.04.2012

Ответы (2)


arrow_upward
4
arrow_downward

Дело в том, что для ограничения исключения PostgreSQL создаст неявный индекс. В вашем случае этот индекс будет частичным, потому что вы используете выражение в столбце, а не только его значение. И это указано в документации (см. Пример 11-2):

PostgreSQL не имеет сложной программы доказательства теорем, которая могла бы распознавать математически эквивалентные выражения, написанные в разных формах. (Такое средство доказательства общих теорем не только чрезвычайно сложно создать, оно, вероятно, будет слишком медленным, чтобы иметь какое-либо реальное применение.) Система может распознавать простые следствия неравенства, например, «x‹ 1 »подразумевает« x ‹2»; в противном случае условие предиката должно точно соответствовать части условия WHERE запроса, иначе индекс не будет распознан как пригодный для использования. Сопоставление происходит во время планирования запроса, а не во время выполнения.

Таким образом, ваши результаты - у вас должно быть точно такое же выражение, которое вы использовали при создании ограничения CHECK.

Для разделения на основе HASH я предпочитаю 2 подхода:

  • добавьте поле, которое может принимать ограниченный набор значений (10 в вашем случае), лучше всего, если такое существует по дизайну;
  • укажите диапазоны хэшей так же, как и диапазоны временных меток: MINVALUE ‹= category_id‹ MAXVALUE

Также возможно создание двухуровневого разбиения:

  • на первом уровне вы создаете 10 разделов на основе category_id HASH;
  • на втором уровне вы создаете необходимое количество разделов в зависимости от ваших диапазонов дат.

Хотя я всегда стараюсь использовать для разбиения только 1 столбец, проще управлять.

person vyegorov    schedule 03.04.2012
comment
спасибо за ваш вклад. Код, который я опубликовал, выполняет двухуровневое разделение с использованием 1 уровня наследования. С точки зрения производительности он работает быстрее, чем фактическое двухуровневое наследование. Я знаю, что наоборот (как вы предложили) должно быть быстрее, потому что на первом уровне меньше таблиц для проверки, а на следующем уровне нужно сканировать только таблицы, унаследованные от соответствующих таблиц первого уровня. Но на практике это медленнее. - person Dojo; 08.04.2012
comment
Его замедляет логика сокращения разделов, а не фактическое сканирование таблицы. В обоих случаях оптимизатор правильно обрезает таблицы, но в случае двухуровневого наследования решение, какие разделы нужно обрезать, занимает больше времени. - person Dojo; 08.04.2012
comment
Интересная особенность двухуровневого секционирования заключается в том, что вы можете запрашивать таблицу первого уровня, и теперь сокращение секций второго уровня занимает меньше времени. Я могу использовать это для архивирования данных таким образом, чтобы не снижать производительность. - person Dojo; 08.04.2012

arrow_upward
1
arrow_downward

Для тех, у кого такая же проблема: я пришел к выводу, что самый простой выход - изменить запросы, включив в них условие по модулю category_id%10=0

person Dojo    schedule 08.04.2012