explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5yZi

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 2,578.593 ↑ 1.0 1 1

Limit (cost=3,103.67..3,103.67 rows=1 width=16) (actual time=2,578.593..2,578.593 rows=1 loops=1)

  • Output: upd.id
  • t_upd.crpt_gtin, t_upd.vat, t_upd.old_cis, t_upd.new_cis, t_upd.serial_number
2.          

CTE dublicate_users

3. 0.425 0.425 ↑ 1.0 340 1

Values Scan on "*VALUES*" (cost=0.00..4.25 rows=340 width=32) (actual time=0.003..0.425 rows=340 loops=1)

  • Output: "*VALUES*".column1, "*VALUES*".column2
4.          

CTE upd

5. 18.223 2,578.453 ↓ 2.7 100 1

Update on ms.motion t (cost=2,819.06..3,098.50 rows=37 width=2,469) (actual time=48.758..2,578.453 rows=100 loops=1)

  • Output: t.id
6.          

CTE id_to_update

7. 0.035 2,559.264 ↑ 1.0 100 1

Limit (cost=0.56..2,818.50 rows=100 width=32) (actual time=48.608..2,559.264 rows=100 loops=1)

  • Output: t_upd.id, dublicate_users.original_id
8. 1,233.240 2,559.229 ↑ 15,955.2 100 1

Nested Loop (cost=0.56..44,960,897.32 rows=1,595,525 width=32) (actual time=48.608..2,559.229 rows=100 loops=1)

  • Output: t_upd.id, dublicate_users.original_id
  • Join Filter: (t_upd.owner_id = dublicate_users.dublicate_id)
  • Rows Removed by Join Filter: 13618308
9. 164.394 164.394 ↑ 131.4 40,055 1

Index Scan using motion_pkey on ms.motion t_upd (cost=0.56..4,701,001.77 rows=5,262,731 width=32) (actual time=0.074..164.394 rows=40,055 loops=1)

  • Output: t_upd.id, t_upd.sum, t_upd.sumincurrency, t_upd.quantity, t_upd.good_id, t_upd.consignment_id, t_upd.reason_id, t_upd.operation_id, t_upd."position", t_upd.reserve, t_upd.correctionamount, t_upd.goodpack_id, t_upd.gtd_id, t_upd.basesum, t_upd.basesumincurrency, t_upd
  • Index Cond: (t_upd.id > '00000000-0000-0000-0000-000000000000'::uuid)
  • Filter: (t_upd.account_id = 'ed1d05b1-e159-11e6-7a69-971100001e4d'::uuid)
  • Rows Removed by Filter: 67726
10. 1,161.595 1,161.595 ↑ 1.0 340 40,055

CTE Scan on dublicate_users (cost=0.00..6.80 rows=340 width=32) (actual time=0.000..0.029 rows=340 loops=40,055)

  • Output: dublicate_users.original_id, dublicate_users.dublicate_id
11. 0.294 2,560.230 ↓ 2.7 100 1

Nested Loop (cost=0.56..280.00 rows=37 width=2,469) (actual time=48.630..2,560.230 rows=100 loops=1)

  • Output: t.id, t.sum, t.sumincurrency, t.quantity, t.good_id, t.consignment_id, t.reason_id, t.operation_id, t."position", t.reserve, t.correctionamount, t.goodpack_id, t.gtd_id, t.basesum, t.basesumincurrency, t.discount, t.slot_id, t.sourceslot_id, t.moment, t.ntype, t.account_id, t.coun
  • Inner Unique: true
12. 2,559.436 2,559.436 ↑ 1.0 100 1

CTE Scan on id_to_update (cost=0.00..2.00 rows=100 width=88) (actual time=48.616..2,559.436 rows=100 loops=1)

  • Output: id_to_update.original_id, id_to_update.*, id_to_update.id
13. 0.500 0.500 ↑ 1.0 1 100

Index Scan using motion_pkey on ms.motion t (cost=0.56..2.78 rows=1 width=2,397) (actual time=0.005..0.005 rows=1 loops=100)

  • Output: t.id, t.sum, t.sumincurrency, t.quantity, t.good_id, t.consignment_id, t.reason_id, t.operation_id, t."position", t.reserve, t.correctionamount, t.goodpack_id, t.gtd_id, t.basesum, t.basesumincurrency, t.discount, t.slot_id, t.sourceslot_id, t.moment, t.ntype, t.account_id,
  • Index Cond: (t.id = id_to_update.id)
  • Filter: (t.account_id = 'ed1d05b1-e159-11e6-7a69-971100001e4d'::uuid)
14. 0.080 2,578.591 ↑ 37.0 1 1

Sort (cost=0.93..1.02 rows=37 width=16) (actual time=2,578.591..2,578.591 rows=1 loops=1)

  • Output: upd.id
  • Sort Key: upd.id DESC
  • Sort Method: top-N heapsort Memory: 25kB
15. 2,578.511 2,578.511 ↓ 2.7 100 1

CTE Scan on upd (cost=0.00..0.74 rows=37 width=16) (actual time=48.760..2,578.511 rows=100 loops=1)

  • Output: upd.id
Planning time : 2.341 ms
Execution time : 2,579.698 ms
Trigger times:
Trigger Name:Total time:Calls:Average time:
RI_ConstraintTrigger_c_64115 for constraint fk_motion_owner 0.871 ms 100 0.009 ms