Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / PostgreSQL Новый топик    Ответить
 Есть ли возможность оптимизации запроса?  [new]
zuev56
Member

Откуда:
Сообщений: 3
Есть таблица:
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 строк):
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 секунд, в зависимости от загрузки сервера.
Есть ли возможность сделать этот запрос более оптимальным?

Сообщение было отредактировано: 3 май 21, 10:40
3 май 21, 10:46    [22317721]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли возможность оптимизации запроса?  [new]
Maxim Boguk
Member

Откуда: Melbourne, Австралия
Сообщений: 4719
zuev56
Есть таблица:
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 строк):
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) можно
переписав запрос в виде

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

Сообщение было отредактировано: 3 май 21, 11:53
3 май 21, 11:58    [22317744]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли возможность оптимизации запроса?  [new]
zuev56
Member

Откуда:
Сообщений: 3
Maxim Boguk, индексы есть:
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 секунды
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;


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

Сообщение было отредактировано: 3 май 21, 15:24
3 май 21, 15:22    [22317847]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли возможность оптимизации запроса?  [new]
Maxim Boguk
Member

Откуда: Melbourne, Австралия
Сообщений: 4719
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
3 май 21, 16:44    [22317885]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли возможность оптимизации запроса?  [new]
zuev56
Member

Откуда:
Сообщений: 3
Maxim Boguk,
Супер! Теперь 60 мс!
Правда в запросах частично различаются данные, процентов 10. При чём проблема, кажется, в моём запросе.

Огромное спасибо!
вчера, 20:59    [22319352]     Ответить | Цитировать Сообщить модератору
Все форумы / PostgreSQL Ответить