PostgreSQL: как структурировать и индексировать связанные с временем данные для оптимальной производительности запросов? - программирование
Подтвердить что ты не робот

PostgreSQL: как структурировать и индексировать связанные с временем данные для оптимальной производительности запросов?

Проблема:

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

Контекст проекта:

Хотя это вопрос с чистой базой данных, некоторый контекст может помочь понять модель данных:

Проект сосредотачивается вокруг проведения исследований на большой, сложной машине. Я не очень разбираюсь в самой машине, но слух в лаборатории есть там кошка Шрёдингера, которая висела на ней сбоку; -)

Мы измеряем множество различных параметров, когда машина работает с датчиками, расположенными по всему аппарату в разных точках измерения (так называемые пятна) через определенные промежутки времени в течение определенного периода времени. Мы используем не только одно устройство для измерения этих параметров, но и целый ряд из них; они отличаются качеством своих данных измерений (я думаю, что это касается выборочных ставок, качества датчика, цены и многих других аспектов, с которыми меня не интересуют); одной из целей проекта является установление сравнения между этими устройствами. Вы можете визуализировать эти измерительные приборы как кучу лабораторных тележек, каждая из которых имеет множество кабелей, подключенных к машине, каждая из которых предоставляет данные измерений.

Модель данных:

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

В двух словах:

  • устройство имеет уникальное имя
  • параметр также имеет имя; они не уникальны, поэтому, у него также есть идентификатор
  • у пятна есть идентификатор

База данных проекта, конечно, сложнее, но эти данные не имеют отношения к проблеме.

  • индекс данных измерений имеет идентификатор, отметку времени, когда было произведено измерение, и ссылки на устройство и место, на котором выполнялось измерение.
  • значение данных измерения имеет ссылку на параметр и на фактическое значение

Первоначально я смоделировал значение данных измерений, чтобы иметь свой собственный идентификатор в качестве первичного ключа; отношение n:m между индексом данных измерения и значением представляло собой отдельную таблицу, в которой хранились только пары index:value ID, но поскольку сама таблица потребляла довольно много пространства жесткого диска, мы ее исключили и изменили значение ID как простое целое число который хранит идентификатор индекса данных измерений, которому он принадлежит; первичный ключ значения данных измерения теперь состоит из этого идентификатора и идентификатора параметра.

На стороне примечания. Когда я создал модель данных, я внимательно следил за общими принципами проектирования, такими как 3NF и соответствующими ограничениями таблицы (например, уникальные ключи); другим правилом было создание индекса для каждого внешнего ключа. У меня есть подозрение, что отклонение в таблицах индексов/значений измерительных данных от "строгого" 3NF может быть одной из причин проблем производительности, которые я рассматриваю сейчас, но изменение модели данных назад не решило проблему.

Модель данных в DDL:

ПРИМЕЧАНИЕ. Ниже приведено обновление этого кода.

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

\c postgres
DROP DATABASE IF EXISTS so_test;
CREATE DATABASE so_test;
\c so_test

CREATE TABLE device
(
  name VARCHAR(16) NOT NULL,
  CONSTRAINT device_pk PRIMARY KEY (name)
);

CREATE TABLE parameter
(
  -- must have ID as names are not unique
  id SERIAL,
  name VARCHAR(64) NOT NULL,
  CONSTRAINT parameter_pk PRIMARY KEY (id)
);

CREATE TABLE spot
(
  id SERIAL,
  CONSTRAINT spot_pk PRIMARY KEY (id)
);

CREATE TABLE measurement_data_index
(
  id SERIAL,
  fk_device_name VARCHAR(16) NOT NULL,
  fk_spot_id INTEGER NOT NULL,
  t_stamp TIMESTAMP NOT NULL,
  CONSTRAINT measurement_pk PRIMARY KEY (id),
  CONSTRAINT measurement_data_index_fk_2_device FOREIGN KEY (fk_device_name)
    REFERENCES device (name) MATCH FULL
    ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT measurement_data_index_fk_2_spot FOREIGN KEY (fk_spot_id)
    REFERENCES spot (id) MATCH FULL
    ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT measurement_data_index_uk_all_cols UNIQUE (fk_device_name, fk_spot_id, t_stamp)
);

CREATE TABLE measurement_data_value
(
  id INTEGER NOT NULL,
  fk_parameter_id INTEGER NOT NULL,
  value VARCHAR(16) NOT NULL,
  CONSTRAINT measurement_data_value_pk PRIMARY KEY (id, fk_parameter_id),
  CONSTRAINT measurement_data_value_fk_2_parameter FOREIGN KEY (fk_parameter_id)
    REFERENCES parameter (id) MATCH FULL
    ON UPDATE NO ACTION ON DELETE NO ACTION
);

Я также создал script, чтобы заполнить таблицу некоторыми тестовыми данными:

