explain.depesz.com

PostgreSQL's explain analyze made readable

Result: lYI4

Settings
# exclusive inclusive rows x rows loops node
1. 0.012 600,905.372 ↑ 1.0 1 1

Result (cost=128,792,099.67..128,792,099.68 rows=1 width=32) (actual time=600,905.372..600,905.372 rows=1 loops=1)

2.          

CTE locations

3. 60.955 60.955 ↑ 2.8 354 1

Function Scan on con_get_locations (cost=0.25..10.25 rows=1,000 width=444) (actual time=60.935..60.955 rows=354 loops=1)

4.          

CTE rt_co_insert

5. 0.002 149.841 ↓ 0.0 0 1

Insert on rtco rtco_1 (cost=8,307.53..8,334.93 rows=265 width=12) (actual time=149.841..149.841 rows=0 loops=1)

  • Conflict Resolution: NOTHING
  • Tuples Inserted: 0
  • Conflicting Tuples: 0
6. 0.005 149.839 ↓ 0.0 0 1

Hash Join (cost=8,307.53..8,334.93 rows=265 width=12) (actual time=149.839..149.839 rows=0 loops=1)

  • Hash Cond: (l.brand_id = br.external_id)
7. 0.232 149.720 ↓ 0.0 0 1

Hash Join (cost=8,302.54..8,325.42 rows=500 width=8) (actual time=149.719..149.720 rows=0 loops=1)

  • Hash Cond: (((l.location_id)::character varying)::text = lo.internid)
8. 61.006 61.006 ↑ 2.8 354 1

CTE Scan on locations l (cost=0.00..20.00 rows=1,000 width=8) (actual time=60.937..61.006 rows=354 loops=1)

9. 23.083 88.482 ↓ 1.7 140,397 1

Hash (cost=7,280.07..7,280.07 rows=81,798 width=31) (actual time=88.482..88.482 rows=140,397 loops=1)

  • Buckets: 262,144 (originally 131072) Batches: 1 (originally 1) Memory Usage: 10,875kB
10. 55.013 65.399 ↓ 1.7 140,397 1

Seq Scan on rtlo lo (cost=607.12..7,280.07 rows=81,798 width=31) (actual time=19.254..65.399 rows=140,397 loops=1)

  • Filter: (NOT (hashed SubPlan 2))
  • Rows Removed by Filter: 24,507
11.          

SubPlan (for Seq Scan)

12. 5.860 10.386 ↓ 2.7 24,507 1

Unique (cost=0.29..584.22 rows=9,157 width=4) (actual time=0.029..10.386 rows=24,507 loops=1)

13. 4.526 4.526 ↓ 2.7 24,507 1

Index Only Scan using rtco_pkey on rtco (cost=0.29..561.33 rows=9,157 width=4) (actual time=0.027..4.526 rows=24,507 loops=1)

  • Heap Fetches: 642
14. 0.032 0.114 ↓ 1.0 108 1

