explain.depesz.com

PostgreSQL's explain analyze made readable

Result: PaXQW

Settings
# exclusive inclusive rows x rows loops node
1. 0.082 35,686.871 ↑ 1.0 1 1

Limit (cost=30,295,552.57..30,295,555.96 rows=1 width=729) (actual time=35,686.791..35,686.871 rows=1 loops=1)

2.          

CTE territory_group_mapping

3. 2.460 5.944 ↓ 12.4 323 1

HashAggregate (cost=56.28..56.60 rows=26 width=36) (actual time=5.759..5.944 rows=323 loops=1)

  • Group Key: etg.external_identifier
4. 0.427 3.484 ↓ 35.8 930 1

Hash Join (cost=7.64..56.15 rows=26 width=8) (actual time=3.064..3.484 rows=930 loops=1)

  • Hash Cond: (songtrust_muma_external_system_1.id = etg.external_system_id)
5. 0.009 0.009 ↑ 2,550.0 1 1

Seq Scan on songtrust_muma_external_system songtrust_muma_external_system_1 (cost=0.00..35.50 rows=2,550 width=4) (actual time=0.006..0.009 rows=1 loops=1)

6. 0.711 3.048 ↓ 704.5 1,409 1

Hash (cost=7.61..7.61 rows=2 width=12) (actual time=3.048..3.048 rows=1,409 loops=1)

  • Buckets: 2,048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 82kB
7. 0.723 2.337 ↓ 704.5 1,409 1

Nested Loop (cost=3.72..7.61 rows=2 width=12) (actual time=0.217..2.337 rows=1,409 loops=1)

  • Join Filter: (pas.territory_group_id = etg.territory_group_id)
8. 0.027 0.594 ↓ 51.0 51 1

Nested Loop (cost=3.44..7.01 rows=1 width=8) (actual time=0.197..0.594 rows=51 loops=1)

9. 0.065 0.414 ↓ 51.0 51 1

Nested Loop (cost=3.17..6.50 rows=1 width=4) (actual time=0.183..0.414 rows=51 loops=1)

10. 0.062 0.232 ↓ 39.0 39 1

Hash Join (cost=3.03..6.15 rows=1 width=4) (actual time=0.162..0.232 rows=39 loops=1)

  • Hash Cond: (pa_1.publishing_administrator_party_id = p_1.id)
11. 0.028 0.028 ↑ 1.0 88 1

Seq Scan on partner_account pa_1 (cost=0.00..2.88 rows=88 width=8) (actual time=0.010..0.028 rows=88 loops=1)

12. 0.012 0.142 ↑ 1.0 1 1