CREATE OR REPLACE FUNCTION insert_data()
RETURNS VOID
LANGUAGE plpgsql
AS
$BODY$
  DECLARE
    t_stamp  TIMESTAMP := '2012-01-01 00:00:00';
    index_id INTEGER;
    param_id INTEGER;
    dev_name VARCHAR(16);
    value    VARCHAR(16);
  BEGIN
    FOR dev IN 1..5
    LOOP
      INSERT INTO device (name) VALUES ('dev_' || to_char(dev, 'FM00'));
    END LOOP;
    FOR param IN 1..20
    LOOP
      INSERT INTO parameter (name) VALUES ('param_' || to_char(param, 'FM00'));
    END LOOP;
    FOR spot IN 1..10
    LOOP
      INSERT INTO spot (id) VALUES (spot);
    END LOOP;

    WHILE t_stamp < '2012-01-07 00:00:00'
    LOOP
      FOR dev IN 1..5
      LOOP
        dev_name := 'dev_' || to_char(dev, 'FM00');
        FOR spot IN 1..10
        LOOP
          INSERT INTO measurement_data_index
            (fk_device_name, fk_spot_id, t_stamp)
            VALUES (dev_name, spot, t_stamp) RETURNING id INTO index_id;
          FOR param IN 1..20
          LOOP
            SELECT id INTO param_id FROM parameter
              WHERE name = 'param_' || to_char(param, 'FM00');
            value := 'd'  || to_char(dev,   'FM00')
                  || '_s' || to_char(spot,  'FM00')
                  || '_p' || to_char(param, 'FM00');
            INSERT INTO measurement_data_value (id, fk_parameter_id, value)
              VALUES (index_id, param_id, value);
          END LOOP;
        END LOOP;
      END LOOP;
      t_stamp := t_stamp + '1 minute'::INTERVAL;
    END LOOP;

  END;
$BODY$;

SELECT insert_data();

Планировщик запросов PostgreSQL требует актуальной статистики, поэтому проанализируйте все таблицы. Вакуумирование может не потребоваться, но все равно:

VACUUM ANALYZE device;
VACUUM ANALYZE measurement_data_index;
VACUUM ANALYZE measurement_data_value;
VACUUM ANALYZE parameter;
VACUUM ANALYZE spot;

Пример запроса:

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

EXPLAIN (ANALYZE ON, BUFFERS ON)
SELECT measurement_data_value.value
  FROM measurement_data_value, parameter
 WHERE measurement_data_value.fk_parameter_id = parameter.id
   AND parameter.name = 'param_01';

Образцовый результат на моей машине разработки (см. ниже некоторые подробности о моей среде):

                                                                QUERY PLAN                                                                
------------------------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=1.26..178153.26 rows=432000 width=12) (actual time=0.046..2281.281 rows=432000 loops=1)
   Hash Cond: (measurement_data_value.fk_parameter_id = parameter.id)
   Buffers: shared hit=55035
   ->  Seq Scan on measurement_data_value  (cost=0.00..141432.00 rows=8640000 width=16) (actual time=0.004..963.999 rows=8640000 loops=1)
         Buffers: shared hit=55032
   ->  Hash  (cost=1.25..1.25 rows=1 width=4) (actual time=0.010..0.010 rows=1 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 1kB
         Buffers: shared hit=1
         ->  Seq Scan on parameter  (cost=0.00..1.25 rows=1 width=4) (actual time=0.004..0.008 rows=1 loops=1)
               Filter: ((name)::text = 'param_01'::text)
               Buffers: shared hit=1
 Total runtime: 2313.615 ms
(12 rows)

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

CREATE INDEX measurement_data_index_idx_fk_device_name
    ON measurement_data_index (fk_device_name);
CREATE INDEX measurement_data_index_idx_fk_spot_id
    ON measurement_data_index (fk_spot_id);
CREATE INDEX measurement_data_value_idx_fk_parameter_id
    ON measurement_data_value (fk_parameter_id);

затем сделайте еще один вакуумный анализ (просто для того, чтобы быть в безопасности) и повторно запустите запрос, планировщик использует растровые кучи и сканирование растровых индексов, а общее время запроса несколько улучшается:

                                                                                   QUERY PLAN                                                                                   
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=8089.19..72842.42 rows=431999 width=12) (actual time=66.773..1336.517 rows=432000 loops=1)
   Buffers: shared hit=55033 read=1184
   ->  Seq Scan on parameter  (cost=0.00..1.25 rows=1 width=4) (actual time=0.005..0.012 rows=1 loops=1)
         Filter: ((name)::text = 'param_01'::text)
         Buffers: shared hit=1
   ->  Bitmap Heap Scan on measurement_data_value  (cost=8089.19..67441.18 rows=431999 width=16) (actual time=66.762..1237.488 rows=432000 loops=1)
         Recheck Cond: (fk_parameter_id = parameter.id)
         Buffers: shared hit=55032 read=1184
         ->  Bitmap Index Scan on measurement_data_value_idx_fk_parameter_id  (cost=0.00..7981.19 rows=431999 width=0) (actual time=65.222..65.222 rows=432000 loops=1)
               Index Cond: (fk_parameter_id = parameter.id)
               Buffers: shared read=1184
 Total runtime: 1371.716 ms
(12 rows)

Тем не менее, это еще более секунды времени выполнения для действительно простого запроса.

Что я сделал до сих пор:

  • получил себе копию PostgreSQL 9.0 High Performance - отличная книга!
  • сделала базовую конфигурацию сервера PostgreSQL, см. ниже приведенную ниже
  • создал фреймворк для запуска серии тестов производительности с использованием реальных запросов из проекта и для графического отображения результатов; эти запросы используют устройства, пятна, параметры и временной интервал в качестве входных параметров, а серия испытаний выполняется, например. 5, 10 устройств, 5, 10 пятен, 5, 10, 15, 20 параметров и 1,7 дня. Основной результат заключается в том, что они слишком медленны, но их план запросов слишком сложный для понимания, поэтому я вернулся к действительно простому запросу, использованному выше.

Я просмотрел partitioning таблицу значений. Данные связаны со временем, и разбиение представляется подходящим средством для организации такого рода данных; даже примеры в документации PostgreSQL используют что-то подобное. Тем не менее, я читал в той же статье:

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

Вся тестовая база данных имеет размер менее 1 ГБ, и я запускаю свои тесты на машине разработки с 8 ГБ ОЗУ и на виртуальной машине с 1 ГБ (см. также среду ниже), поэтому таблица далеко не очень большая или даже превышая физическую память. Я мог бы реализовать разделение в любом случае на каком-то этапе, но я чувствую, что подход не нацелен на проблему производительности.

