Как взять предпоследние значение перед вставкой новой записи в большой таблице СУБД postgres?

Question

[wolf-9830]

У нас есть таблица с долгами клиентов, она большая, около 5 миллионов записей, суть проблемы в том, что перед вставкой нового значения долга для определенного клиента мне нужно получить запись с предыдущим долгом клиента (обычно долги начисляются по крону пачками), решая задачу я выбрал два пути решения:
1) пишется триггер на эту большую таблицу и перед вставкой вычисляется id прошлой записи для определенного id клиента и записывается в промежуточную таблицу. Но такой вариант использовать как мне кажется не очень гуманно, потому что скорость выполнения запроса выборки нужного мне ид прошлой записи ~1.2 секунды, за ночь в таблицу вставляется около 50 000 записей, вполне возможно я не правильно составляю запрос на выборку, если что не так, направьте меня пожалуйста в нужное направление, вот мой запрос:

select id from debt where id<6534794 AND cus_id = 143867 ORDER BY id desc LIMIT 1

И его результаты я вставляю insert-ом в промежуточную таблицу для дальнейших операций

2) В промежуточную таблицу я пишу только id новой записи, которая была вставлена в большую таблицу, после пишу функцию на языке plpgsql, которая анализирует промежуточную таблицу, берет id вставленной записи и на основе этого id делает выборку уже из большой таблицы предпоследнего значения, но это очень долго, мне критически важно, чтоб запрос отрабатывал меньше секунды для одной записи

У вас наверно возникнет вопрос а зачем собственно так извращаться? А затем, что эти данные будет обрабатывать скрипт на php и в том числе он будет вычислять дельту между долгами клиентов, по этому я беру данные из промежуточной таблицы с долгами, потом к ним прибавляю еще огромное количество других данных и пишу в отдельную таблицу, вопрос, почему я не сделаю одним sql запросом, потому что это очень будет тормозить, проще вывести в отдельную таблицу и вставлять в нее данные, а потом добавить нужные индексы и разбить по партициям, в данном проекте очень важна скорость работы, по этому если вам кажется, что я костылю, то прошу вас предложить мне варианты для правильного решения данной проблемы. Спасибо.

Answer 1

[Falseclock]

а чем RETURNING не нравится?

Туда же можно привязать что угодно

Я например удаляя какие-то данные вытаскиваю что вообще удалилось и джоинами прикручиваю все что мне нужно вытащить. например

DELETE FROM 
				waybills 
			USING 
				invoices 
			JOIN invoice_data ON
				invoice_data.invoice_uuid = invoices.invoice_uuid
			WHERE 
				waybills.invoice_uuid = waybills.invoice_uuid AND 
				waybills.waybill_uuid = ?
			RETURNING 
				invoice_data.order_data_id,
				invoices.order_id,
				invoices.invoice_uuid

Вы же в своем инсерте также можете добавить RETURNING и указать что вы хотите вернуть )))

Comments

[Falseclock]

и покажите структуру таблицы. Скорее всего вам надо создать индекс. в котором два поля, по которым вы ищите данные. Два разных индекса по двум разным полям не будут работать должным образом, нужные сложные индексы.

[wolf-9830]

Falseclock: прочитав про RETURNING понял, что вещь очень хорошая и в дальнейшем она мне очень пригодится, но это мне не подходит, т.к. нужна прошлая запись о долге этого клиента, а RETURNING возвращает текущею запись, если я правильно понял. Так же хочу сообщить, что индексы присутствуют в таблице на нужных полях.

[Falseclock]

wolf-9830: плохо вы прочитали про RETURNING. Можно вставляя запись указать что в поле FROM что в USING текущую таблицу и возвращать разницу между старой и новой записью.

[Falseclock]

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

[Falseclock]

virtex_old2=# select * from debt;
 id | amount | cus_id
----+--------+--------
  1 |   1111 |     25
  2 |    389 |     37
  3 |   1993 |     65
  4 |    591 |     19
  5 |    735 |     16
(5 rows)

virtex_old2=# INSERT INTO debt (amount,cus_id) VALUES (2222,25) RETURNING (SELECT d.amount FROM debt d WHERE d.cus_id = 25 ORDER BY d.id DESC LIMIT 1);
 amount
--------
   1111
(1 row)

INSERT 0 1
virtex_old2=# INSERT INTO debt (amount,cus_id) VALUES (3333,25) RETURNING (SELECT d.amount FROM debt d WHERE d.cus_id = 25 ORDER BY d.id DESC LIMIT 1);
 amount
--------
   2222
(1 row)

INSERT 0 1
virtex_old2=#

