Постгрес оптимизация запроса?

Question

[des1roer]

PostgreSQL 9.3.3 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-4), 64-bit

/------ 04.02.2015 8:40:12 --------/

EXPLAIN ANALYZE SELECT EXTRACT(EPOCH FROM "F_Date"), "F_ConvertedValue", "F_TagName_ID" FROM "SC_Tag"."T_TagData" where "F_Date" > (localtimestamp - interval'1 hour') and "F_TagName_ID" in (73,72,39,64,76,75)

            ORDER BY "F_Date" asc;;

/* Результат : "LOG: duration: 11276.442 ms statement: EXPLAIN ANALYZE SELECT EXTRACT(EPOCH FROM "F_Date"), "F_ConvertedValue", "F_TagName_ID" FROM "SC_Tag"."T_TagData" where "F_Date" > (localtimestamp - interval'1 hour') and "F_TagName_ID" in (73,72,39,64,76,75) ORDER BY "F_Date" asc;;

возвращено записей: 7 (выполнено: 11,279 с; всего: 11,279 с)" */

/------ 04.02.2015 8:49:52 --------/

EXPLAIN ANALYZE SELECT EXTRACT(EPOCH FROM "F_Date"), "F_ConvertedValue", "F_TagName_ID" FROM "SC_Tag"."T_TagData" where "F_Date" > (localtimestamp - interval'1 hour') and "F_TagName_ID" in (73,72,39,64,76,75)

            --ORDER BY "F_Date" asc;;

/* Результат : "LOG: duration: 9359.324 ms statement: EXPLAIN ANALYZE SELECT EXTRACT(EPOCH FROM "F_Date"), "F_ConvertedValue", "F_TagName_ID" FROM "SC_Tag"."T_TagData" where "F_Date" > (localtimestamp - interval'1 hour') and "F_TagName_ID" in (73,72,39,64,76,75)

возвращено записей: 4 (выполнено: 9,360 с; всего: 9,375 с)" */

это вообще кошмарно. может имеет смысл сортировать уже на клиенте али может какие другие соображения будут?

Answer 1

[Дмитрий Энтелис]

des1roer: CREATE INDEX насколько понимаю создает индексы для наиболее часто используемых данных. но если данные динамические то как это сделать и поможет ли

des1roer: Вы понимаете не правильно.

1) индекс нужен для ускорения поиска и сортировки

2) без "order by" порядок кортежей вообще не гарантирован.
даже с "order by" если у нескольких кортежей совпадают значения в поле по которому идет сортировка - их порядок не гарантирован.
индекс по (F_Date,F_TagName_ID) существенно ускорит выборку я полагаю

3)В порядке бреда: т.к в таблице у Вас весьма много записей - есть смысл обдумать так ли их надо хранить в реляционной базе, или можно агрегировать в списки например по часам и класть в какую нибудь монгу? Ну или хотя бы чистить старые записи. Или переносить их в другую таблицу.

Comments

[des1roer]

совет 2 помог. а вот насчет данных - то есть он в любом случае будет прощелкивать вес записи даж не смотря что указан таймстамп?

[Дмитрий Энтелис]

des1roer: если нет индекса - да, конечно, он делает полный скан таблицы, т.к данные в ней не отсортированы. индекс это по сути отсортированный набор значений. по нему он быстро находит нужные записи

Answer 2

[krypt3r]

Начните с CREATE INDEX

Comments

[des1roer]

данные поступают со 100 датчиков раз в полминуты. необходимо отображение графика за последний день. CREATE INDEX насколько понимаю создает индексы для наиболее часто используемых данных. но если данные динамические то как это сделать и поможет ли? база же вроде сама более новые более актульными считает

[des1roer]

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

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

Получается динамически создавть индексы для данных последних дней не получится?

[Дмитрий Энтелис]

des1roer: " но блокирует запись (вставку, обновление и удаление) до конца построения индекса"
создайте новую пустую таблицу. создайте в ней индекс.
переключите запись данных с датчиков на неё.
дозалейте в таблицу старые данные.
простой будет миннимален

[Дмитрий Энтелис]

des1roer: "Получается динамически создавть индексы для данных последних дней не получится?" - создайте отдельную таблицу для данных последних дней. Все лишние записи из неё переносите в другую таблицу

[Дмитрий Энтелис]

"Это добавляет дополнительные расходы на операции с данными" - если много сложных индексов - это существенно замедляет вставку. От 1 индекса думаю ничего не ухудшится