explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4D7w

Settings
# exclusive inclusive rows x rows loops node
1. 211.675 7,913.497 ↓ 10,877.0 21,754 1

Sort (cost=1,350.51..1,350.51 rows=2 width=105) (actual time=7,911.010..7,913.497 rows=21,754 loops=1)

  • Sort Key: p.full_name
  • Sort Method: external merge Disk: 2184kB
2.          

CTE supplier_state

3. 0.519 6.084 ↑ 1.7 268 1

HashAggregate (cost=562.74..567.20 rows=446 width=56) (actual time=6.021..6.084 rows=268 loops=1)

  • Group Key: sp_1.id, last_value(rs.registry_stage) OVER (?)
4. 0.688 5.565 ↓ 1.9 864 1

WindowAgg (cost=551.59..560.51 rows=446 width=56) (actual time=4.833..5.565 rows=864 loops=1)

5. 0.487 4.877 ↓ 1.9 864 1

Sort (cost=551.59..552.70 rows=446 width=34) (actual time=4.810..4.877 rows=864 loops=1)

  • Sort Key: e.event_driven_id, e.created_when
  • Sort Method: quicksort Memory: 92kB
6. 0.739 4.390 ↓ 1.9 864 1

Hash Join (cost=464.34..531.96 rows=446 width=34) (actual time=3.249..4.390 rows=864 loops=1)

  • Hash Cond: (rs.event_id = e.id)
7. 0.439 0.439 ↓ 1.0 3,140 1

Seq Scan on register_stages rs (cost=0.00..51.39 rows=3,139 width=18) (actual time=0.014..0.439 rows=3,140 loops=1)

8. 0.499 3.212 ↓ 1.4 1,315 1

