У меня есть большая таблица (> строки на 50 м), который имеет некоторые данные с идентификатором и меткой времени:
id, timestamp, data1, ..., dataN
... с многостолбцовым индексом на (id, timestamp)
.
Я должен запросить таблицу для выбора всех строк с определенным идентификатором, где метка времени между двумя датами, которые я в настоящее время делаю использование:
SELECT * FROM mytable WHERE id = x AND timestamp BETWEEN y AND z
Это в настоящее время принимает 2 минуты на высококачественной машине (2x двухъядерные Xeon на 3 ГГц w/HT, 16 ГБ RAM, 2x диски на 1 ТБ в RAID 0), и я действительно хотел бы ускорить его.
Я нашел эту подсказку, которая рекомендует использовать пространственный индекс, но пример, который она дает, для IP-адресов. Однако увеличение скорости (436 с к 3 с) является впечатляющим.
Как я могу использовать это с метками времени?
Не могли бы вы ОБЪЯСНИТЬ запрос для нас? Тогда мы узнаем, как база данных выполняет ваш запрос. А что с конфигурацией? Какие настройки для shared_buffers и work_mem? А когда вы (или вашу систему) последний раз вакуумировали и анализировали? И последнее, какую ОС и pgSQL-версию вы используете?
Вы можете создавать замечательные индексы, но без правильных настроек база данных не может использовать их очень эффективно.
Этот совет подходит только когда у вас есть два столбца A и B и вы используете такие запросы, как:
where 'a' between A and B
Это не так:
where A between 'a' and 'b'
Использование индекса по date (столбец)
вместо столбца
может немного ускорить его немного.
Убедитесь, что индекс - TableID+TableTimestamp, и сделайте запрос типа:
SELECT
....
FROM YourTable
WHERE TableID=..YourID..
AND TableTimestamp>=..startrange..
AND TableTimestamp<=..endrange..
если вы примените функции к столбцу TableTimestamp таблицы в WHERE, вы не сможете полностью использовать индекс.
если вы уже делаете все это, то ваше оборудование может не справиться с задачей.
если вы используете версию 8.2 или более позднюю, вам следует попробовать:
WHERE (TableID, TableTimestamp) >= (..YourID.., ..startrange.. )
and (TableID, TableTimestamp) <= (..YourID.., ..endrange..)