Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Есть ли возможность оптимизации запроса? / 5 сообщений из 5, страница 1 из 1
03.05.2021, 10:46
    #40068027
zuev56
zuev56
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Есть ли возможность оптимизации запроса?
Есть таблица:
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
 sch.activity_log 
    activity_log_id   integer NOT NULL
    user_id           integer NOT NULL
    is_online         boolean
    insert_date       timestamp with time zone NOT NULL
    online_app        integer
    is_online_mobile  boolean NOT NULL
    last_seen         integer NOT NULL (unix epoch)

В ней 2 млн записей.

Есть запрос для получения последних записей для каждого user_id (результат - 295 строк):
Код: sql
1.
2.
select x.* from ( select *, row_number() over (partition by user_id order by last_seen desc) as rn from sch.activity_log) x
where rn = 1


Запрос выполняется 3-5 секунд, в зависимости от загрузки сервера.
Есть ли возможность сделать этот запрос более оптимальным?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.05.2021, 11:58
    #40068038
Maxim Boguk
Maxim Boguk
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Есть ли возможность оптимизации запроса?
zuev56
Есть таблица:
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
 sch.activity_log 
    activity_log_id   integer NOT NULL
    user_id           integer NOT NULL
    is_online         boolean
    insert_date       timestamp with time zone NOT NULL
    online_app        integer
    is_online_mobile  boolean NOT NULL
    last_seen         integer NOT NULL (unix epoch)

В ней 2 млн записей.

Есть запрос для получения последних записей для каждого user_id (результат - 295 строк):
Код: sql
1.
2.
select x.* from ( select *, row_number() over (partition by user_id order by last_seen desc) as rn from sch.activity_log) x
where rn = 1


Запрос выполняется 3-5 секунд, в зависимости от загрузки сервера.
Есть ли возможность сделать этот запрос более оптимальным?


при наличии индекса по (user_id, last_seen) можно
переписав запрос в виде

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
WITH RECURSIVE t AS ( 
  --start from greatest user_id 
  (
    SELECT * FROM activity_log ORDER BY user_id DESC, last_seen DESC LIMIT 1
  ) 
  UNION ALL 
  SELECT bpt.* FROM t, 
  LATERAL ( 
   --latest entry from the next user_id < current user_id 
    SELECT * FROM activity_log WHERE user_id<t.user_id ORDER BY user_id DESC, last_seen DESC LIMIT 1 
  ) AS bpt 
) 
--return found values 
SELECT * FROM t;



по мотивам моей старой презентации


--
Maxim Boguk
лучшая поддержка PostgreSQL: dataegret.ru
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.05.2021, 15:22
    #40068107
zuev56
zuev56
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Есть ли возможность оптимизации запроса?
Maxim Boguk, индексы есть:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
CREATE INDEX "IX_activity_log_last_seen"
    ON activity_log USING btree
    (last_seen DESC NULLS LAST)
    TABLESPACE pg_default;

CREATE INDEX "IX_activity_log_user_id"
    ON activity_log USING btree
    (user_id DESC NULLS LAST)
    TABLESPACE pg_default;


Но даже с ними предложенный скрипт выполняется более чем 70 секунд, что медленнее моего примерно в 20 раз :(

С таким индексом выполнялось аж 82 секунды
Код: plsql
1.
2.
3.
4.
CREATE INDEX "IX_activity_log_user_id_last_seen"
    ON vk.activity_log USING btree
    (user_id, last_seen DESC NULLS LAST)
    TABLESPACE pg_default;



А на мой текущий запрос индексы, кажется, никак не влияют
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.05.2021, 16:44
    #40068123
Maxim Boguk
Maxim Boguk
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Есть ли возможность оптимизации запроса?
zuev56,

а кто просил делать индекс
ON vk.activity_log USING btree
(user_id, last_seen DESC NULLS LAST)

когда надо индекс

ON vk.activity_log USING btree
(user_id, last_seen)

--
Maxim Boguk
лучшая поддержка PostgreSQL: dataegret.ru
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
06.05.2021, 20:59
    #40068817
zuev56
zuev56
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Есть ли возможность оптимизации запроса?
Maxim Boguk,
Супер! Теперь 60 мс!
Правда в запросах частично различаются данные, процентов 10. При чём проблема, кажется, в моём запросе.

Огромное спасибо!
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Есть ли возможность оптимизации запроса? / 5 сообщений из 5, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]