Эффективное обращение к таблице огромных временных рядов по одной строке каждые 15 минут

У меня две таблицы: conttagtable (t) и contfloattable (cf). T имеет около 43 тыс. Строк. CF имеет более 9 миллиардов.

Я создал индекс для обеих таблиц в столбце tagindex для обеих таблиц. Этот столбец можно рассматривать как уникальный идентификатор для conttagtable и как внешний ключ в conttagtable для confloattable . Я явно не создал PK или внешний ключ в любой таблице, относящейся к другой, хотя эти данные логически связаны tagindex на обеих таблицах, как если бы conttagtable.tagindex были PRIMARY KEY и contfloattable.tagindex где FOREIGN KEY (tagindex) REFERENCES conttagtable(tagindex) . Данные поступали с дампа доступа к Microsoft и я не знал, могу ли я доверять tagindex быть уникальным, поэтому «уникальность» не применяется.

Сами данные чрезвычайно велики.

Мне нужно получить одиночную произвольно выбранную строку из contfloattable для каждого 15-минутного contfloattable.dateandtime интервала для каждого conttagtable.tagid . Таким образом, если у contfloattable для данного tagid есть 4000 образцов, охватывающих 30 минут, мне нужен образец из диапазона 0-14 минут и образец из диапазона 15-30 минут. Любой образец в пределах 15 минут допустим; 1-й, последний, случайный, любой.

В двух словах, мне нужно получить образец каждые 15 минут, но только один образец на t.tagname. Образцы прямо сейчас записываются каждые 5 секунд, а данные охватывают два года. Это большая проблема с данными и мой взгляд с точки зрения sql. Все решения временного интервала, которые я пробовал из поиска или поиска в SO, дали время запроса, которое так долго, что они непрактичны.

  • Являются ли мои индексы достаточными для быстрого соединения? (они, как представляется, оставляют часть временного интервала)
  • Получаю ли я выгоду от добавления каких-либо других индексов?
  • Каков наилучший / самый быстрый запрос, который достигает вышеуказанных целей?

Вот SQLFiddle, содержащий схему и некоторые примеры данных: http://sqlfiddle.com/#!1/c7d2f/2

Схема:

  Table "public.conttagtable" (t) Column | Type | Modifiers -------------+---------+----------- tagname | text | tagindex | integer | tagtype | integer | tagdatatype | integer | Indexes: "tagindex" btree (tagindex) Table "public.contfloattable" (CF) Column | Type | Modifiers -------------+-----------------------------+----------- dateandtime | timestamp without time zone | millitm | integer | tagindex | integer | Val | double precision | status | text | marker | text | Indexes: "tagindex_contfloat" btree (tagindex) 

Результат, который я хотел бы увидеть, выглядит примерно так:

 cf.dateandtime |cf."Val"|cf.status|t.tagname -------------------------------------------------- 2012-11-16 00:00:02 45 S SuperAlpha 2012-11-16 00:00:02 45 S SuperBeta 2012-11-16 00:00:02 45 S SuperGamma 2012-11-16 00:00:02 45 S SuperDelta 2012-11-16 00:15:02 45 S SuperAlpha 2012-11-16 00:15:02 45 S SuperBeta 2012-11-16 00:15:02 45 S SuperGamma 2012-11-16 00:15:02 45 S SuperDelta 2012-11-16 00:30:02 45 S SuperAlpha 2012-11-16 00:30:02 45 S SuperBeta 2012-11-16 00:30:02 45 S SuperGamma 2012-11-16 00:30:02 45 S SuperDelta 2012-11-16 00:45:02 42 S SuperAlpha 

… и т. д. и т.д.

Как было предложено Клодоальдо, это моя последняя попытка, любые предложения по ее ускорению?

 with i as ( select cf.tagindex, min(dateandtime) dateandtime from contfloattable cf group by floor(extract(epoch from dateandtime) / 60 / 15), cf.tagindex ) select cf.dateandtime, cf."Val", cf.status, t.tagname from contfloattable cf inner join conttagtable t on cf.tagindex = t.tagindex inner join i on i.tagindex = cf.tagindex and i.dateandtime = cf.dateandtime order by floor(extract(epoch from cf.dateandtime) / 60 / 15), cf.tagindex 

