Arhn - архитектура программирования

Как улучшить этот запрос Postgres?

У меня есть sql-запрос:

SELECT cast(AVG(SNR) AS integer) AS snr,
       cast(AVG(RSSI) AS integer) AS rts
FROM SESSION
WHERE DATE(associationtime)>DATE(NOW()- INTERVAL '21 DAYS');

Работает медленно, потому что 21 день содержит 300 тыс. строк.

 Aggregate  (cost=21768.07..21768.09 rows=1 width=8) (actual time=346.794..346.795 rows=1 loops=1)
   ->  Seq Scan on session  (cost=0.00..20095.77 rows=334459 width=8) (actual time=0.014..282.512 rows=345304 loops=1)
         Filter: (date(associationtime) > date((now() - '21 days'::interval)))
         Rows Removed by Filter: 148508
 Total runtime: 346.867 ms

Как я могу улучшить свой запрос? Могу ли я создать индекс или что-то в этом роде?

ОБНОВЛЕНИЕ:

Индекс на associationtime не помогает.

postgres=# CREATE INDEX session_lim_values_idx ON session (associationtime);
CREATE INDEX
postgres=# EXPLAIN (ANALYZE) SELECT cast(AVG(SNR) as integer) as snr, cast(AVG(RSSI) as integer)  as rts FROM session WHERE DATE(associationtime)>DATE(NOW()- INTERVAL '21 DAYS');
                                                      QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=21768.07..21768.09 rows=1 width=8) (actual time=347.654..347.654 rows=1 loops=1)
   ->  Seq Scan on session  (cost=0.00..20095.77 rows=334459 width=8) (actual time=0.014..283.344 rows=345304 loops=1)
         Filter: (date(associationtime) > date((now() - '21 days'::interval)))
         Rows Removed by Filter: 148508
 Total runtime: 347.731 ms

И DATE(associationtime) тоже:

postgres=# CREATE INDEX session_lim_values_idx ON session (DATE(associationtime));
CREATE INDEX
postgres=# EXPLAIN (ANALYZE) SELECT cast(AVG(SNR) as integer) as snr, cast(AVG(RSSI) as integer)  as rts FROM session WHERE DATE(associationtime)>DATE(NOW()- INTERVAL '21 DAYS');
                                                      QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=21768.07..21768.09 rows=1 width=8) (actual time=341.050..341.050 rows=1 loops=1)
   ->  Seq Scan on session  (cost=0.00..20095.77 rows=334459 width=8) (actual time=0.015..278.247 rows=345304 loops=1)
         Filter: (date(associationtime) > date((now() - '21 days'::interval)))
         Rows Removed by Filter: 148508
 Total runtime: 341.129 ms
postgresql sql
14.07.2014

  • Какой тип данных associationtime 14.07.2014
  • Какую часть таблицы вы выбираете? Что count(*) from session против count(*) from session where associationtime > ... 14.07.2014
  • Индекс на associationTime не поможет, потому что вы вызываете для него функцию (которая должна выполнять некоторые сложные математические операции, не меньше). Это, вероятно, тот индекс, который вам действительно нужен (более полезный, чем индекс с функцией), что означает, что ваши критерии поиска должны быть чем-то вроде associationTime >= CURRENT_DATE - INTERVAL '20 DAYS'. 14.07.2014
  • @Clockwork-Muse: для этого предназначен индекс на основе функций (и который, по-видимому, не работает) 14.07.2014
  • @a_horse_with_no_name - я это знаю, просто я не считаю, что вариант, основанный на DATE(...), очень полезен (учитывая, что запрашивать диапазон отметок времени тривиально). 14.07.2014

Ответы:


1

Поскольку вас интересуют только целые дни, вы можете кэшировать результат в материализованном представлении.

CREATE MATERIALIZED VIEW matview_avg_session
AS SELECT cast(AVG(SNR) AS integer) AS snr,
   cast(AVG(RSSI) AS integer) AS rts
   FROM SESSION
   WHERE DATE(associationtime) > DATE(NOW()- INTERVAL '21 DAYS');

затем получите доступ к данным следующим образом:

SELECT * FROM matview_avg_session;

и обновите его (автоматически один раз в день) следующим образом:

REFRESH MATERIALIZED VIEW matview_avg_session;

или вы посмотрите на этот ответ о том, как создать триггер для его обновления, но имейте в виду, что вы не хотите делать это после КАЖДОЙ вставки... Автоматическое обновление материализованного представления с помощью правила или уведомления

14.07.2014

2

Вы можете использовать покрывающий индекс, т. е. заставить исполнителя использовать сканирование только по индексу.

Для охвата индекса вы сначала добавляете столбцы, используемые в предложениях where, затем столбцы, используемые в group by, затем столбцы, используемые в порядке, а затем столбцы, используемые в select.

  ALTER TABLE session ADD KEY ix1(date(associationtime), <remaining_columns>);

где <remaining_columns> это те, которые вы должны написать в предложении group by в заявлении SFW.

14.07.2014

3

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

SELECT cast(AVG(SNR) AS integer) AS snr,
       cast(AVG(RSSI) AS integer) AS rts
FROM SESSION
WHERE associationtime > DATE(NOW() - INTERVAL '20 DAYS');

Если это не поможет сделать VACUUM ANALYZE на нем, попробуйте еще раз.

14.07.2014
  • Вы пропускаете полночь указанной даты... или, скорее, вы считаете большую часть дополнительного дня (все, кроме полуночи), который не был включен в исходный набор. 14.07.2014
  • @Clockwork-Muse Вы правы, я исправил большую часть лишнего дня. Потеря полуночи верна, но с 300 тысячами записей это не должно иметь большого значения. 14.07.2014
    • Новые материалы
    Коллекции публикаций по глубокому обучению
    Последние пару месяцев я создавал коллекции последних академических публикаций по различным подполям глубокого обучения в моем блоге https://amundtveit.com - эта публикация дает обзор 25..
    Представляем: Pepita
    Фреймворк JavaScript с открытым исходным кодом Я знаю, что недостатка в фреймворках JavaScript нет. Но я просто не мог остановиться. Я хотел написать что-то сам, со своими собственными..
    Советы по коду Laravel #2
    1-) Найти // You can specify the columns you need // in when you use the find method on a model User::find(‘id’, [‘email’,’name’]); // You can increment or decrement // a field in..
    Работа с временными рядами спутниковых изображений, часть 3 (аналитика данных)
    Анализ временных рядов спутниковых изображений для данных наблюдений за большой Землей (arXiv) Автор: Рольф Симоэс , Жильберто Камара , Жильберто Кейрос , Фелипе Соуза , Педро Р. Андраде ,..
    3 способа решить квадратное уравнение (3-й мой любимый) -
    1. Методом факторизации — 2. Используя квадратичную формулу — 3. Заполнив квадрат — Давайте поймем это, решив это простое уравнение: Мы пытаемся сделать LHS,..
    Создание VR-миров с A-Frame
    Виртуальная реальность (и дополненная реальность) стали главными модными терминами в образовательных технологиях. С недорогими VR-гарнитурами, такими как Google Cardboard , и использованием..
    Демистификация рекурсии
    КОДЕКС Демистификация рекурсии Упрощенная концепция ошеломляющей О чем весь этот шум? Рекурсия, кажется, единственная тема, от которой у каждого начинающего студента-информатика..