Hash (cost=3.66..3.66 rows=106 width=8) (actual time=0.114..0.114 rows=108 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 13kB
15. 0.082 0.082 ↓ 1.0 108 1

Seq Scan on rtbr br (cost=0.00..3.66 rows=106 width=8) (actual time=0.020..0.082 rows=108 loops=1)

  • Filter: (source = 'datastore'::text)
  • Rows Removed by Filter: 27
16.          

CTE rt_peco_insert

17. 6.811 8.323 ↑ 2.8 354 1

Insert on rtpecoav (cost=0.42..7,443.50 rows=1,000 width=56) (actual time=0.168..8.323 rows=354 loops=1)

  • Conflict Resolution: UPDATE
  • Conflict Arbiter Indexes: rtpecoav_unique
  • Tuples Inserted: 0
  • Conflicting Tuples: 354
18. 0.410 1.512 ↑ 2.8 354 1

Nested Loop (cost=0.42..7,443.50 rows=1,000 width=56) (actual time=0.056..1.512 rows=354 loops=1)

19. 0.040 0.040 ↑ 2.8 354 1

CTE Scan on locations l_1 (cost=0.00..20.00 rows=1,000 width=4) (actual time=0.000..0.040 rows=354 loops=1)

20. 1.062 1.062 ↑ 1.0 1 354

Index Scan using rtlo_unique_internid on rtlo lo_1 (cost=0.42..7.42 rows=1 width=31) (actual time=0.003..0.003 rows=1 loops=354)

  • Index Cond: (internid = ((l_1.location_id)::character varying)::text)
21.          

CTE rt_sepeco_insert_active

22. 0.003 450,406.392 ↓ 0.0 0 1

Insert on rtsepecoav rtsepecoav_1 (cost=5.41..96,580,213.11 rows=795 width=64) (actual time=450,406.391..450,406.392 rows=0 loops=1)

23. 242,502.852 450,406.389 ↓ 0.0 0 1

Nested Loop (cost=5.41..96,580,213.11 rows=795 width=64) (actual time=450,406.389..450,406.389 rows=0 loops=1)

  • Join Filter: (NOT (SubPlan 5))
  • Rows Removed by Join Filter: 1,062
24. 1.551 8.187 ↑ 1.5 354 1

Nested Loop (cost=5.41..3,965.84 rows=530 width=4) (actual time=0.047..8.187 rows=354 loops=1)

25. 0.983 1.326 ↑ 1.5 354 1

Hash Join (cost=4.99..34.04 rows=530 width=4) (actual time=0.040..1.326 rows=354 loops=1)

  • Hash Cond: (l_2.brand_id = br_1.external_id)
26. 0.311 0.311 ↑ 2.8 354 1

CTE Scan on locations l_2 (cost=0.00..20.00 rows=1,000 width=8) (actual time=0.000..0.311 rows=354 loops=1)

27. 0.008 0.032 ↓ 1.0 108 1

Hash (cost=3.66..3.66 rows=106 width=4) (actual time=0.032..0.032 rows=108 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
28. 0.024 0.024 ↓ 1.0 108 1

Seq Scan on rtbr br_1 (cost=0.00..3.66 rows=106 width=4) (actual time=0.009..0.024 rows=108 loops=1)

  • Filter: (source = 'datastore'::text)
  • Rows Removed by Filter: 27
29. 5.310 5.310 ↑ 1.0 1 354

Index Scan using rtlo_unique_internid on rtlo lo_2 (cost=0.42..7.42 rows=1 width=31) (actual time=0.015..0.015 rows=1 loops=354)

  • Index Cond: (internid = ((l_2.location_id)::character varying)::text)
30. 0.347 0.354 ↑ 1.0 3 354

Materialize (cost=0.00..1.04 rows=3 width=4) (actual time=0.000..0.001 rows=3 loops=354)

31. 0.007 0.007 ↑ 1.0 3 1

Seq Scan on rtse se (cost=0.00..1.03 rows=3 width=4) (actual time=0.004..0.007 rows=3 loops=1)

32.          

SubPlan (for Nested Loop)

33. 207,576.873 207,894.996 ↑ 1.0 3,735,160 1,062

Materialize (cost=0.00..111,776.44 rows=3,881,229 width=16) (actual time=0.015..195.758 rows=3,735,160 loops=1,062)

34. 318.123 318.123 ↑ 1.0 3,735,691 1

Seq Scan on rtsepecoav (cost=0.00..73,418.29 rows=3,881,229 width=16) (actual time=0.006..318.123 rows=3,735,691 loops=1)

35.          

CTE rt_sepeco_insert_surface

36. 0.000 150,340.710 ↓ 0.0 0 1

Insert on rtsepecoav rtsepecoav_3 (cost=7.51..32,196,045.54 rows=265 width=64) (actual time=150,340.710..150,340.710 rows=0 loops=1)

37.          

Initplan (for Insert)

38. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=1.04..1.05 rows=1 width=4) (never executed)

39. 0.000 0.000 ↓ 0.0 0

Seq Scan on rtse (cost=0.00..1.03 rows=3 width=4) (never executed)

40. 0.003 0.007 ↑ 1.0 1 1

Aggregate (cost=1.04..1.05 rows=1 width=4) (actual time=0.007..0.007 rows=1 loops=1)

41. 0.004 0.004 ↑ 1.0 3 1

Seq Scan on rtse rtse_1 (cost=0.00..1.03 rows=3 width=4) (actual time=0.004..0.004 rows=3 loops=1)

42. 0.862 150,340.707 ↓ 0.0 0 1

Nested Loop (cost=5.41..32,196,043.44 rows=265 width=64) (actual time=150,340.707..150,340.707 rows=0 loops=1)

43. 1.023 1.355 ↑ 1.5 354 1

Hash Join (cost=4.99..34.04 rows=530 width=8) (actual time=0.041..1.355 rows=354 loops=1)

  • Hash Cond: (l_3.brand_id = br_2.external_id)
44. 0.300 0.300 ↑ 2.8 354 1

CTE Scan on locations l_3 (cost=0.00..20.00 rows=1,000 width=12) (actual time=0.000..0.300 rows=354 loops=1)

45. 0.008 0.032 ↓ 1.0 108 1

Hash (cost=3.66..3.66 rows=106 width=4) (actual time=0.032..0.032 rows=108 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
46. 0.024 0.024 ↓ 1.0 108 1

Seq Scan on rtbr br_2 (cost=0.00..3.66 rows=106 width=4) (actual time=0.010..0.024 rows=108 loops=1)

  • Filter: (source = 'datastore'::text)
  • Rows Removed by Filter: 27
47. 79,816.380 150,338.490 ↓ 0.0 0 354

Index Scan using rtlo_unique_internid on rtlo lo_3 (cost=0.42..60,747.18 rows=1 width=31) (actual time=424.685..424.685 rows=0 loops=354)

  • Index Cond: (internid = ((l_3.location_id)::character varying)::text)
  • Filter: (NOT (SubPlan 9))
  • Rows Removed by Filter: 1
48.          

SubPlan (for Index Scan)

49. 70,225.317 70,522.110 ↑ 1.0 3,739,078 354

Materialize (cost=0.00..111,776.44 rows=3,881,229 width=16) (actual time=0.012..199.215 rows=3,739,078 loops=354)

50. 296.793 296.793 ↑ 1.0 3,742,272 1

Seq Scan on rtsepecoav rtsepecoav_2 (cost=0.00..73,418.29 rows=3,881,229 width=16) (actual time=0.006..296.793 rows=3,742,272 loops=1)

51.          

Initplan (for Result)

52. 0.004 149.846 ↑ 1.0 1 1

Aggregate (cost=5.96..5.97 rows=1 width=8) (actual time=149.846..149.846 rows=1 loops=1)

53. 149.842 149.842 ↓ 0.0 0 1

CTE Scan on rt_co_insert (cost=0.00..5.30 rows=265 width=0) (actual time=149.842..149.842 rows=0 loops=1)

54. 0.021 8.405 ↑ 1.0 1 1

Aggregate (cost=22.50..22.51 rows=1 width=8) (actual time=8.405..8.405 rows=1 loops=1)

55. 8.384 8.384 ↑ 2.8 354 1

CTE Scan on rt_peco_insert (cost=0.00..20.00 rows=1,000 width=0) (actual time=0.169..8.384 rows=354 loops=1)

56. 0.003 450,406.395 ↑ 1.0 1 1

Aggregate (cost=17.89..17.90 rows=1 width=8) (actual time=450,406.395..450,406.395 rows=1 loops=1)

57. 450,406.392 450,406.392 ↓ 0.0 0 1

CTE Scan on rt_sepeco_insert_active (cost=0.00..15.90 rows=795 width=0) (actual time=450,406.392..450,406.392 rows=0 loops=1)

58. 0.003 150,340.714 ↑ 1.0 1 1

Aggregate (cost=5.96..5.97 rows=1 width=8) (actual time=150,340.714..150,340.714 rows=1 loops=1)

59. 150,340.711 150,340.711 ↓ 0.0 0 1

CTE Scan on rt_sepeco_insert_surface (cost=0.00..5.30 rows=265 width=0) (actual time=150,340.711..150,340.711 rows=0 loops=1)

Planning time : 1.835 ms
Execution time : 600,936.695 ms
Trigger times:
Trigger Name:Total time:Calls:Average time:
tg_rtpecoav_insert on rtpecoav 0.126 ms 1 0.126 ms
tg_rtpecoav_update on rtpecoav 0.513 ms 1 0.513 ms
tg_rtsepecoav_insert on rtsepecoav 0.184 ms 1 0.184 ms