У меня есть две таблицы, 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 мне это не по силам. Все решения временных интервалов, которые я пробовал при поиске в Google или поиске на 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
conttagtableзапись приходится около 200 000contfloattableзаписей. Как вы сведете это к одной выходной строке для каждогоconttagtable.tagname? - person ruakh schedule 16.10.2012