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)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=#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=#Так же хочу сообщить, что индексы присутствуют в таблице на нужных полях.
id<6534794? Если простой индекс, то индекс работать не будет
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=#