План запроса из вышеперечисленного: http://explain.depesz.com/s/loR

В течение 15 минут:

 with i as ( select cf.tagindex, min(dateandtime) dateandtime from contfloattable cf group by floor(extract(epoch from dateandtime) / 60 / 15), cf.tagindex ) select cf.dateandtime, cf."Val", cf.status, t.tagname from contfloattable cf inner join conttagtable t on cf.tagindex = t.tagindex inner join i on i.tagindex = cf.tagindex and i.dateandtime = cf.dateandtime order by cf.dateandtime, t.tagname 

Покажите результат объяснения для этого запроса (если он работает), поэтому мы можем попытаться оптимизировать. Вы можете опубликовать его в этом ответе.

Объяснить вывод

 "Sort (cost=15102462177.06..15263487805.24 rows=64410251271 width=57)" " Sort Key: cf.dateandtime, t.tagname" " CTE i" " -> HashAggregate (cost=49093252.56..49481978.32 rows=19436288 width=12)" " -> Seq Scan on contfloattable cf (cost=0.00..38528881.68 rows=1408582784 width=12)" " -> Hash Join (cost=270117658.06..1067549320.69 rows=64410251271 width=57)" " Hash Cond: (cf.tagindex = t.tagindex)" " -> Merge Join (cost=270117116.39..298434544.23 rows=1408582784 width=25)" " Merge Cond: ((i.tagindex = cf.tagindex) AND (i.dateandtime = cf.dateandtime))" " -> Sort (cost=2741707.02..2790297.74 rows=19436288 width=12)" " Sort Key: i.tagindex, i.dateandtime" " -> CTE Scan on i (cost=0.00..388725.76 rows=19436288 width=12)" " -> Materialize (cost=267375409.37..274418323.29 rows=1408582784 width=21)" " -> Sort (cost=267375409.37..270896866.33 rows=1408582784 width=21)" " Sort Key: cf.tagindex, cf.dateandtime" " -> Seq Scan on contfloattable cf (cost=0.00..24443053.84 rows=1408582784 width=21)" " -> Hash (cost=335.74..335.74 rows=16474 width=44)" " -> Seq Scan on conttagtable t (cost=0.00..335.74 rows=16474 width=44)" 

Похоже, вам нужен этот индекс:

 create index cf_tag_datetime on contfloattable (tagindex, dateandtime) 

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

Обновить

Я добавил индекс cf_tag_datetime (tagindex, dateandtime), и вот новое объяснение:

 "Sort (cost=15349296514.90..15512953953.25 rows=65462975340 width=57)" " Sort Key: cf.dateandtime, t.tagname" " CTE i" " -> HashAggregate (cost=49093252.56..49490287.76 rows=19851760 width=12)" " -> Seq Scan on contfloattable cf (cost=0.00..38528881.68 rows=1408582784 width=12)" " -> Hash Join (cost=270179293.86..1078141313.22 rows=65462975340 width=57)" " Hash Cond: (cf.tagindex = t.tagindex)" " -> Merge Join (cost=270178752.20..298499296.08 rows=1408582784 width=25)" " Merge Cond: ((i.tagindex = cf.tagindex) AND (i.dateandtime = cf.dateandtime))" " -> Sort (cost=2803342.82..2852972.22 rows=19851760 width=12)" " Sort Key: i.tagindex, i.dateandtime" " -> CTE Scan on i (cost=0.00..397035.20 rows=19851760 width=12)" " -> Materialize (cost=267375409.37..274418323.29 rows=1408582784 width=21)" " -> Sort (cost=267375409.37..270896866.33 rows=1408582784 width=21)" " Sort Key: cf.tagindex, cf.dateandtime" " -> Seq Scan on contfloattable cf (cost=0.00..24443053.84 rows=1408582784 width=21)" " -> Hash (cost=335.74..335.74 rows=16474 width=44)" " -> Seq Scan on conttagtable t (cost=0.00..335.74 rows=16474 width=44)" 