Кроме того, я рассматриваю cluster таблицу значений. Мне не нравится тот факт, что кластеризация должна быть переделана всякий раз, когда вставляются новые данные, и что для этого дополнительно требуется эксклюзивная блокировка чтения/записи, но, глядя на этот, вопрос SO кажется, что это в любом случае имеет свои преимущества и может быть вариантом. Тем не менее, кластеризация выполняется по индексу, и поскольку в запрос входит (до 4) критериев выбора (устройства, точки, параметры и время), мне придется создавать кластеры для всех из них, что, в свою очередь, создает впечатление, что Я просто не создаю нужные индексы...

Моя среда:

  • развитие происходит на MacBook Pro (середина 2009 года) с двухъядерным процессором и 8 ГБ оперативной памяти.
  • Я запускаю тесты производительности базы данных на виртуальной машине Debian 6.0 с 1 ГБ ОЗУ, размещенной на MBP
  • Версия PostgreSQL - это 9.1, поскольку это была последняя версия, когда я ее установил, возможно обновление до 9.2.
  • Я изменил shared_buffers с 1600 до 256% оперативной памяти на обеих машинах, как рекомендовано в PostgreSQL docs (что связано с расширение настройки ядра, такие как SHMALL, SHMMAX и т.д.)
  • Аналогично, я изменил effective_cache_size с 128 МБ по умолчанию на 50% доступной ОЗУ
  • Я провел тест производительности с различными настройками work_mem, но не видел каких-либо существенных различий в производительности

Примечание:Один из аспектов, который, как мне кажется, важен, состоит в том, что серия тестов производительности с реальными запросами проекта не отличается по производительности между MacBook с 8 ГБ и виртуальной машиной с 1 ГБ; то есть, если запрос занимает 10 секунд на MacBook, он также занимает 10 секунд на виртуальной машине. Кроме того, я выполнял те же тесты производительности до и после изменения shared_buffers, effective_cache_size и work_mem, а изменения конфигурации не улучшали производительность более чем на 10%; некоторые результаты на самом деле даже ухудшились, поэтому кажется, что любая разница вызвана скорее изменением теста, чем изменением конфигурации. Эти наблюдения заставляют меня думать, что параметры ОЗУ и postgres.conf пока не являются ограничивающими факторами.

Мои вопросы:

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

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

В более широком контексте настраивается производительность запросов

  • обычно делается "на базе инцидента", т.е. как только запрос не будет удовлетворительно работать? Кажется, что все мои запросы слишком медленные...
  • в основном вопрос о поиске (и понимании) планов запросов, затем добавление индексов и измерение, если что-то улучшится, возможно, ускорит процесс, применив один опыт?

Как я могу получить эту базу данных?

Обновление 01:

Глядя на ответы до сих пор, я думаю, что я не объяснил необходимость в таблицах индексов/значений данных измерений, поэтому позвольте мне попробовать еще раз. Место для хранения здесь.

Примечание:

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

Полагая

  • 1 день измерений
  • 1 набор измерений в минуту
  • 10 устройств
  • 10 параметров
  • 10 точек

Это добавляет к

1 meas/min x 60 min/hour x 24 hour/day = 1440 meas/day

Каждое измерение имеет данные из каждого места и каждого устройства для каждого параметра, поэтому

10 spots x 10 devices x 10 parameters = 1000 data sets/meas

Итак, всего

1440 meas/day x 1000 data sets/meas = 1 440 000 data sets/day

Если мы сохраним все измерения в одной таблице как Catcall, предложенные, например

CREATE TABLE measurement_data
(
  device_name character varying(16) NOT NULL,
  spot_id integer NOT NULL,
  parameter_id integer NOT NULL,
  t_stamp timestamp without time zone NOT NULL,
  value character varying(16) NOT NULL,
  -- constraints...
);

одна строка будет содержать

17 + 4 + 4 + 8 + 17 = 50 bytes/row

в худшем случае, когда все поля varchar полностью заполнены. Это составляет

50 bytes/row x 1 440 000 rows/day = 72 000 000 bytes/day

или ~ 69 МБ в день.

В то время как это не сильно звучит, требование к пространству хранения в реальной базе данных было бы непомерно (опять же, используемые здесь цифры только для иллюстрации). Поэтому мы разделили данные измерений на индекс и таблицу значений, как объяснялось ранее в вопросе:

CREATE TABLE measurement_data_index
(
  id SERIAL,
  fk_device_name VARCHAR(16) NOT NULL,
  fk_spot_id INTEGER NOT NULL,
  t_stamp TIMESTAMP NOT NULL,
  -- constraints...
);

CREATE TABLE measurement_data_value
(
  id INTEGER NOT NULL,
  fk_parameter_id INTEGER NOT NULL,
  value VARCHAR(16) NOT NULL,
  -- constraints...
);

где идентификатор строки значений равен идентификатору индекса, который он принадлежит.

Размеры строки в таблицах индексов и значений

index: 4 + 17 + 4 + 8 = 33 bytes
value: 4 + 4 + 17     = 25 bytes

(опять же, наихудший сценарий). Общее количество строк

index: 10 devices x 10 spots x 1440 meas/day =   144 000 rows/day
value: 10 parameters x 144 000 rows/day      = 1 440 000 rows/day

поэтому общее число

index: 33 bytes/row x   144 000 rows/day =  4 752 000 bytes/day
value: 25 bytes/row x 1 440 000 rows/day = 36 000 000 bytes/day
total:                                   = 40 752 000 bytes/day

