explain.depesz.com

PostgreSQL's explain analyze made readable

Result: pBCr

Settings
# exclusive inclusive rows x rows loops node
1. 696.979 114,744.530 ↑ 1.0 1 1

Aggregate (cost=1,765,561.18..1,765,561.19 rows=1 width=8) (actual time=114,744.529..114,744.530 rows=1 loops=1)

2. 4,935.114 114,047.551 ↓ 10,677.8 1,046,429 1

Nested Loop (cost=1,572,351.11..1,765,559.95 rows=98 width=23,930) (actual time=20,263.264..114,047.551 rows=1,046,429 loops=1)

3.          

CTE m

4. 0.000 29,985.185 ↓ 47.4 1,046,429 1

Gather (cost=1,043,294.75..1,572,347.22 rows=22,078 width=129) (actual time=20,262.861..29,985.185 rows=1,046,429 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
5. 1,068.685 31,605.410 ↓ 37.9 348,810 3 / 3

Nested Loop (cost=1,042,294.75..1,569,139.42 rows=9,199 width=129) (actual time=21,035.107..31,605.410 rows=348,810 loops=3)

6. 1,267.791 25,964.908 ↓ 20.8 653,117 3 / 3

Hash Join (cost=1,042,294.31..1,527,113.69 rows=31,459 width=129) (actual time=21,034.986..25,964.908 rows=653,117 loops=3)

  • Hash Cond: ((m_1.objectid)::text = (m_2.objectid)::text)
7. 1,663.877 7,687.028 ↓ 6.3 653,117 3 / 3

Hash Join (cost=260,994.45..706,268.31 rows=103,923 width=129) (actual time=4,022.224..7,687.028 rows=653,117 loops=3)

  • Hash Cond: (m_1.tblreturnmetaid = t_returnmeta_sequence.tblreturnmetaid)
8. 2,107.836 2,154.031 ↓ 1.6 997,234 3 / 3

Parallel Seq Scan on tblreturnmeta m_1 (cost=60,162.39..501,234.82 rows=632,440 width=125) (actual time=90.071..2,154.031 rows=997,234 loops=3)

  • Filter: (fullyearreturn AND (archived IS NULL) AND (NOT (hashed SubPlan 1)))
  • Rows Removed by Filter: 2,962,502
9.          

SubPlan (for Parallel Seq Scan)

10. 46.195 46.195 ↓ 0.0 0 3 / 3

Index Scan using ix2tblreparse on tblreparse (cost=0.43..59,563.40 rows=239,594 width=19) (actual time=46.195..46.195 rows=0 loops=3)

  • Index Cond: ((mainformcompletedate IS NULL) AND (mainformrequestdate IS NOT NULL))
11. 1,538.175 3,869.120 ↑ 1.0 1,959,350 3 / 3

Hash (cost=176,250.40..176,250.40 rows=1,966,533 width=8) (actual time=3,869.120..3,869.120 rows=1,959,350 loops=3)

  • Buckets: 2,097,152 Batches: 1 Memory Usage: 92,922kB
12. 2,330.945 2,330.945 ↑ 1.0 1,959,350 3 / 3

Seq Scan on t_returnmeta_sequence (cost=0.00..176,250.40 rows=1,966,533 width=8) (actual time=88.794..2,330.945 rows=1,959,350 loops=3)

  • Filter: ((archived IS NULL) AND (fullyearsequence < 5))
  • Rows Removed by Filter: 4,046,484
13. 2,342.726 17,010.089 ↓ 7.9 2,985,316 3 / 3

Hash (cost=776,554.58..776,554.58 rows=379,623 width=19) (actual time=17,010.089..17,010.089 rows=2,985,316 loops=3)

  • Buckets: 4,194,304 (originally 524288) Batches: 1 (originally 1) Memory Usage: 181,451kB
14. 4,058.503 14,667.363 ↓ 7.9 2,985,316 3 / 3

Hash Join (cost=607,407.30..776,554.58 rows=379,623 width=19) (actual time=8,159.931..14,667.363 rows=2,985,316 loops=3)

  • Hash Cond: (t_returnmeta_sequence_1.tblreturnmetaid = m_2.tblreturnmetaid)
15. 2,459.754 2,459.754 ↑ 1.0 3,002,963 3 / 3

Seq Scan on t_returnmeta_sequence t_returnmeta_sequence_1 (cost=0.00..161,263.32 rows=3,003,411 width=4) (actual time=0.017..2,459.754 rows=3,002,963 loops=3)

  • Filter: (archived IS NULL)
  • Rows Removed by Filter: 3,002,871
16. 2,352.064 8,149.106 ↓ 2.0 2,985,316 3 / 3

Hash (cost=588,498.83..588,498.83 rows=1,512,678 width=23) (actual time=8,149.106..8,149.106 rows=2,985,316 loops=3)

  • Buckets: 4,194,304 (originally 2097152) Batches: 1 (originally 1) Memory Usage: 193,113kB
17. 5,792.852 5,797.042 ↓ 2.0 2,985,316 3 / 3

Seq Scan on tblreturnmeta m_2 (cost=60,162.39..588,498.83 rows=1,512,678 width=23) (actual time=12.276..5,797.042 rows=2,985,316 loops=3)

  • Filter: ((amendedbyobjectid IS NULL) AND (archived IS NULL) AND (NOT (hashed SubPlan 2)))
  • Rows Removed by Filter: 8,893,891
18.          

SubPlan (for Seq Scan)

19. 4.190 4.190 ↓ 0.0 0 3 / 3

Index Scan using ix2tblreparse on tblreparse tblreparse_1 (cost=0.43..59,563.40 rows=239,594 width=19) (actual time=4.189..4.190 rows=0 loops=3)

  • Index Cond: ((mainformcompletedate IS NULL) AND (mainformrequestdate IS NOT NULL))
20. 4,571.817 4,571.817 ↑ 1.0 1 1,959,350 / 3

Index Scan using ix2tblheaderraw on tblheaderraw tblheaderraw_1 (cost=0.43..1.34 rows=1 width=4) (actual time=0.007..0.007 rows=1 loops=1,959,350)

  • Index Cond: (tblheaderrawid = m_1.tblheaderrawid)
  • Filter: ((cnlatestbatch = 1) AND ((formtype)::text = '990'::text))
  • Rows Removed by Filter: 0
21. 1,951.976 102,833.863 ↓ 5,536.7 1,046,429 1

Nested Loop (cost=3.46..192,394.28 rows=189 width=4) (actual time=20,263.217..102,833.863 rows=1,046,429 loops=1)

22. 1,672.872 94,603.313 ↓ 2,768.3 1,046,429 1

Nested Loop (cost=3.03..190,758.69 rows=378 width=8) (actual time=20,263.167..94,603.313 rows=1,046,429 loops=1)

23. 1,969.107 85,605.438 ↓ 2,768.3 1,046,429 1

Nested Loop (cost=2.60..189,249.30 rows=378 width=12) (actual time=20,263.122..85,605.438 rows=1,046,429 loops=1)

24. 1,594.371 78,404.186 ↓ 1,437.4 1,046,429 1

Nested Loop (cost=2.16..186,052.79 rows=728 width=16) (actual time=20,263.100..78,404.186 rows=1,046,429 loops=1)

25. 2,345.549 68,438.383 ↓ 742.1 1,046,429 1

Nested Loop (cost=1.73..179,860.15 rows=1,410 width=20) (actual time=20,263.052..68,438.383 rows=1,046,429 loops=1)

26. 1,644.878 59,814.260 ↓ 385.7 1,046,429 1

Nested Loop (cost=1.30..168,060.01 rows=2,713 width=24) (actual time=20,263.015..59,814.260 rows=1,046,429 loops=1)

27. 2,044.596 50,844.379 ↓ 191.8 1,046,429 1

Nested Loop (cost=0.86..144,198.95 rows=5,455 width=28) (actual time=20,262.965..50,844.379 rows=1,046,429 loops=1)

28. 2,059.067 41,474.780 ↓ 95.0 1,046,429 1

Nested Loop (cost=0.43..96,036.86 rows=11,016 width=32) (actual time=20,262.918..41,474.780 rows=1,046,429 loops=1)

29. 32,090.710 32,090.710 ↓ 47.4 1,046,429 1

CTE Scan on m (cost=0.00..441.56 rows=22,078 width=36) (actual time=20,262.867..32,090.710 rows=1,046,429 loops=1)

30. 7,325.003 7,325.003 ↑ 1.0 1 1,046,429

Index Scan using ix2tblpracticesraw on tblpracticesraw (cost=0.43..4.33 rows=1 width=4) (actual time=0.007..0.007 rows=1 loops=1,046,429)

  • Index Cond: (tblpracticesrawid = m.tblpracticesrawid)
  • Filter: (cnlatestbatch = 1)
31. 7,325.003 7,325.003 ↑ 1.0 1 1,046,429

Index Scan using ix2tblbalanceraw on tblbalanceraw (cost=0.43..4.37 rows=1 width=4) (actual time=0.007..0.007 rows=1 loops=1,046,429)

  • Index Cond: (tblbalancerawid = m.tblbalancerawid)
  • Filter: (cnlatestbatch = 1)
32. 7,325.003 7,325.003 ↑ 1.0 1 1,046,429

Index Scan using ix2tblexpenseraw on tblexpenseraw (cost=0.43..4.37 rows=1 width=4) (actual time=0.007..0.007 rows=1 loops=1,046,429)

  • Index Cond: (tblexpenserawid = m.tblexpenserawid)
  • Filter: (cnlatestbatch = 1)
33. 6,278.574 6,278.574 ↑ 1.0 1 1,046,429

Index Scan using ix2tblrevenueraw on tblrevenueraw (cost=0.43..4.35 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=1,046,429)

  • Index Cond: (tblrevenuerawid = m.tblrevenuerawid)
  • Filter: (cnlatestbatch = 1)
34. 8,371.432 8,371.432 ↑ 1.0 1 1,046,429

Index Scan using ix2tblsummaryraw on tblsummaryraw (cost=0.43..4.39 rows=1 width=4) (actual time=0.008..0.008 rows=1 loops=1,046,429)

  • Index Cond: (tblsummaryrawid = m.tblsummaryrawid)
  • Filter: (cnlatestbatch = 1)
35. 5,232.145 5,232.145 ↑ 1.0 1 1,046,429

Index Scan using ix2tblheaderraw on tblheaderraw (cost=0.43..4.39 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=1,046,429)

  • Index Cond: (tblheaderrawid = m.tblheaderrawid)
  • Filter: (cnlatestbatch = 1)
36. 7,325.003 7,325.003 ↑ 1.0 1 1,046,429

Index Scan using ix2tblprogramraw on tblprogramraw (cost=0.43..3.99 rows=1 width=4) (actual time=0.007..0.007 rows=1 loops=1,046,429)

  • Index Cond: (tblprogramrawid = m.tblprogramrawid)
  • Filter: (cnlatestbatch = 1)
37. 6,278.574 6,278.574 ↑ 1.0 1 1,046,429

Index Scan using ix2tblreconciliationform990raw on tblreconciliationform990raw (cost=0.43..4.33 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=1,046,429)

  • Index Cond: (tblreconciliationform990rawid = m.tblreconciliationform990rawid)
  • Filter: (cnlatestbatch = 1)
38. 6,278.574 6,278.574 ↑ 1.0 1 1,046,429

Index Scan using ix2tblrequiredschedulesraw on tblrequiredschedulesraw (cost=0.43..4.33 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=1,046,429)

  • Index Cond: (tblrequiredschedulesrawid = m.tblrequiredschedulesrawid)
  • Filter: (cnlatestbatch = 1)
Planning time : 49.725 ms
Execution time : 114,788.967 ms