Похоже, что это произошло вовремя 🙁 Однако, если я удалю порядок по предложению (не совсем то, что мне нужно, но будет работать), это то, что происходит, большое сокращение:

 "Hash Join (cost=319669581.62..1127631600.98 rows=65462975340 width=57)" " Hash Cond: (cf.tagindex = t.tagindex)" " CTE i" " -> HashAggregate (cost=49093252.56..49490287.76 rows=19851760 width=12)" " -> Seq Scan on contfloattable cf (cost=0.00..38528881.68 rows=1408582784 width=12)" " -> Merge Join (cost=270178752.20..298499296.08 rows=1408582784 width=25)" " Merge Cond: ((i.tagindex = cf.tagindex) AND (i.dateandtime = cf.dateandtime))" " -> Sort (cost=2803342.82..2852972.22 rows=19851760 width=12)" " Sort Key: i.tagindex, i.dateandtime" " -> CTE Scan on i (cost=0.00..397035.20 rows=19851760 width=12)" " -> Materialize (cost=267375409.37..274418323.29 rows=1408582784 width=21)" " -> Sort (cost=267375409.37..270896866.33 rows=1408582784 width=21)" " Sort Key: cf.tagindex, cf.dateandtime" " -> Seq Scan on contfloattable cf (cost=0.00..24443053.84 rows=1408582784 width=21)" " -> Hash (cost=335.74..335.74 rows=16474 width=44)" " -> Seq Scan on conttagtable t (cost=0.00..335.74 rows=16474 width=44)" 

Я еще не пробовал этот индекс … сделаю это, хотя. ожидание.

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

 create index cf_tag_datetime on contfloattable (dateandtime, tagindex) 

Вот еще одна формулировка. Мне будет очень интересно посмотреть, как он масштабируется в полном наборе данных. Сначала создайте этот индекс:

 CREATE INDEX contfloattable_tag_and_timeseg ON contfloattable(tagindex, (floor(extract(epoch FROM dateandtime) / 60 / 15) )); 

затем запустите это с таким же количеством work_mem сколько вы можете себе позволить:

 SELECT (first_value(x) OVER (PARTITION BY x.tagindex, floor(extract(epoch FROM x.dateandtime) / 60 / 15))).*, (SELECT t.tagname FROM conttagtable t WHERE t.tagindex = x.tagindex) AS tagname FROM contfloattable x ORDER BY dateandtime, tagname; 

Sneaky Wombat : Объясните сверху sql по полному набору данных (без предлагаемого индекса): http://explain.depesz.com/s/kGo

В качестве альтернативы, здесь требуется только один последовательный проход через contfloattable , со значениями, собранными в tuplestore, затем JOIN ed to, чтобы получить имя тега. Для этого требуется много work_mem :

 SELECT cf.dateandtime, cf.dataVal, cf.status, t.tagname FROM ( SELECT (first_value(x) OVER (PARTITION BY x.tagindex, floor(extract(epoch FROM x.dateandtime) / 60 / 15))).* FROM contfloattable x ) cf INNER JOIN conttagtable t ON cf.tagindex = t.tagindex ORDER BY cf.dateandtime, t.tagname; 

Sneaky Wombat : Объясните сверху sql по полному набору данных (без предлагаемого индекса): http://explain.depesz.com/s/57q

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

 SET work_mem = '500MB'; 

… или больше, если у вас есть как минимум 4 ГБ ОЗУ и находятся на 64-битном процессоре. Опять же, мне было бы очень интересно посмотреть, как это работает с полным набором данных.

BTW, для правильности этих запросов я бы посоветовал ALTER TABLE conttagtable ADD PRIMARY KEY (tagindex); затем DROP INDEX t_tagindex; , Это займет некоторое время, так как будет создан уникальный индекс. Большинство упомянутых здесь запросов предполагают, что t.tagindex является уникальным в conttagtable , и это действительно необходимо выполнить. Уникальный индекс можно использовать для дополнительных оптимизаций, которые старый не уникальный t_tagindex не может, и он дает гораздо лучшие статистические оценки.

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