или ~ 39 МБ в день - в отличие от ~ 69 МБ для решения одной таблицы.

Обновить 02 (re: ответ wildplassers):

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

Изменения по сравнению с первоначальным подходом (несколько в порядке важности):

  • временная метка swap и параметр, т.е. переместите поле t_stamp из таблицы measurement_data_index в measurement_data_value и переместите поле fk_parameter_id из значения в таблицу индексов: при этом изменении все поля в таблице индексов являются постоянными и новыми измерениями данные записываются только в таблицу значений. Я не ожидал значительного улучшения производительности запроса (я был неправ), но я чувствую, что он делает концепцию индекса данных измерений более ясной. Хотя для этого требуется дробно большее пространство для хранения (согласно некоторой довольно грубой оценке), наличие "статической" таблицы индексов также может помочь в развертывании, когда tablespaces перемещаются на разные жесткие диски в соответствии с их требованиями к чтению/записи.
  • используйте суррогатный ключ в таблице устройств. Из того, что я понимаю, суррогатный ключ является первичным ключом, который строго не требуется с точки зрения дизайна базы данных (например, имя устройства уже уникально, поэтому оно также может служить PK), но может помочь повысить производительность запросов. Я добавил это, потому что, опять же, я чувствую, что это делает концепцию более ясной, если только индексная таблица ссылается только на идентификаторы (вместо некоторых имен и некоторых идентификаторов).
  • rewrite insert_data(): используйте generate_series() вместо вложенных FOR циклов; делает код намного "более быстрым".
  • В качестве побочного эффекта этих изменений вставка тестовых данных занимает примерно 50% времени, необходимого для первого решения.
  • Я не добавил представление, как предложил wildplasser; нет необходимости в обратной совместимости.
  • Дополнительные индексы для FK в индексной таблице, по-видимому, игнорируются планировщиком запросов и не влияют на план или производительность запроса.

(кажется, что без этой строки код ниже некорректно отображается как код на странице SO...)

\c postgres
DROP DATABASE IF EXISTS so_test_03;
CREATE DATABASE so_test_03;
\c so_test_03

CREATE TABLE device
(
  id SERIAL,
  name VARCHAR(16) NOT NULL,
  CONSTRAINT device_pk PRIMARY KEY (id),
  CONSTRAINT device_uk_name UNIQUE (name)
);

CREATE TABLE parameter
(
  id SERIAL,
  name VARCHAR(64) NOT NULL,
  CONSTRAINT parameter_pk PRIMARY KEY (id)
);

CREATE TABLE spot
(
  id SERIAL,
  name VARCHAR(16) NOT NULL,
  CONSTRAINT spot_pk PRIMARY KEY (id)
);

CREATE TABLE measurement_data_index
(
  id SERIAL,
  fk_device_id    INTEGER NOT NULL,
  fk_parameter_id INTEGER NOT NULL,
  fk_spot_id      INTEGER NOT NULL,
  CONSTRAINT measurement_pk PRIMARY KEY (id),
  CONSTRAINT measurement_data_index_fk_2_device FOREIGN KEY (fk_device_id)
    REFERENCES device (id) MATCH FULL
    ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT measurement_data_index_fk_2_parameter FOREIGN KEY (fk_parameter_id)
    REFERENCES parameter (id) MATCH FULL
    ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT measurement_data_index_fk_2_spot FOREIGN KEY (fk_spot_id)
    REFERENCES spot (id) MATCH FULL
    ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT measurement_data_index_uk_all_cols UNIQUE (fk_device_id, fk_parameter_id, fk_spot_id)
);

CREATE TABLE measurement_data_value
(
  id INTEGER NOT NULL,
  t_stamp TIMESTAMP NOT NULL,
  value VARCHAR(16) NOT NULL,
  -- NOTE: inverse field order compared to wildplassers version
  CONSTRAINT measurement_data_value_pk PRIMARY KEY (id, t_stamp),
  CONSTRAINT measurement_data_value_fk_2_index FOREIGN KEY (id)
    REFERENCES measurement_data_index (id) MATCH FULL
    ON UPDATE NO ACTION ON DELETE NO ACTION
);

CREATE OR REPLACE FUNCTION insert_data()
RETURNS VOID
LANGUAGE plpgsql
AS
$BODY$
  BEGIN
    INSERT INTO device (name)
    SELECT 'dev_' || to_char(item, 'FM00')
    FROM generate_series(1, 5) item;

    INSERT INTO parameter (name)
    SELECT 'param_' || to_char(item, 'FM00')
    FROM generate_series(1, 20) item;

    INSERT INTO spot (name)
    SELECT 'spot_' || to_char(item, 'FM00')
    FROM generate_series(1, 10) item;

    INSERT INTO measurement_data_index (fk_device_id, fk_parameter_id, fk_spot_id)
    SELECT device.id, parameter.id, spot.id
    FROM device, parameter, spot;

    INSERT INTO measurement_data_value(id, t_stamp, value)
    SELECT index.id,
           item,
           'd'  || to_char(index.fk_device_id,    'FM00') ||
           '_s' || to_char(index.fk_spot_id,      'FM00') ||
           '_p' || to_char(index.fk_parameter_id, 'FM00')
    FROM measurement_data_index index,
         generate_series('2012-01-01 00:00:00', '2012-01-06 23:59:59', interval '1 min') item;
  END;
$BODY$;

SELECT insert_data();

На каком-то этапе я изменю свои собственные соглашения на использование операторов inline PRIMARY KEY и REFERENCES вместо явного CONSTRAINT s; на данный момент я считаю, что поддерживать это так, как было проще, сравнивать эти два решения.

Не забудьте обновить статистику для планировщика запросов:

