ReSQL.ru
     
powered by simpleCommunicator - 2.0.59     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Помогите с запросом, не могу понять как оптимизировать :(
11 сообщений из 11, страница 1 из 1
Помогите с запросом, не могу понять как оптимизировать :(
    #39357801
berni
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
berni
Гость
Нужно обновить таблицу entities, задать время которое берется из таблицы transactions, связь между ними через таблицу entities_stats, у каждого entities может быть несколько entities_stats и нужно выбрать минимальное время, я выбираю его через id entities_stats так как понятно что первая запись самая ранняя.

Код: plsql
1.
2.
3.
4.
UPDATE entities SET first_seen = (
	SELECT t.time from entities_stats est LEFT JOIN transactions t ON est.transaction_id = t.id 
	WHERE est.entity_id = entities.id ORDER BY est.id LIMIT 1
)



локально на маленькой базе всего в 10к entities выполняется за 14 секунд, продакшн база содержит 200М entities и 1B entities_stats, 200M transactions. Запрос висит уже сутки и пока результата нет.

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
Update on entities  (cost=0.00..176573.09 rows=10633 width=72)
  ->  Seq Scan on entities  (cost=0.00..176573.09 rows=10633 width=72)
        SubPlan 1
          ->  Limit  (cost=0.57..16.56 rows=1 width=8)
                ->  Nested Loop Left Join  (cost=0.57..1312.24 rows=82 width=8)
                      ->  Index Scan using entities_stats_pkey on entities_stats est  (cost=0.29..871.02 rows=82 width=8)
                            Filter: (entity_id = entities.id)
                      ->  Index Scan using transactions_pkey on transactions t  (cost=0.28..5.37 rows=1 width=8)
                            Index Cond: (est.transaction_id = id)



Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
                                                         Table "public.entities"
     Column     |          Type          |                       Modifiers                        | Storage  | Stats target | Description
----------------+------------------------+--------------------------------------------------------+----------+--------------+-------------
 id             | integer                | not null default nextval('entities_id_seq'::regclass) | plain    |              |
 name        | character varying(255) |                                                        | extended |              |
 first_seen     | integer                |                                                        | plain    |              |
 last_seen      | integer                |                                                        | plain    |              |
Indexes:
    "entities_pkey" PRIMARY KEY, btree (id), tablespace "index_space"
    "entities_name_index" UNIQUE, btree (name), tablespace "index_space"
Referenced by:
    TABLE "entities_stats" CONSTRAINT "entities_stats_entity_id_fkey" FOREIGN KEY (entity_id) REFERENCES entities(id)



Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
                                                 Table "public.entities_stats"
     Column     |  Type   |                         Modifiers                          | Storage | Stats target | Description
----------------+---------+------------------------------------------------------------+---------+--------------+-------------
 id             | integer | not null default nextval('entities_stats_id_seq'::regclass) | plain   |              |
 entity_id     | integer |                                                            | plain   |              |
 transaction_id | integer |                                                            | plain   |              |
Indexes:
    "entities_stats_pkey" PRIMARY KEY, btree (id), tablespace "index_space"
    "entities_stats_entity_id_index" btree (entity_id), tablespace "index_space"
    "entities_stats_transaction_id_index" btree (transaction_id), tablespace "index_space"
Foreign-key constraints:
    "entities_stats_entity_id_fkey" FOREIGN KEY (entity_id) REFERENCES entities(id)
    "entities_stats_transaction_id_fkey" FOREIGN KEY (transaction_id) REFERENCES transactions(id)




Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
                                                            Table "public.transactions"
     Column      |            Type             |                         Modifiers                         | Storage  | Stats target | Description
-----------------+-----------------------------+-----------------------------------------------------------+----------+--------------+-------------
 id              | integer                     | not null default nextval('transactions_id_seq'::regclass) | plain    |              |
 time            | integer                     |                                                           | plain    |              |
Indexes:
    "transactions_pkey" PRIMARY KEY, btree (id), tablespace "index_space"
Referenced by:
    TABLE "entities_stats" CONSTRAINT "entities_stats_transaction_id_fkey" FOREIGN KEY (transaction_id) REFERENCES transactions(id)
...
Рейтинг: 0 / 0
30.11.2016, 16:43
| Ответить | Цитировать | Написать  
Помогите с запросом, не могу понять как оптимизировать :(
    #39357819
Фотография Щукина Анна
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Щукина Анна
Участник
berni,
про dml-оператор MERGE почитайте...
...
Рейтинг: 0 / 0
30.11.2016, 17:00
| Ответить | Цитировать | Написать  
Помогите с запросом, не могу понять как оптимизировать :(
    #39357839
Фотография vyegorov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vyegorov
Участник
Щукина Анна,

А как тут MERGE поможет, в его PG-варианте `INSERT ... ON CONFLICT`?
...
Рейтинг: 0 / 0
30.11.2016, 17:25
| Ответить | Цитировать | Написать  
Помогите с запросом, не могу понять как оптимизировать :(
    #39357852
Фотография vyegorov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vyegorov
Участник
berni,

Такой UPDATE привидёт к существенному приросту размера таблицы. В продукции лучше делать небольшими кусочками, 3-5% от общего числа записей, перемежая вакуумом (ну или дожидаться autovacuum-а).

Также, вам надо избавиться от доступа к `entities_stats` через первичный ключ. Что у вас со статистикой, давно обновлялась?
...
Рейтинг: 0 / 0
30.11.2016, 17:31
| Ответить | Цитировать | Написать  
Помогите с запросом, не могу понять как оптимизировать :(
    #39357858
berni
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
berni
Гость
vyegorov,

я думал о том что бы засунуть время в entities_stats, и даже сделал запрос на локалке что бы проверить скорость вставляя пока просто id вместо времени, через group by entity_id выбирая min(id) в запросе но скорость осталась практически такой-же может прибавила пару секунд.

данные обновляются часто, несколько тысяч в час.
...
Рейтинг: 0 / 0
30.11.2016, 17:39
| Ответить | Цитировать | Написать  
Помогите с запросом, не могу понять как оптимизировать :(
    #39357883
Melkij
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Melkij
Участник
Я правильно понимаю, что большинство из строк в процессе не меняется? Т.е. должны обновиться только небольшое число строк, у кого изменился результат подзапроса?

Попробуйте вот так:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
with first_rows as (
SELECT DISTINCT ON (est.entity_id) est.entity_id, t.time from entities_stats est LEFT JOIN transactions t ON est.transaction_id = t.id ORDER BY est.entity_id, est.id
), for_update as (
select id, time 
from entities
left join first_rows on first_rows.entity_id = entities.id
where (first_rows.entity_id is null and entities.first_seen is not null)
or first_rows.time != entities.first_seen
for update
)
update entities set first_seen = time
from for_update
where for_update.id = entities.id


Идея в том, чтобы сначала посчитать строки, которые надо обновлять и обновить только их.

explain (analyze, buffers) покажите.
...
Рейтинг: 0 / 0
30.11.2016, 17:56
| Ответить | Цитировать | Написать  
Помогите с запросом, не могу понять как оптимизировать :(
    #39357886
berni
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
berni
Гость
Melkij,

Не совем, мне нужно обновить всю таблицу entities. грубо говоря нам нужно что бы в ней теперь было поле first_seen, не предусмотрели это с самого начала. А что бы выбрать дату когда мы увидели эту запись в первый раз найдо найти транзакцию через entities_stats, которых несколько для каждой entity.
...
Рейтинг: 0 / 0
30.11.2016, 18:00
| Ответить | Цитировать | Написать  
Помогите с запросом, не могу понять как оптимизировать :(
    #39357888
Фотография vyegorov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vyegorov
Участник
berni,

На вопрос про статистику не ответили -- как часто обновляется статистика (pg_stat_user_tables)?

Я бы попробовал сделать:
Код: sql
1.
CREATE INDEX CONCURRENTLY i_entities_stats_entity_n_id ON entities_stats(entity_id, id);
...
Рейтинг: 0 / 0
30.11.2016, 18:03
| Ответить | Цитировать | Написать  
Помогите с запросом, не могу понять как оптимизировать :(
    #39357889
Alexius
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alexius
Участник
berni,

возможно тут понадобится индекс по (entity_id, id) в entities_stats, если для одного entity_id в среднем достаточно большое число строк может быть. если есть возможность - попробуйте обновить относительно небольшое число строк (добавьте какое-то условие) и покажите explain analyze запроса.
...
Рейтинг: 0 / 0
30.11.2016, 18:03
| Ответить | Цитировать | Написать  
Помогите с запросом, не могу понять как оптимизировать :(
    #39357908
Melkij
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Melkij
Участник
berniгрубо говоря нам нужно что бы в ней теперь было поле first_seen, не предусмотрели это с самого начала
Что, одноразовая задача?
Одной транзакцией такое делать конечно не надо.

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
with first_rows as (
SELECT DISTINCT ON (est.entity_id) est.entity_id, t.time from entities_stats est JOIN transactions t ON est.transaction_id = t.id 
 WHERE entity_id between 1 and 10000
 ORDER BY est.entity_id, est.id
)
update entities set first_seen = time
from first_rows
where first_rows.id = entities.id


И идите в цикле такими не очень большими транзакциями вплоть до curval(какой-то там сиквенс). Именно отдельными транзакциями, чтобы не держать блокировки. Впрочем, если у вас не OLTP, раз запросу позволительно висеть уже сутки, то можно кусками побольше обновлять.
...
Рейтинг: 0 / 0
30.11.2016, 18:19
| Ответить | Цитировать | Написать  
Помогите с запросом, не могу понять как оптимизировать :(
    #39357927
berni
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
berni
Гость
Спасибо индекс помог, локально вместо 14 секунд моментально отработало, сейчас строим на проде и посмотрю как пойдет.
...
Рейтинг: 0 / 0
30.11.2016, 18:31
| Ответить | Цитировать | Написать  
11 сообщений из 11, страница 1 из 1
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Помогите с запросом, не могу понять как оптимизировать :(
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


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