Postgres query dramatically slowing production

**Second issue - productivity*4.
There are two tables test.purchase and test.stock. After filling test.purchase with INSERT query and 4 triggers which are calculate values in this table.

INSERT INTO test.purchase(
import_id, product_id, euro_price, qty, expiry_date)
VALUES (2,125,16,27,'2021-03-03');

Everything calculated and inserted all values well, but each new query executed much slowly than previous, for example, this query for the row #87 was executed in more than two minutes:

Query returned successfully in 2 min 22 secs.

Delay progressed so fast that looks like if it will be query for the row #1000, it will be executed tomorrow (((. Please advise.

CREATE TABLE test.purchase
(
import_id integer,
product_id integer,
usd_price numeric(10,2),
euro_price numeric(10,2),
qty integer,
euro_stock_price numeric(10,2),
expiry_date date,
euro_stock_amt numeric(10,2),
CONSTRAINT purchase_product_id_import_id_key UNIQUE (product_id, import_id),
CONSTRAINT purchase_import_id_fkey FOREIGN KEY (import_id)
    REFERENCES test.imports (import_id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION,
CONSTRAINT purchase_product_id_fkey FOREIGN KEY (product_id)
    REFERENCES test.products (product_id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION
);
CREATE TRIGGER "0_tr_purchase_convert_price_to_euro"
AFTER INSERT OR UPDATE OF usd_price
ON test.purchase
FOR EACH ROW
EXECUTE PROCEDURE test.fn_purchase_convert_price_to_euro();

CREATE FUNCTION test.fn_purchase_convert_price_to_euro()
RETURNS trigger
LANGUAGE 'plpgsql'
COST 100
VOLATILE NOT LEAKPROOF
AS $BODY$
BEGIN
UPDATE test.purchase pr
SET euro_price = usd_price / i.rate
FROM test.imports i
WHERE pr.import_id = i.import_id
AND pr.usd_price IS NOT NULL;
RETURN NEW;
END
$BODY$;
CREATE TRIGGER "1_tr_purchase_euro_stock_price"
AFTER INSERT OR UPDATE OF euro_price
ON test.purchase
FOR EACH ROW
EXECUTE PROCEDURE test.fn_purchase_euro_stock_price();

CREATE FUNCTION test.fn_purchase_euro_stock_price()
RETURNS trigger
LANGUAGE 'plpgsql'
COST 100
VOLATILE NOT LEAKPROOF
AS $BODY$
BEGIN
UPDATE test.purchase pr
SET euro_stock_price = euro_price + euro_price * i.costs_per_euro
FROM test.imports i
WHERE pr.import_id = i.import_id;
RETURN NEW;
END
$BODY$;
CREATE TRIGGER "2_tr_purchase_euro_stock_amt"
AFTER INSERT OR UPDATE OF qty, euro_stock_price
ON test.purchase
FOR EACH ROW
EXECUTE PROCEDURE test.fn_purchase_euro_stock_amt();

CREATE FUNCTION test.fn_purchase_stock_amount_in()
RETURNS trigger
LANGUAGE 'plpgsql'
COST 100
VOLATILE NOT LEAKPROOF
AS $BODY$
BEGIN
UPDATE test.stock s
SET
amount_in = euro_stock_amt
FROM test.purchase pr
WHERE
pr.import_id = s.import_id
AND 
pr.product_id = s.product_id
;
RETURN NEW;
END
$BODY$;
CREATE TRIGGER "3_tr_purchase_stock_plus_new"
AFTER INSERT
ON test.purchase
FOR EACH ROW
EXECUTE PROCEDURE test.fn_purchase_stock_plus_new();

CREATE FUNCTION test.fn_purchase_stock_plus_new()
RETURNS trigger
LANGUAGE 'plpgsql'
COST 100
VOLATILE NOT LEAKPROOF
AS $BODY$
BEGIN
INSERT INTO test.stock(
product_id,
import_id,
expiry_date,
stock_in
)
VALUES(
NEW.product_id,
NEW.import_id,
NEW.expiry_date,
NEW.qty 
);
RETURN NEW;
END
$BODY$;
CREATE TRIGGER "4_tr_purchase_stock_amount_in"
AFTER INSERT
ON test.purchase
FOR EACH ROW
EXECUTE PROCEDURE test.fn_purchase_stock_amount_in();

CREATE FUNCTION test.fn_purchase_stock_amount_in()
RETURNS trigger
LANGUAGE 'plpgsql'
COST 100
VOLATILE NOT LEAKPROOF
AS $BODY$
BEGIN
UPDATE test.stock s
SET
amount_in = euro_stock_amt
FROM test.purchase pr
WHERE
pr.import_id = s.import_id
AND 
pr.product_id = s.product_id
;
RETURN NEW;
END
$BODY$;
CREATE TABLE test.stock
(
product_id integer,
import_id integer,
expiry_date date,
stock_in integer,
stock_out integer,
stock_balance integer,
amount_in numeric(10,2),
amount_sold numeric(10,2),
amount_balance numeric(10,2),
CONSTRAINT stock_product_id_import_id_key UNIQUE (product_id, import_id),
CONSTRAINT stock_import_id_fkey FOREIGN KEY (import_id)
    REFERENCES test.imports (import_id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION,
CONSTRAINT stock_product_id_fkey FOREIGN KEY (product_id)
    REFERENCES test.products (product_id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION,
CONSTRAINT stock_nonnegative CHECK (stock_balance >= 0)
);
CREATE TRIGGER tr_stock_balance_update
AFTER INSERT OR UPDATE OF stock_out
ON test.stock
FOR EACH ROW
EXECUTE PROCEDURE test.fn_stock_balance_update();

CREATE FUNCTION test.fn_stock_balance_update()
RETURNS trigger
LANGUAGE 'plpgsql'
COST 100
VOLATILE NOT LEAKPROOF
AS $BODY$
BEGIN
UPDATE
  test.stock s
SET
  stock_balance = stock_in - stock_out,
  amount_balance = amount_sold - amount_in
WHERE
  NEW.product_id = s.product_id
  AND
  NEW.expiry_date = s.expiry_date;
RETURN NEW;
END
$BODY$;
EXPLAIN (ANALYSE,BUFFERS,SETTINGS)
UPDATE test.stock s
SET
amount_in = euro_stock_amt
FROM test.purchase pr
WHERE
pr.import_id = s.import_id
AND 
pr.product_id = s.product_id
;
 Update on stock s  (cost=18.18..1951.72 rows=45 width=54) (actual time=1.781..1.782 rows=0 loops=1).  
 Buffers: shared hit=540
 ->  Nested Loop  (cost=18.18..1951.72 rows=45 width=54) (actual time=0.051..1.108 rows=88 loops=1)
     Buffers: shared hit=354
     ->  Seq Scan on stock s  (cost=0.00..2.88 rows=88 width=42) (actual time=0.015..0.118 rows=88 loops=1)
           Buffers: shared hit=2
     ->  Bitmap Heap Scan on purchase pr  (cost=18.18..22.19 rows=1 width=20) (actual time=0.007..0.007 rows=1 loops=88)
           Recheck Cond: ((product_id = s.product_id) AND (import_id = s.import_id))
           Heap Blocks: exact=88
           Buffers: shared hit=352
           ->  Bitmap Index Scan on purchase_product_id_import_id_key  (cost=0.00..18.18 rows=1 width=0) (actual time=0004..0.004 rows=1 loops=88)
                 Index Cond: ((product_id = s.product_id) AND (import_id = s.import_id))
                 Buffers: shared hit=264
 Planning Time: 0.751 ms
 Execution Time: 1.914 ms
 (15 rows)
EXPLAIN (ANALYSE,BUFFERS,SETTINGS)
INSERT INTO test.stock(
product_id,
import_id,
expiry_date,
stock_in
)
VALUES
(
NEW.product_id,
NEW.import_id,
NEW.expiry_date,
NEW.qty 
);
ERROR:  missing FROM-clause entry for table "new"
LINE 10: NEW.product_id,

It's very strange result because it's direct insert from one table another and it works well

EXPLAIN (ANALYSE,BUFFERS,SETTINGS)
UPDATE test.purchase pr
SET euro_stock_price = euro_price + euro_price * i.costs_per_euro
FROM test.imports i
WHERE pr.import_id = i.import_id;
RETURN NEW;
END;
Update on purchase pr  (cost=2.07..139.16 rows=88 width=60) (actual time=1.128..1.129 rows=0 loops=1)
Buffers: shared hit=627
->  Hash Join  (cost=2.07..139.16 rows=88 width=60) (actual time=0.179..0.469 rows=88 loops=1)
     Hash Cond: (pr.import_id = i.import_id)
     Buffers: shared hit=140
     ->  Seq Scan on purchase pr  (cost=0.00..135.88 rows=88 width=38) (actual time=0.117..0.199 rows=88 loops=1)
           Buffers: shared hit=135
     ->  Hash  (cost=2.03..2.03 rows=3 width=14) (actual time=0.029..0.030 rows=3 loops=1)
           Buckets: 1024  Batches: 1  Memory Usage: 9kB
           Buffers: shared hit=2
           ->  Seq Scan on imports i  (cost=0.00..2.03 rows=3 width=14) (actual time=0.004..0.013 rows=3 loops=1)
                 Buffers: shared hit=2
 Time: 0.303 ms
 Trigger 2_tr_purchase_euro_stock_amt: time=319.528 calls=88
 Execution Time: 320.858 ms
 (15 rows)
EXPLAIN (ANALYSE,BUFFERS,SETTINGS)
UPDATE test.purchase pr
SET euro_stock_amt = euro_stock_price * qty
;
Update on purchase pr  (cost=0.00..69.54 rows=88 width=54) (actual time=0.684..0.684 rows=0 loops=1)
Buffers: shared hit=535 dirtied=61
->  Seq Scan on purchase pr  (cost=0.00..69.54 rows=88 width=54) (actual time=0.059..0.164 rows=88 loops=1)
     Buffers: shared hit=68
Planning Time: 0.140 ms
Execution Time: 0.734 ms
(6 rows)
EXPLAIN (ANALYSE,BUFFERS,SETTINGS)
UPDATE test.purchase pr
SET euro_price = usd_price / i.rate
FROM test.imports i
WHERE pr.import_id = i.import_id
AND pr.usd_price IS NOT NULL;
 Update on purchase pr  (cost=2.07..71.28 rows=29 width=61) (actual time=0.448..0.449 rows=0 loops=1)
 Buffers: shared hit=130 dirtied=3
 ->  Hash Join  (cost=2.07..71.28 rows=29 width=61) (actual time=0.047..0.258 rows=29 loops=1)
     Hash Cond: (pr.import_id = i.import_id)
     Buffers: shared hit=70
     ->  Seq Scan on purchase pr  (cost=0.00..68.88 rows=29 width=39) (actual time=0.012..0.138 rows=29 loops=1)
           Filter: (usd_price IS NOT NULL)
           Rows Removed by Filter: 59
           Buffers: shared hit=68
     ->  Hash  (cost=2.03..2.03 rows=3 width=17) (actual time=0.017..0.018 rows=3 loops=1)
           Buckets: 1024  Batches: 1  Memory Usage: 9kB
           Buffers: shared hit=2
           ->  Seq Scan on imports i  (cost=0.00..2.03 rows=3 width=17) (actual time=0.004..0.009 rows=3 loops=1)
                 Buffers: shared hit=2
Planning Time: 0.447 ms
Trigger 1_tr_purchase_euro_stock_price: time=148624.503 calls=29
Execution Time: 148625.095 ms
(17 rows)

Looks like fn_purchase_convert_price_to_euro is the source of slowing, so I need to re-write it.

Thanks to everybody for advises )

评论