VACUUM ANALYZE device;
VACUUM ANALYZE measurement_data_index;
VACUUM ANALYZE measurement_data_value;
VACUUM ANALYZE parameter;
VACUUM ANALYZE spot;

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

EXPLAIN (ANALYZE ON, BUFFERS ON)
SELECT measurement_data_value.value
  FROM measurement_data_index,
       measurement_data_value,
       parameter
 WHERE measurement_data_index.fk_parameter_id = parameter.id
   AND measurement_data_index.id = measurement_data_value.id
   AND parameter.name = 'param_01';

Результат:

Nested Loop  (cost=0.00..34218.28 rows=431998 width=12) (actual time=0.026..696.349 rows=432000 loops=1)
  Buffers: shared hit=435332
  ->  Nested Loop  (cost=0.00..29.75 rows=50 width=4) (actual time=0.012..0.453 rows=50 loops=1)
        Join Filter: (measurement_data_index.fk_parameter_id = parameter.id)
        Buffers: shared hit=7
        ->  Seq Scan on parameter  (cost=0.00..1.25 rows=1 width=4) (actual time=0.005..0.010 rows=1 loops=1)
              Filter: ((name)::text = 'param_01'::text)
              Buffers: shared hit=1
        ->  Seq Scan on measurement_data_index  (cost=0.00..16.00 rows=1000 width=8) (actual time=0.003..0.187 rows=1000 loops=1)
              Buffers: shared hit=6
  ->  Index Scan using measurement_data_value_pk on measurement_data_value  (cost=0.00..575.77 rows=8640 width=16) (actual time=0.013..12.157 rows=8640 loops=50)
        Index Cond: (id = measurement_data_index.id)
        Buffers: shared hit=435325
Total runtime: 726.125 ms

Это почти половина из ~ 1.3s требуется первый подход; учитывая, что я загружаю 432K строк, это результат, с которым я могу жить на данный момент.

ПРИМЕЧАНИЕ: Порядок полей в таблице значений PK равен id, t_stamp; порядок в ответе wildplassers t_stamp, whw_id. Я сделал это таким образом, потому что я чувствую, что "регулярный" порядок полей - это тот, в котором поля перечислены в объявлении таблицы (а "обратное" - это наоборот), но это только мое собственное соглашение, которое мешает мне получить смущенный. В любом случае, как сказал Эрвин Брандштеттер, этот порядок является абсолютно критическим для улучшения производительности; если это неправильный путь (и обратный индекс, как в решении wildplassers отсутствует), план запроса выглядит ниже, а производительность более чем в 3 раза хуже:

Hash Join  (cost=22.14..186671.54 rows=431998 width=12) (actual time=0.460..2570.941 rows=432000 loops=1)
  Hash Cond: (measurement_data_value.id = measurement_data_index.id)
  Buffers: shared hit=63537
  ->  Seq Scan on measurement_data_value  (cost=0.00..149929.58 rows=8639958 width=16) (actual time=0.004..1095.606 rows=8640000 loops=1)
        Buffers: shared hit=63530
  ->  Hash  (cost=21.51..21.51 rows=50 width=4) (actual time=0.446..0.446 rows=50 loops=1)
        Buckets: 1024  Batches: 1  Memory Usage: 2kB
        Buffers: shared hit=7
        ->  Hash Join  (cost=1.26..21.51 rows=50 width=4) (actual time=0.015..0.359 rows=50 loops=1)
              Hash Cond: (measurement_data_index.fk_parameter_id = parameter.id)
              Buffers: shared hit=7
              ->  Seq Scan on measurement_data_index  (cost=0.00..16.00 rows=1000 width=8) (actual time=0.002..0.135 rows=1000 loops=1)
                    Buffers: shared hit=6
              ->  Hash  (cost=1.25..1.25 rows=1 width=4) (actual time=0.008..0.008 rows=1 loops=1)
                    Buckets: 1024  Batches: 1  Memory Usage: 1kB
                    Buffers: shared hit=1
                    ->  Seq Scan on parameter  (cost=0.00..1.25 rows=1 width=4) (actual time=0.004..0.007 rows=1 loops=1)
                          Filter: ((name)::text = 'param_01'::text)
                          Buffers: shared hit=1
Total runtime: 2605.277 ms
4b9b3361

ОТВЕТЫ

Ответ 1

Идея этого "решения" заключается в следующем: избегайте отдельных ключевых доменов для {device, spot, paramater}. Есть только 1000 возможных комбинаций этих трех. (можно рассматривать как плохой случай нарушения BCNF). Поэтому я объединяю их в одну таблицу what_how_where, которая ссылается на дерево отдельных доменов. Количество ключевых элементов в таблице измерений (данных) уменьшается с 4 до 2, а суррогатный ключ опущен (поскольку он не используется) Таблица what_how_where имеет суррогатный ключ. Значение я может быть выражено как: если в этой таблице присутствует кортеж: параметр "что" может быть измерен устройством "как" на месте "where".

-- temp schema for scratch
DROP SCHEMA tmp CASCADE;
CREATE SCHEMA tmp;
SET search_path=tmp;

        -- tables for the three "key domain"s
CREATE TABLE device
        ( id SERIAL NOT NULL PRIMARY KEY
        , dname VARCHAR NOT NULL -- 'name' might be a reserve word
        , CONSTRAINT device_name UNIQUE (dname)
        );

CREATE TABLE parameter
        ( id SERIAL PRIMARY KEY -- must have ID as names are not unique
        , pname VARCHAR NOT NULL
        );

CREATE TABLE spot
        ( id SERIAL PRIMARY KEY
        , sname VARCHAR NOT NULL
        );
        -- One table to combine the three "key domain"s