[Falseclock]

wolf-9830:

Так же хочу сообщить, что индексы присутствуют в таблице на нужных полях.

а индекс у вас на эксперссию на вот этот случай id<6534794? Если простой индекс, то индекс работать не будет

[Falseclock]

5 миллионов записей - это копейки. И если у вас запрос длится очень долго, значит индексы не работают. Как советует Pavel Denisov делайте EXPLAIN ANALYZE

virtex_old2=# EXPLAIN ANALYZE INSERT INTO debt (amount,cus_id) VALUES (3333,25) RETURNING (SELECT d.amount FROM debt d WHERE d.cus_id = 25 ORDER BY d.id DESC LIMIT 1);
                                                     QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
 Insert on debt  (cost=1.10..1.12 rows=1 width=0) (actual time=0.095..0.095 rows=1 loops=1)
   InitPlan 1 (returns $0)
     ->  Limit  (cost=1.10..1.10 rows=1 width=9) (actual time=0.028..0.029 rows=1 loops=1)
           ->  Sort  (cost=1.10..1.11 rows=3 width=9) (actual time=0.026..0.026 rows=1 loops=1)
                 Sort Key: d.id
                 Sort Method: top-N heapsort  Memory: 25kB
                 ->  Seq Scan on debt d  (cost=0.00..1.09 rows=3 width=9) (actual time=0.009..0.010 rows=3 loops=1)
                       Filter: (cus_id = 25)
                       Rows Removed by Filter: 4
   ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.032..0.032 rows=1 loops=1)
 Planning time: 0.224 ms
 Execution time: 0.138 ms
(12 rows)

virtex_old2=#

[Falseclock]

Взял засунул 5 миллионов записей в таблицу через рандом.
Запрос на инсерт работает 1 секунду.
Создал индекс. Снизилось до 145мс.

Показали бы вашу таблицу и кишки ее, мы бы посоветовали... Чего боитесь то?

virtex_old2=# INSERT INTO debt(amount,cus_id)  SELECT amount, cus_id FROM (SELECT generate_series(1,5000000) as id, (random()*10000)::int AS amount, (random()*10000)::int AS cus_id) as aa;
INSERT 0 5000000
virtex_old2=# select count(*) from debt;
  count
---------
 5100018
(1 row)

virtex_old2=# EXPLAIN ANALYZE INSERT INTO debt (amount,cus_id) VALUES (3333,25) RETURNING (SELECT d.amount FROM debt d WHERE d.cus_id = 25 ORDER BY d.id DESC LIMIT 1);
                                                          QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
 Insert on debt  (cost=91320.75..91320.76 rows=1 width=0) (actual time=991.627..991.630 rows=1 loops=1)
   InitPlan 1 (returns $0)
     ->  Limit  (cost=91320.74..91320.75 rows=1 width=8) (actual time=991.541..991.542 rows=1 loops=1)
           ->  Sort  (cost=91320.74..91321.99 rows=499 width=8) (actual time=991.538..991.538 rows=1 loops=1)
                 Sort Key: d.id
                 Sort Method: top-N heapsort  Memory: 25kB
                 ->  Seq Scan on debt d  (cost=0.00..91318.25 rows=499 width=8) (actual time=6.914..990.835 rows=517 loops=1)
                       Filter: (cus_id = 25)
                       Rows Removed by Filter: 5099503
   ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.022..0.023 rows=1 loops=1)
 Planning time: 0.188 ms
 Execution time: 991.702 ms
(12 rows)

virtex_old2=# CREATE INDEX cus_id_idx ON debt (cus_id);
CREATE INDEX
virtex_old2=# EXPLAIN ANALYZE INSERT INTO debt (amount,cus_id) VALUES (3333,25) RETURNING (SELECT d.amount FROM debt d WHERE d.cus_id = 25 ORDER BY d.id DESC LIMIT 1);
                                                                 QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
 Insert on debt  (cost=1802.05..1802.06 rows=1 width=0) (actual time=145.303..145.306 rows=1 loops=1)
   InitPlan 1 (returns $0)
     ->  Limit  (cost=1802.04..1802.05 rows=1 width=8) (actual time=144.820..144.821 rows=1 loops=1)
           ->  Sort  (cost=1802.04..1803.29 rows=499 width=8) (actual time=144.812..144.812 rows=1 loops=1)
                 Sort Key: d.id
                 Sort Method: top-N heapsort  Memory: 25kB
                 ->  Bitmap Heap Scan on debt d  (cost=12.30..1799.55 rows=499 width=8) (actual time=7.500..143.932 rows=518 loops=1)
                       Recheck Cond: (cus_id = 25)
                       Heap Blocks: exact=510
                       ->  Bitmap Index Scan on cus_id_idx  (cost=0.00..12.17 rows=499 width=0) (actual time=0.506..0.506 rows=519 loops=1)
                             Index Cond: (cus_id = 25)
   ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.025..0.025 rows=1 loops=1)
 Planning time: 5.582 ms
 Execution time: 145.724 ms