Hash (cost=452.60..452.60 rows=939 width=32) (actual time=3.212..3.212 rows=1,315 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 99kB
9. 1.590 2.713 ↓ 1.4 1,315 1

Hash Join (cost=288.13..452.60 rows=939 width=32) (actual time=0.346..2.713 rows=1,315 loops=1)

  • Hash Cond: (e.event_driven_id = sp_1.id)
10. 0.822 0.822 ↓ 1.0 6,630 1

Seq Scan on events e (cost=0.00..147.09 rows=6,609 width=24) (actual time=0.026..0.822 rows=6,630 loops=1)

11. 0.094 0.301 ↓ 1.1 311 1

Hash (cost=284.43..284.43 rows=296 width=8) (actual time=0.301..0.301 rows=311 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 21kB
12. 0.207 0.207 ↓ 1.1 311 1

Index Only Scan using supplier_pkey on supplier sp_1 (cost=0.27..284.43 rows=296 width=8) (actual time=0.022..0.207 rows=311 loops=1)

  • Heap Fetches: 210
13.          

CTE supplier_postfactum_state

14. 0.348 3.481 ↑ 2.4 311 1

HashAggregate (cost=240.87..248.27 rows=740 width=48) (actual time=3.381..3.481 rows=311 loops=1)

  • Group Key: e_1.event_driven_id, last_value(sse.supplier_state) OVER (?)
15. 0.393 3.133 ↑ 1.6 451 1

WindowAgg (cost=222.37..237.17 rows=740 width=48) (actual time=2.696..3.133 rows=451 loops=1)

16. 0.311 2.740 ↑ 1.6 451 1

Sort (cost=222.37..224.22 rows=740 width=26) (actual time=2.676..2.740 rows=451 loops=1)

  • Sort Key: e_1.event_driven_id, e_1.created_when
  • Sort Method: quicksort Memory: 60kB
17. 1.291 2.429 ↑ 1.6 451 1

Hash Join (cost=22.65..187.10 rows=740 width=26) (actual time=0.865..2.429 rows=451 loops=1)

  • Hash Cond: (e_1.id = sse.event_id)
18. 0.850 0.850 ↓ 1.0 6,630 1

Seq Scan on events e_1 (cost=0.00..147.09 rows=6,609 width=24) (actual time=0.013..0.850 rows=6,630 loops=1)

19. 0.163 0.288 ↑ 1.6 451 1

Hash (cost=13.40..13.40 rows=740 width=18) (actual time=0.288..0.288 rows=451 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 33kB
20. 0.125 0.125 ↑ 1.6 451 1

Seq Scan on supplier_state_event sse (cost=0.00..13.40 rows=740 width=18) (actual time=0.017..0.125 rows=451 loops=1)

21.          

CTE public_price

22. 0.043 1.286 ↓ 22.0 22 1

Unique (cost=57.44..57.45 rows=1 width=24) (actual time=1.220..1.286 rows=22 loops=1)

23. 0.053 1.243 ↓ 79.0 79 1

Sort (cost=57.44..57.44 rows=1 width=24) (actual time=1.219..1.243 rows=79 loops=1)

  • Sort Key: spl_1.fk_supplier_id, (last_value(spl_1.id) OVER (?))
  • Sort Method: quicksort Memory: 31kB
24. 0.075 1.190 ↓ 79.0 79 1

WindowAgg (cost=57.40..57.43 rows=1 width=24) (actual time=1.127..1.190 rows=79 loops=1)

25. 0.064 1.115 ↓ 79.0 79 1

Sort (cost=57.40..57.41 rows=1 width=24) (actual time=1.109..1.115 rows=79 loops=1)

  • Sort Key: spl_1.fk_supplier_id, spl_1.created_when
  • Sort Method: quicksort Memory: 31kB
26. 0.269 1.051 ↓ 79.0 79 1

Hash Right Join (cost=33.29..57.39 rows=1 width=24) (actual time=1.025..1.051 rows=79 loops=1)

  • Hash Cond: (cpl.fk_supplier_price_list_id = spl_1.id)
  • Filter: (cpl.id IS NULL)
  • Rows Removed by Filter: 688
27. 0.125 0.125 ↓ 1.1 688 1

Seq Scan on contract_price_list cpl (cost=0.00..22.41 rows=641 width=16) (actual time=0.012..0.125 rows=688 loops=1)

28. 0.242 0.657 ↓ 1.0 757 1

Hash (cost=24.24..24.24 rows=724 width=24) (actual time=0.657..0.657 rows=757 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 50kB
29. 0.415 0.415 ↓ 1.0 757 1

Seq Scan on supplier_price_list spl_1 (cost=0.00..24.24 rows=724 width=24) (actual time=0.038..0.415 rows=757 loops=1)

30. 25.901 7,701.822 ↓ 10,877.0 21,754 1

GroupAggregate (cost=477.55..477.58 rows=2 width=105) (actual time=7,659.674..7,701.822 rows=21,754 loops=1)

  • Group Key: p.id
31. 262.284 7,675.921 ↓ 53,697.5 107,395 1

Sort (cost=477.55..477.55 rows=2 width=79) (actual time=7,659.636..7,675.921 rows=107,395 loops=1)

  • Sort Key: p.id
  • Sort Method: external merge Disk: 8560kB
32. 2,013.436 7,413.637 ↓ 53,697.5 107,395 1

Nested Loop (cost=1.42..477.54 rows=2 width=79) (actual time=12.250..7,413.637 rows=107,395 loops=1)

  • Join Filter: (pp.supplier_id = ss.supplier_id)
  • Rows Removed by Join Filter: 28676609
33. 125.693 1,211.484 ↓ 107,403.0 107,403 1

Nested Loop (cost=1.42..463.04 rows=1 width=103) (actual time=5.014..1,211.484 rows=107,403 loops=1)

34. 125.254 756.778 ↓ 109,671.0 109,671 1

Nested Loop (cost=1.12..462.68 rows=1 width=39) (actual time=4.991..756.778 rows=109,671 loops=1)

35. 23.241 192.840 ↓ 109,671.0 109,671 1

Nested Loop (cost=0.70..462.23 rows=1 width=39) (actual time=4.954..192.840 rows=109,671 loops=1)

36. 0.560 7.385 ↓ 17.0 17 1

Nested Loop (cost=0.28..25.02 rows=1 width=40) (actual time=4.797..7.385 rows=17 loops=1)

  • Join Filter: (pp.supplier_id = sps.fk_supplier_id)
  • Rows Removed by Join Filter: 6209
37. 0.074 1.589 ↓ 22.0 22 1

Nested Loop (cost=0.28..8.32 rows=1 width=32) (actual time=1.263..1.589 rows=22 loops=1)

38. 1.317 1.317 ↓ 22.0 22 1

CTE Scan on public_price pp (cost=0.00..0.02 rows=1 width=16) (actual time=1.224..1.317 rows=22 loops=1)

39. 0.198 0.198 ↑ 1.0 1 22

Index Scan using supplier_price_list_pkey on supplier_price_list spl (cost=0.28..8.29 rows=1 width=16) (actual time=0.009..0.009 rows=1 loops=22)

  • Index Cond: (id = pp.active_id)
  • Filter: (pp.supplier_id = fk_supplier_id)
40. 5.236 5.236 ↓ 70.8 283 22

CTE Scan on supplier_postfactum_state sps (cost=0.00..16.65 rows=4 width=8) (actual time=0.154..0.238 rows=283 loops=22)

  • Filter: ((supplier_state)::text = 'OPERATIVE'::text)
  • Rows Removed by Filter: 28
41. 162.214 162.214 ↓ 1.3 6,451 17

Index Scan using suppliers_product_fk_supplier_price_list_id_idx on suppliers_product sp (cost=0.43..387.09 rows=5,012 width=23) (actual time=0.042..9.542 rows=6,451 loops=17)

  • Index Cond: (fk_supplier_price_list_id = spl.id)
  • Filter: (is_active AND (parse_status = 0))
  • Rows Removed by Filter: 297
42. 438.684 438.684 ↑ 1.0 1 109,671

Index Scan using product_name_pkey on product_name pn (cost=0.42..0.45 rows=1 width=16) (actual time=0.004..0.004 rows=1 loops=109,671)

  • Index Cond: (id = sp.fk_product_name_id)
43. 329.013 329.013 ↑ 1.0 1 109,671

Index Scan using product_pkey on product p (cost=0.29..0.36 rows=1 width=72) (actual time=0.003..0.003 rows=1 loops=109,671)

  • Index Cond: (id = pn.fk_product_id)
  • Filter: (fk_product_group_id IS NOT NULL)
  • Rows Removed by Filter: 0
44. 4,188.717 4,188.717 ↑ 1.7 268 107,403

CTE Scan on supplier_state ss (cost=0.00..8.92 rows=446 width=8) (actual time=0.000..0.039 rows=268 loops=107,403)

Planning time : 6.246 ms
Execution time : 7,918.614 ms