CREATE TABLE what_how_where
        ( id SERIAL NOT NULL PRIMARY KEY
        , device_id INTEGER NOT NULL REFERENCES device(id)
        , spot_id INTEGER NOT NULL REFERENCES spot(id)
        , parameter_id INTEGER NOT NULL REFERENCES parameter(id)
        , CONSTRAINT what_natural UNIQUE (device_id,spot_id,parameter_id)
        );

CREATE TABLE measurement
        ( whw_id INTEGER NOT NULL REFERENCES what_how_where(id)
        , t_stamp TIMESTAMP NOT NULL
        , value VARCHAR(32) NOT NULL
        , CONSTRAINT measurement_natural PRIMARY KEY (t_stamp,whw_id)
        );

INSERT INTO device (dname)
SELECT 'dev_' || d::text
FROM generate_series(1,10) d;

INSERT INTO parameter (pname)
SELECT 'param_' || p::text
FROM generate_series(1,10) p;

INSERT INTO spot (sname)
SELECT 'spot_' || s::text
FROM generate_series(1,10) s;

INSERT INTO what_how_where (device_id,spot_id,parameter_id)
SELECT d.id,s.id,p.id
FROM device d
JOIN spot s ON(1=1)
JOIN parameter p ON(1=1)
        ;
ANALYSE what_how_where;

INSERT INTO measurement(whw_id, t_stamp, value)
SELECT w.id
        , g
        , random()::text
FROM what_how_where w
JOIN generate_series('2012-01-01'::date, '2012-09-23'::date, '1 day'::interval) g
        ON (1=1)
        ;

CREATE UNIQUE INDEX measurement_natural_reversed ON measurement(whw_id,t_stamp);
ANALYSE measurement;

        -- A view to *more or less* emulate the original behaviour
DROP VIEW measurement_data ;
CREATE VIEW measurement_data AS (
        SELECT d.dname AS dname
        , p.pname AS pname
        , w.spot_id AS spot_id
        , w.parameter_id AS parameter_id
        , m.t_stamp AS t_stamp
        , m.value AS value
        FROM measurement m
        JOIN what_how_where w ON m.whw_id = w.id
        JOIN device d ON w.device_id = d.id
        JOIN parameter p ON w.parameter_id = p.id
        );


EXPLAIN (ANALYZE ON, BUFFERS ON)
SELECT md.value
  FROM measurement_data md
 WHERE md.pname = 'param_8'
   AND md.t_stamp >= '2012-07-01'
   AND md.t_stamp < '2012-08-01'
        ;

ОБНОВЛЕНИЕ: существует одна практическая проблема, которая может быть решена только путем кластеризации:

  • с оценкой размера строки в 50 байт.
  • и требуется конкретность запроса только 5% (1/20) параметров
  • что означает, что около 4 "желаемых" кортежей живут на странице диска ОС (+76 нежелательных кортежей)

Без кластеризации это означает, что все страницы должны быть проверены на+. Индексы здесь не помогают (они помогают только в том случае, если они могут избежать втягивания страниц, это может быть случай для (диапазона) поиска в первом ключевом столбце (столбцах)). Индексы могут помочь бит для сканирования в памяти после того, как они были извлечены.

Как следствие, это означает, что (как только размер вашего запроса будет больше, чем доступное буферное пространство), ваш запрос фактически измеряет скорость ввода-вывода вашего компьютера.

Ответ 2

Я в основном пересмотрел всю вашу настройку. Протестировано в PostgreSQL 9.1.5.

Схема DB

  • Я думаю, что ваш макет таблицы имеет большой логический недостаток (как также отметил @Catcall). Я изменил его так, как я подозреваю, это должно быть:
    Предполагается, что ваша последняя таблица measurement_data_value (которую я переименовал в measure_val) сохранил значение за parameter (теперь: param) для каждой строки в measurement_data_index (теперь: measure). См. Ниже.

  • Несмотря на то, что "устройство имеет уникальное имя", в любом случае используйте первичный ключ с суррогатным ключом. Текстовые строки по своей сути более громоздки и медленнее, чем внешние ключи в больших таблицах. Они также подвержены сортировке, что может значительно замедлить запросы.

    В рамках этого связанного вопроса мы обнаружили, что объединение и сортировка на столбце text среднего размера были основным замедлением. Если вы настаиваете на использовании текстовой строки в качестве первичного ключа, прочитайте в поддержку сортировки в PostgreSQL 9.1 или новее.

  • Не подпадайте под анти-шаблон с использованием id в качестве имени для первичного ключа. Когда вы присоединитесь к нескольким таблицам (например, вам придется много делать!), Вы получите несколько столбцов name id - какой беспорядок! (К сожалению, некоторые ОРМ используют его.)

    Вместо этого назовите суррогатный столбец первичного ключа после таблицы, чтобы как-то сделать его значимым. Тогда вы можете иметь внешние ключи, ссылающиеся на него, с тем же именем (что хорошо, поскольку они содержат одни и те же данные).

    CREATE TABLE spot
( spot_id SERIAL PRIMARY KEY);

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

  • Не используйте varchar(n), если у вас нет веской причины. Просто используйте varchar или проще: просто text.

Все это и многое другое вошли в мое предложение о лучшей схеме db:

CREATE TABLE device
( device_id serial PRIMARY KEY 
 ,device text NOT NULL
);

CREATE TABLE param
( param_id serial PRIMARY KEY
 ,param text NOT NULL
);
CREATE INDEX param_param_idx ON param (param); -- you are looking up by name!

CREATE TABLE spot
( spot_id  serial PRIMARY KEY);