(14 rows)

virtex_old2=#

[Falseclock]

Кстати, последующие запросы будут работать со скоростью до 10мс.

virtex_old2=# EXPLAIN ANALYZE INSERT INTO debt (amount,cus_id) VALUES (3333,255) RETURNING (SELECT d.amount FROM debt d WHERE d.cus_id = 255 ORDER BY d.id DESC LIMIT 1);
                                                                 QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
 Insert on debt  (cost=1802.05..1802.06 rows=1 width=0) (actual time=2.288..2.290 rows=1 loops=1)
   InitPlan 1 (returns $0)
     ->  Limit  (cost=1802.04..1802.05 rows=1 width=8) (actual time=2.205..2.205 rows=1 loops=1)
           ->  Sort  (cost=1802.04..1803.29 rows=499 width=8) (actual time=2.202..2.202 rows=1 loops=1)
                 Sort Key: d.id
                 Sort Method: top-N heapsort  Memory: 25kB
                 ->  Bitmap Heap Scan on debt d  (cost=12.30..1799.55 rows=499 width=8) (actual time=0.341..1.866 rows=550 loops=1)
                       Recheck Cond: (cus_id = 255)
                       Heap Blocks: exact=543
                       ->  Bitmap Index Scan on cus_id_idx  (cost=0.00..12.17 rows=499 width=0) (actual time=0.183..0.183 rows=551 loops=1)
                             Index Cond: (cus_id = 255)
   ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.020..0.020 rows=1 loops=1)
 Planning time: 0.257 ms
 Execution time: 2.375 ms
(14 rows)

virtex_old2=# EXPLAIN ANALYZE INSERT INTO debt (amount,cus_id) VALUES (3333,25) RETURNING (SELECT d.amount FROM debt d WHERE d.cus_id = 25 ORDER BY d.id DESC LIMIT 1);
                                                                 QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
 Insert on debt  (cost=1802.05..1802.06 rows=1 width=0) (actual time=1.475..1.476 rows=1 loops=1)
   InitPlan 1 (returns $0)
     ->  Limit  (cost=1802.04..1802.05 rows=1 width=8) (actual time=1.413..1.413 rows=1 loops=1)
           ->  Sort  (cost=1802.04..1803.29 rows=499 width=8) (actual time=1.411..1.411 rows=1 loops=1)
                 Sort Key: d.id
                 Sort Method: top-N heapsort  Memory: 25kB
                 ->  Bitmap Heap Scan on debt d  (cost=12.30..1799.55 rows=499 width=8) (actual time=0.202..1.205 rows=527 loops=1)
                       Recheck Cond: (cus_id = 25)
                       Heap Blocks: exact=510
                       ->  Bitmap Index Scan on cus_id_idx  (cost=0.00..12.17 rows=499 width=0) (actual time=0.111..0.111 rows=528 loops=1)
                             Index Cond: (cus_id = 25)
   ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.014..0.014 rows=1 loops=1)
 Planning time: 0.179 ms
 Execution time: 1.535 ms
(14 rows)

[wolf-9830]

Falseclock: Действительно проблема крылась в индексах, сейчас все настроили с коллегами и этот запрос: select id from debt where id<6534794 AND cus_id = 143867 ORDER BY id desc LIMIT 1 выдает 2 миллисекунды, спасибо вам Falseclock и вам Pavel Denisov

[Falseclock]

wolf-9830: я все же предлагаю сделать так как предлагаю я. Слишком большой индекс будет тормозить инсерты в будущем. У вас тот случай, когда надо сделать быстро и то и другое. При моем варианте можно обойтись одним индексом только по ID заказчика.

Смысл в returning в том, что любая операция в потсгресе проводится внутри собственного begin-commit. Соответственно вытаскивая данные даже при инсерте, вы вытащите последнюю запись до внесения новой. Этот прием позволяет избавиться таких триггеров, которые работают before insert or update. Вы заранее можете выяснить стоит ли вам вообще вносить данные или обновлять их.

[wolf-9830]

Falseclock: к сожалению в код, который добавляет эти записи по крону вмешиваться нельзя, начальство так сказало, вот и ищу обходные пути.