Выбор одного значения

[hugarin]

Здравствуйте. Разрабатываем новое хранилище данных для нашего завода. Хотим использовать ваше секционирование.

Приведу кусочек структуры нашей БД.

Таблица oss.object_properties хранит идентификаторы свойств, вторичные ключи указывают на объекты, которым принадлежат эти свойства.

CREATE TABLE oss.object_properties
(
  object_property integer NOT NULL DEFAULT oss.next_id('object_property'),
  facet integer NOT NULL,
  property integer NOT NULL,
  CONSTRAINT pk_oss_object_properties PRIMARY KEY (object_property),
  CONSTRAINT fk_oss_object_properties_facet FOREIGN KEY (facet)
      REFERENCES oss.facets(facet) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE NO ACTION,
  CONSTRAINT fk_oss_object_properties_property FOREIGN KEY (property)
      REFERENCES oss.properties(property) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE NO ACTION
);

Таблица oss.partitioned_storage - главная архивная таблица (далее - архив) с секционированием по диапазонам поля mark, которая хранит значения всех свойств растянутых по времени.

CREATE TABLE oss.partitioned_storage
(
  object_property integer NOT NULL,
  mark timestamptz NOT NULL,
  value text,
  CONSTRAINT pk_oss_partitioned_storage PRIMARY KEY (object_property, mark),
  CONSTRAINT fk_oss_partitioned_storage_object_property FOREIGN KEY (object_property)
      REFERENCES oss.object_properties(object_property) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE NO ACTION
);

Проблемы

1. Как определить содержит ли архив хотя бы одно значение для конкретного свойства object_property?

Подобные выборки приводят к тому, что в память будут загружены индексы всех секций. Как вариант решения проблемы можно добавить в таблицу oss.object_properties дополнительный столбец-флаг, который будет нам говорить о том, есть данные по этому свойству или нет. Издержки здесь в том, что придется постоянно при вставке и удалении свойств проверять этот флаг. Но при удалении одного значения напрашивается вопрос: а вдруг это последнее и флаг нужно сбросить и мы снова попадаем в похожую ситуацию. Рассуждаем дальше. Тогда вместо флага мы добавим два столбца с временными границами всех значений по свойству left и right. Издержки - границы постоянно нужно будет держать актуальными при вставке, обновлении, удалении. Это повлечет за собой регулярное обслуживание таблицы oss.object_properties с полным перестроением. Хотя, возможно это будет приемлемым вариантом.

2. Как выбрать значение конкретного свойства object_property левее(по полю mark) известной отметки времени?
3. Как выбрать последнее значение конкретного свойства object_property? Это может быть частным случаем проблемы 2, если известной отметкой времени считать infinity.

Выборки данных для ответов на вопросы 2 и 3 затрагивают все секции архива, а хотелось бы чтобы этого не происходило.

select *
from oss.partitioned_storage
where object_property = 1
and mark < '2017-01-01 10:22'
order by mark desc
limit 1

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

Частично эту проблему решает дополнительный столбец в таблице oss.partitioned_storage, который будет указывать на предыдущее значение конкретного свойства.

[funbringer]

Добрый день, @hugarin

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

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

-- т.е. вы предлагаете денормализовать таблицу и разбить один запрос на два: сначала проверить флаг, а потом искать в архиве, так?

Далее,

Выборки данных для ответов на вопросы 2 и 3 затрагивают все секции архива, а хотелось бы чтобы этого не происходило.

Мне кажется, вы что-то неправильно делаете. Я примерно повторил вашу схему, и вот что получилось:

CREATE SCHEMA oss;

CREATE TABLE oss.object_properties
(
    object_property integer NOT NULL,
    facet integer NOT NULL,
    property integer NOT NULL
);

CREATE TABLE oss.partitioned_storage
(
    object_property integer NOT NULL,
    mark timestamptz NOT NULL,
    value text
);

select create_range_partitions(
    'oss.partitioned_storage',
    'mark',
    '2016-11-01 10:22'::timestamptz,
    '10 days'::interval,
    100);

select min(range_min::timestamptz),
       max(range_max::timestamptz)
from pathman_partition_list ;
          min           |          max
------------------------+------------------------
 2016-11-01 10:22:00+03 | 2019-07-29 10:22:00+03
(1 row)

explain (costs off) select *
from oss.partitioned_storage
where object_property = 1
and mark < '2017-01-01 10:22'
order by mark desc
limit 1;
                                                     QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
 Limit
   ->  Sort
         Sort Key: partitioned_storage_1.mark DESC
         ->  Append
               ->  Seq Scan on partitioned_storage_1
                     Filter: (object_property = 1)
               ->  Seq Scan on partitioned_storage_2
                     Filter: (object_property = 1)
               ->  Seq Scan on partitioned_storage_3
                     Filter: (object_property = 1)
               ->  Seq Scan on partitioned_storage_4
                     Filter: (object_property = 1)
               ->  Seq Scan on partitioned_storage_5
                     Filter: (object_property = 1)
               ->  Seq Scan on partitioned_storage_6
                     Filter: (object_property = 1)
               ->  Seq Scan on partitioned_storage_7
                     Filter: ((mark < '2017-01-01 10:22:00+03'::timestamp with time zone) AND (object_property = 1))
(18 rows)

Можно заметить, что выбираются только первые 7 партиций. Более того, можно отключить pg_pathman и посмотреть на разницу во времени планирования:

\timing 
Timing is on.

set pg_pathman.enable = on;
... -- запрос
Time: 1,559 ms

set pg_pathman.enable = off;
... -- запрос
Time: 12,901 ms

Как выбрать последнее значение конкретного свойства object_property?

• Можно сделать решение "в лоб" с ORDER BY.

• Мы убедились, что pg_pathman умеет отсекать лишние партиции, которые не соответствуют нужной дате. Предположим, что одна партиция покрывает 1 день, тогда мы можем обновлять время от времени дату последнего значения и использовать ее для отсечения лишних партиций. Как это делать - зависит от вас. Можно использовать триггеры, можно раз в день делать ORDER BY и обновлять дату, тогда все остальные запросы с условием по дате будут работать быстро.

[hugarin]

Спасибо за выкладки. Ситуация у нас прояснилась. Выполнили explain analyze для запросов вида

select *
from oss.partitioned_storage
where object_property = 1
order by mark desc
limit 1;

и все стало ясно. Т.е. работает все как мы и предполагали. В плане строится цепочка прохода по партициям с использованием индекса в оптимальном порядке и при нахождении одного значения executor дальше не идет. Спасибо за работу.