CREATE TABLE measure
( measure_id serial PRIMARY KEY
 ,device_id int NOT NULL REFERENCES device (device_id) ON UPDATE CASCADE
 ,spot_id int NOT NULL REFERENCES spot (spot_id) ON UPDATE CASCADE
 ,t_stamp timestamp NOT NULL
 ,CONSTRAINT measure_uni UNIQUE (device_id, spot_id, t_stamp)
);

CREATE TABLE measure_val   -- better name? 
( measure_id int NOT NULL REFERENCES measure (measure_id)
                 ON UPDATE CASCADE ON DELETE CASCADE  -- guessing it fits
 ,param_id int NOT NULL REFERENCES param (param_id)
                 ON UPDATE CASCADE ON DELETE CASCADE  -- guessing it fits
 ,value text NOT NULL
 ,CONSTRAINT measure_val_pk PRIMARY KEY (measure_id, param_id)
);
CREATE INDEX measure_val_param_id_idx ON measure_val (param_id);  -- !crucial!

Я переименовал массивный measurement_data_value в measure_val, потому что это то, что в таблице: значения параметра для измерений. Теперь также имеет смысл multi-column pk.

Но я добавил отдельный индекс на param_id. То, как у вас было это, столбец param_id был вторым столбцом в индексе с несколькими столбцами, что приводит к плохим результатам для param_id. Прочитайте все подробности о том, что связано с этим вопросом на dba.SE.

После реализации этого запроса ваш запрос должен быть быстрее. Но вы можете сделать больше.

Тестируемые данные

Это заполняет данные намного быстрее. Дело в том, что я использую команды DML на основе набора, выполняя массовые вставки вместо циклов, которые выполняют отдельные вставки, которые берут навсегда. Имеет большое значение для значительного количества тестовых данных, которые вы хотите вставить. Это также намного короче и проще.

Чтобы сделать его еще более эффективным, я использую изменяющий данные CTE (новый в Postgres 9.1), который мгновенно повторно использует огромное количество строк на последнем шаге.

CREATE OR REPLACE FUNCTION insert_data()
RETURNS void LANGUAGE plpgsql AS
$BODY$
BEGIN
   INSERT INTO device (device)
   SELECT 'dev_' || to_char(g, 'FM00')
   FROM generate_series(1,5) g;

   INSERT INTO param (param)
   SELECT 'param_' || to_char(g, 'FM00')
   FROM generate_series(1,20) g;

   INSERT INTO spot (spot_id)
   SELECT nextval('spot_spot_id_seq'::regclass)
   FROM generate_series(1,10) g; -- to set sequence, too

   WITH x AS (
      INSERT INTO measure (device_id, spot_id, t_stamp)
      SELECT d.device_id, s.spot_id, g
      FROM   device    d
      CROSS  JOIN spot s
      CROSS  JOIN generate_series('2012-01-06 23:00:00' -- smaller set
                                 ,'2012-01-07 00:00:00' -- for quick tests
                                 ,interval '1 min') g
      RETURNING *
      )
   INSERT INTO measure_val (measure_id, param_id, value)
   SELECT x.measure_id
         ,p.param_id
         ,x.device_id || '_' || x.spot_id || '_' || p.param
   FROM  x
   CROSS JOIN param p;
END
$BODY$;

Вызов:

SELECT insert_data();

Query

  • Использовать явный синтаксис JOIN и таблицу aliased, чтобы упростить чтение и отладку ваших запросов:
SELECT v.value
FROM   param p
JOIN   measure_val v USING (param_id)
WHERE  p.param = 'param_01';

Предложение USING предназначено только для упрощения синтаксиса, но не превосходит ON в противном случае.

Это должно быть намного быстрее теперь по двум причинам:

  • Индекс param_param_idx на param.param.
  • Индекс measure_val_param_id_idx на measure_val.param_id, как подробно описано здесь.

Редактировать после обратной связи

Мой главный надзор заключался в том, что вы уже добавили ключевой индекс в форме measurement_data_value_idx_fk_parameter_id дальше в своем вопросе. (Я обвиняю ваши загадочные имена!: P) При ближайшем рассмотрении у вас есть больше 10M (7 * 24 * 60 * 5 * 10 * 20) строк в вашей тестовой настройке, и ваш запрос извлекает > 500K. Я тестировал только с гораздо меньшим подмножеством.

Кроме того, по мере того, как вы извлекаете 5% всей таблицы, индексы будут пока заходить. Я был оптимистом, такой объем данных должен занять некоторое время. Это реалистичное требование, чтобы вы запрашивали строки 500k? Я бы предположил, что вы совокупны в своем реальном приложении?

Дополнительные параметры

  • Partitioning.

  • Больше ОЗУ и настроек, которые его используют.

    Виртуальная машина Debian 6.0 с 1 ГБ оперативной памяти

    находится ниже того, что вам нужно.

  • Частичные индексы, особенно в связи с проверками только для индекса PostgreSQL 9.2.

  • Материализованные представления агрегированных данных. Очевидно, вы не собираетесь отображать строки в 500K, а какие-то агрегации. Вы можете вычислить это один раз и сохранить результаты в материализованном виде, откуда вы можете быстрее получать данные.

  • Если ваши запросы преобладают по параметру (например, пример), вы можете использовать CLUSTER физически переписать таблицу в соответствии с индексом:

    CLUSTER measure_val USING measure_val_param_id_idx

    Таким образом, все строки для одного параметра сохраняются последовательно. Означает меньшее количество блоков для чтения и упрощения кэширования. Должен сделать запрос под рукой гораздо быстрее. Или INSERT строки в благоприятном порядке для начала, с тем же эффектом.
    Разделение будет хорошо сочетаться с CLUSTER, так как вам не придется переписывать всю (огромную) таблицу каждый раз. Поскольку ваши данные, очевидно, просто вставлены и не обновлены, раздел останется "в порядке" после CLUSTER.

  • Как правило, PostgreSQL 9.2 должен быть отличным для вас, поскольку его улучшения фокусируются на производительности с большими данными.