Hash (cost=3.01..3.01 rows=1 width=4) (actual time=0.142..0.142 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
13. 0.130 0.130 ↑ 1.0 1 1

Seq Scan on party p_1 (cost=0.00..3.01 rows=1 width=4) (actual time=0.017..0.130 rows=1 loops=1)

  • Filter: (full_name = 'Songtrust'::citext)
  • Rows Removed by Filter: 80
14. 0.117 0.117 ↑ 1.0 1 39

Index Scan using partner_account_scope_account_id_4c7cde38 on partner_account_scope pas (cost=0.14..0.34 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=39)

  • Index Cond: (account_id = pa_1.id)
15. 0.153 0.153 ↑ 1.0 1 51

Index Only Scan using territory_group_pkey on territory_group tg (cost=0.28..0.51 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=51)

  • Index Cond: (id = pas.territory_group_id)
  • Heap Fetches: 0
16. 1.020 1.020 ↓ 14.0 28 51

Index Scan using external_identifier_territory_group_territory_group_id_da2b0189 on external_identifier_territory_group etg (cost=0.28..0.58 rows=2 width=12) (actual time=0.005..0.020 rows=28 loops=51)

  • Index Cond: (territory_group_id = tg.id)
  • Filter: ((external_field_name)::text = 'TECD'::text)
17.          

CTE multi_childless_nodes_per_chain

18. 2.606 233.312 ↓ 5.0 5 1

GroupAggregate (cost=922.44..923.31 rows=1 width=6) (actual time=232.702..233.312 rows=5 loops=1)

  • Group Key: p_2."TITUCD", p_2."TICHAIN
  • Filter: (count(*) > 1)
  • Rows Removed by Filter: 5,263
19. 6.528 230.706 ↓ 61.3 5,273 1

Sort (cost=922.44..922.65 rows=86 width=10) (actual time=230.171..230.706 rows=5,273 loops=1)

  • Sort Key: p_2."TITUCD", p_2."TICHAIN
  • Sort Method: quicksort Memory: 440kB
20. 224.178 224.178 ↓ 61.3 5,273 1

Foreign Scan on songtrust_songipcp p_2 (cost=100.00..919.67 rows=86 width=10) (actual time=211.656..224.178 rows=5,273 loops=1)

  • Filter: (btrim(COALESCE("TIIPLINK", ''::text)) = ''::text)
  • Rows Removed by Filter: 11,832
21.          

CTE controlled_chain_without_op

22. 55.701 55.701 ↓ 0.0 0 1

Foreign Scan (cost=683.89..718.09 rows=1 width=6) (actual time=55.700..55.701 rows=0 loops=1)

  • Relations: Aggregate on (maestro_incoming.songtrust_songipcp ip)
23. 0.081 35,686.789 ↑ 353,430.0 1 1

WindowAgg (cost=30,293,854.56..31,493,539.11 rows=353,430 width=729) (actual time=35,686.789..35,686.789 rows=1 loops=1)

24. 0.035 35,686.708 ↑ 70,686.0 5 1

Nested Loop Left Join (cost=30,293,854.56..31,481,169.06 rows=353,430 width=564) (actual time=35,133.720..35,686.708 rows=5 loops=1)

  • Join Filter: (cm.code = p."TICYCD")
  • Rows Removed by Join Filter: 127
25. 0.011 35,686.618 ↑ 22,440.0 5 1

Nested Loop Left Join (cost=30,293,854.56..30,420,861.19 rows=112,200 width=560) (actual time=35,133.681..35,686.618 rows=5 loops=1)

  • Join Filter: ((npc."TITUCD" = p."TITUCD") AND (npc."TICHAIN" = p."TICHAIN"))
26. 0.014 35,453.277 ↑ 22,440.0 5 1

Nested Loop Left Join (cost=30,293,854.56..30,418,056.18 rows=112,200 width=560) (actual time=34,900.354..35,453.277 rows=5 loops=1)

  • Join Filter: ((nop."TITUCD" = p."TITUCD") AND (nop."TICHAIN" = p."TICHAIN"))
27. 0.076 35,397.558 ↑ 22,440.0 5 1

Nested Loop Left Join (cost=30,293,854.56..30,415,251.16 rows=112,200 width=560) (actual time=34,844.646..35,397.558 rows=5 loops=1)

  • Join Filter: ((p."TICORP" = 'P'::text) AND (ep.external_system_id = songtrust_muma_external_system.id) AND ((ep.external_identifier)::text = p."TIIPCD"))
  • Rows Removed by Join Filter: 246
28. 451.453 35,397.397 ↑ 22,440.0 5 1

Nested Loop Left Join (cost=30,293,854.56..30,303,049.41 rows=112,200 width=560) (actual time=34,844.572..35,397.397 rows=5 loops=1)

  • Join Filter: ((ps.counterpoint_number)::text = (p."TITUCD")::text)
  • Rows Removed by Join Filter: 1,131,490
29. 0.012 61.049 ↑ 22,440.0 5 1

Nested Loop Left Join (cost=33,320.21..40,271.03 rows=112,200 width=414) (actual time=60.947..61.049 rows=5 loops=1)

  • Join Filter: ((eip.external_system_id = songtrust_muma_external_system.id) AND (((eip.external_identifier)::jsonb ->> 'tiipcd'::text) = p."TIIPCD") AND ((((eip.external_identifier)::jsonb ->> 'titucd'::text))::integer = p."TITUCD") AND ((((eip.external_identifier)::jsonb ->> 'tichain'::text))::integer = p."TICHAIN"))
30. 0.276 60.977 ↑ 22,440.0 5 1

Merge Left Join (cost=33,312.05..34,091.84 rows=112,200 width=410) (actual time=60.881..60.977 rows=5 loops=1)

  • Merge Cond: (p."TITECD" = (tm.muma_tecd_code)::text)
31. 0.269 51.627 ↑ 22,440.0 5 1

Sort (cost=33,310.92..33,591.42 rows=112,200 width=378) (actual time=51.624..51.627 rows=5 loops=1)

  • Sort Key: p."TITECD
  • Sort Method: quicksort Memory: 32kB
32. 0.122 51.358 ↑ 2,200.0 51 1

Hash Left Join (cost=248.01..4,338.25 rows=112,200 width=378) (actual time=51.086..51.358 rows=51 loops=1)

  • Hash Cond: ((songtrust_muma_external_system.id = es.external_system_id) AND ((p."TITUCD")::text = (es.external_identifier)::text))
33. 0.083 50.306 ↑ 2,200.0 51 1

Hash Left Join (cost=131.22..2,532.84 rows=112,200 width=374) (actual time=50.138..50.306 rows=51 loops=1)

  • Hash Cond: ((songtrust_muma_external_system.id = esw.external_system_id) AND (p."TIIPCD" = (esw.external_identifier)::text))
  • Join Filter: (p."TICORP" = 'C'::text)
34. 0.081 49.906 ↑ 2,200.0 51 1

Nested Loop (cost=104.63..1,661.45 rows=112,200 width=370) (actual time=49.798..49.906 rows=51 loops=1)

35. 49.774 49.774 ↓ 1.2 51 1

Foreign Scan on songtrust_songipcp p (cost=104.63..217.08 rows=44 width=54) (actual time=49.762..49.774 rows=51 loops=1)

36. 0.028 0.051 ↑ 2,550.0 1 51

Materialize (cost=0.00..48.25 rows=2,550 width=4) (actual time=0.001..0.001 rows=1 loops=51)

37. 0.023 0.023 ↑ 2,550.0 1 1

Seq Scan on songtrust_muma_external_system (cost=0.00..35.50 rows=2,550 width=4) (actual time=0.022..0.023 rows=1 loops=1)

38. 0.142 0.317 ↑ 1.0 94 1

Hash (cost=25.18..25.18 rows=94 width=14) (actual time=0.317..0.317 rows=94 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 13kB
39. 0.175 0.175 ↑ 1.0 94 1

Seq Scan on external_identifier_songwriter esw (cost=0.00..25.18 rows=94 width=14) (actual time=0.015..0.175 rows=94 loops=1)

  • Filter: ((external_field_name)::text = 'CRCD'::text)
40. 0.310 0.930 ↑ 1.0 374 1

Hash (cost=111.19..111.19 rows=374 width=15) (actual time=0.930..0.930 rows=374 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 26kB
41. 0.620 0.620 ↑ 1.0 374 1

Seq Scan on external_identifier_song es (cost=0.00..111.19 rows=374 width=15) (actual time=0.190..0.620 rows=374 loops=1)

  • Filter: ((external_field_name)::text = 'TUTCD'::text)
  • Rows Removed by Filter: 281
42. 2.903 9.074 ↓ 8.6 223 1

Sort (cost=1.13..1.20 rows=26 width=64) (actual time=9.032..9.074 rows=223 loops=1)

  • Sort Key: tm.muma_tecd_code USING <
  • Sort Method: quicksort Memory: 51kB
43. 6.171 6.171 ↓ 12.4 323 1

CTE Scan on territory_group_mapping tm (cost=0.00..0.52 rows=26 width=64) (actual time=5.764..6.171 rows=323 loops=1)

44. 0.004 0.060 ↓ 0.0 0 5

Materialize (cost=8.15..8.19 rows=1 width=40) (actual time=0.012..0.012 rows=0 loops=5)

45. 0.001 0.056 ↓ 0.0 0 1

Subquery Scan on eip (cost=8.15..8.19 rows=1 width=40) (actual time=0.056..0.056 rows=0 loops=1)

46. 0.003 0.055 ↓ 0.0 0 1

GroupAggregate (cost=8.15..8.18 rows=1 width=138) (actual time=0.055..0.055 rows=0 loops=1)

  • Group Key: external_identifier_interested_party_chain.external_system_id, external_identifier_interested_party_chain.external_field_name, external_identifier_interested_party_chain.external_identifier
47. 0.037 0.052 ↓ 0.0 0 1

Sort (cost=8.15..8.16 rows=1 width=138) (actual time=0.052..0.052 rows=0 loops=1)

  • Sort Key: external_identifier_interested_party_chain.external_system_id, external_identifier_interested_party_chain.external_identifier
  • Sort Method: quicksort Memory: 25kB
48. 0.015 0.015 ↓ 0.0 0 1

Index Scan using external_identifier_intereste_int4_int41_text_external_syst_idx on external_identifier_interested_party_chain (cost=0.12..8.14 rows=1 width=138) (actual time=0.014..0.015 rows=0 loops=1)

  • Index Cond: ((((external_identifier)::jsonb ->> 'titucd'::text))::integer = 101,869)
49. 332.573 34,884.895 ↓ 226,298.0 226,298 5

Materialize (cost=30,260,534.36..30,260,534.38 rows=1 width=146) (actual time=6,883.801..6,976.979 rows=226,298 loops=5)

50. 370.025 34,552.322 ↓ 226,298.0 226,298 1

HashAggregate (cost=30,260,534.36..30,260,534.37 rows=1 width=146) (actual time=34,418.924..34,552.322 rows=226,298 loops=1)

  • Group Key: ps.counterpoint_number
51. 1,041.447 34,182.297 ↑ 6,982.5 279,978 1

Merge Join (cost=443,185.65..25,373,142.00 rows=1,954,956,941 width=146) (actual time=1,347.842..34,182.297 rows=279,978 loops=1)

  • Merge Cond: (pa.id = pw.managed_by_id)
52. 1,340.632 1,340.632 ↑ 444.4 216 1

Foreign Scan on publishing_app_user_profile pa (cost=100.42..28,906.34 rows=95,986 width=5) (actual time=674.800..1,340.632 rows=216 loops=1)

  • Filter: (is_b2b_client IS TRUE)
  • Rows Removed by Filter: 191,762
53. 1,420.005 31,800.218 ↓ 1.2 4,060,982 1

Materialize (cost=443,085.23..915,516.64 rows=3,394,518 width=11) (actual time=672.484..31,800.218 rows=4,060,982 loops=1)

54. 30,380.213 30,380.213 ↓ 1.2 4,060,982 1

Foreign Scan (cost=443,085.23..907,030.34 rows=3,394,518 width=11) (actual time=672.479..30,380.213 rows=4,060,982 loops=1)

  • Relations: ((rightsmanagement.publishing_app_song ps) INNER JOIN (rightsmanagement.publishing_app_song_ownership po)) INNER JOIN (rightsmanagement.publishing_app_songwriter pw)
55. 0.044 0.085 ↑ 1.0 50 5

Materialize (cost=0.00..1.88 rows=50 width=14) (actual time=0.005..0.017 rows=50 loops=5)

56. 0.041 0.041 ↑ 1.0 50 1

Seq Scan on external_identifier_publisher ep (cost=0.00..1.62 rows=50 width=14) (actual time=0.018..0.041 rows=50 loops=1)

  • Filter: ((external_field_name)::text = 'PUCD'::text)
57. 55.705 55.705 ↓ 0.0 0 5

CTE Scan on controlled_chain_without_op nop (cost=0.00..0.02 rows=1 width=6) (actual time=11.141..11.141 rows=0 loops=5)

  • Filter: ("TITUCD" = 101,869)
58. 233.330 233.330 ↓ 0.0 0 5

CTE Scan on multi_childless_nodes_per_chain npc (cost=0.00..0.02 rows=1 width=6) (actual time=46.666..46.666 rows=0 loops=5)

  • Filter: ("TITUCD" = 101,869)
  • Rows Removed by Filter: 5
59. 0.026 0.055 ↑ 24.2 26 5

Materialize (cost=0.00..19.45 rows=630 width=36) (actual time=0.005..0.011 rows=26 loops=5)

60. 0.029 0.029 ↑ 22.5 28 1

Seq Scan on st_capacity_node_type_mapping cm (cost=0.00..16.30 rows=630 width=36) (actual time=0.021..0.029 rows=28 loops=1)