Ответ 3

Я не вижу, как вы относитесь к определенному измеренному значению с определенной комбинацией устройства, места и времени. Мне что-то не хватает?

Посмотрите на это по-другому.

CREATE TABLE measurement_data
(
  device_name character varying(16) NOT NULL,
  spot_id integer NOT NULL,
  parameter_id integer NOT NULL,
  t_stamp timestamp without time zone NOT NULL,
  value character varying(16) NOT NULL,
  CONSTRAINT measurement_data_pk PRIMARY KEY (device_name , spot_id , t_stamp , parameter_id ),
  CONSTRAINT measurement_data_fk_device FOREIGN KEY (device_name)
      REFERENCES device (name) MATCH FULL
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT measurement_data_fk_parameter FOREIGN KEY (parameter_id)
      REFERENCES parameter (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT measurement_data_fk_spot FOREIGN KEY (spot_id)
      REFERENCES spot (id) MATCH FULL
      ON UPDATE NO ACTION ON DELETE NO ACTION
);

(Еще лучшее название этой таблицы - "измерения". Каждая таблица содержит данные.)

Я ожидал бы гораздо более высокую производительность на этом столе. Но я также ожидаю, что любой запрос, который возвращает много, много строк, чтобы бороться с производительностью. (Если аппаратное обеспечение и сеть не соответствуют задаче.)

Ответ 4

Кажется, из цифр вы попадаете из-за нехватки времени. Вы можете проверить это, используя pg_test_timing или добавив timing off к вашим параметрам объяснения (оба введены в PostgreSQL версии 9.2). Я могу приблизительно воспроизвести ваши результаты, переключив настройку моего источника синхронизации на HPET вместо TSC.

С помощью HPET:

 Nested Loop  (cost=8097.73..72850.98 rows=432000 width=12) (actual time=29.188..905.765 rows=432000 loops=1)
   Buffers: shared hit=56216
   ->  Seq Scan on parameter  (cost=0.00..1.25 rows=1 width=4) (actual time=0.004..0.008 rows=1 loops=1)
         Filter: ((name)::text = 'param_01'::text)
         Rows Removed by Filter: 19
         Buffers: shared hit=1
   ->  Bitmap Heap Scan on measurement_data_value  (cost=8097.73..68529.73 rows=432000 width=16) (actual time=29.180..357.848 rows=432000 loops=1)
         Recheck Cond: (fk_parameter_id = parameter.id)
         Buffers: shared hit=56215
         ->  Bitmap Index Scan on measurement_data_value_idx_fk_parameter_id  (cost=0.00..7989.73 rows=432000 width=0) (actual time=21.710..21.710 rows=432000 loops=1)
               Index Cond: (fk_parameter_id = parameter.id)
               Buffers: shared hit=1183
 Total runtime: 1170.409 ms

С отключением HPET и временем:

 Nested Loop  (cost=8097.73..72850.98 rows=432000 width=12) (actual rows=432000 loops=1)
   Buffers: shared hit=56216
   ->  Seq Scan on parameter  (cost=0.00..1.25 rows=1 width=4) (actual rows=1 loops=1)
         Filter: ((name)::text = 'param_01'::text)
         Rows Removed by Filter: 19
         Buffers: shared hit=1
   ->  Bitmap Heap Scan on measurement_data_value  (cost=8097.73..68529.73 rows=432000 width=16) (actual rows=432000 loops=1)
         Recheck Cond: (fk_parameter_id = parameter.id)
         Buffers: shared hit=56215
         ->  Bitmap Index Scan on measurement_data_value_idx_fk_parameter_id  (cost=0.00..7989.73 rows=432000 width=0) (actual rows=432000 loops=1)
               Index Cond: (fk_parameter_id = parameter.id)
               Buffers: shared hit=1183
 Total runtime: 156.537 ms

С TSC:

 Nested Loop  (cost=8097.73..72850.98 rows=432000 width=12) (actual time=29.090..156.233 rows=432000 loops=1)
   Buffers: shared hit=56216
   ->  Seq Scan on parameter  (cost=0.00..1.25 rows=1 width=4) (actual time=0.004..0.008 rows=1 loops=1)
         Filter: ((name)::text = 'param_01'::text)
         Rows Removed by Filter: 19
         Buffers: shared hit=1
   ->  Bitmap Heap Scan on measurement_data_value  (cost=8097.73..68529.73 rows=432000 width=16) (actual time=29.083..114.908 rows=432000 loops=1)
         Recheck Cond: (fk_parameter_id = parameter.id)
         Buffers: shared hit=56215
         ->  Bitmap Index Scan on measurement_data_value_idx_fk_parameter_id  (cost=0.00..7989.73 rows=432000 width=0) (actual time=21.667..21.667 rows=432000 loops=1)
               Index Cond: (fk_parameter_id = parameter.id)
               Buffers: shared hit=1183
 Total runtime: 168.869 ms

Таким образом, ваша медлительность, по-видимому, в основном вызвана аппаратными средствами. Тем не менее, выбор огромного количества строк не будет чрезвычайно быстрым в PostgreSQL. Если вам нужно делать хруст числа на больших объёмах данных, может быть хорошей идеей структурировать ваши данные, чтобы вы могли получить их в больших кусках. (например, если вам нужно всегда обрабатывать данные за несколько дней, соберите все измерения за один день в массиве)

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