explain.depesz.com

PostgreSQL's explain analyze made readable

Result: cDmS

Settings
# exclusive inclusive rows x rows loops node
1. 98.496 3,945.716 ↓ 1.3 206,387 1

Sort (cost=1,566,833.93..1,567,236.34 rows=160,964 width=32) (actual time=3,924.616..3,945.716 rows=206,387 loops=1)

  • Sort Key: o.down_oid, n.dp_state, lt.ltfpos, (row_number() OVER (?)), org.adms_sub_area_id
  • Sort Method: external merge Disk: 8912kB
2. 99.264 3,847.220 ↓ 1.3 206,387 1

WindowAgg (cost=1,545,840.68..1,549,059.96 rows=160,964 width=32) (actual time=3,723.090..3,847.220 rows=206,387 loops=1)

3. 56.179 3,747.956 ↓ 1.3 206,387 1

Sort (cost=1,545,840.68..1,546,243.09 rows=160,964 width=20) (actual time=3,723.082..3,747.956 rows=206,387 loops=1)

  • Sort Key: o.down_oid, lt.ltfpos DESC NULLS LAST
  • Sort Method: external merge Disk: 5872kB
4. 92.430 3,691.777 ↓ 1.3 206,387 1

Gather (cost=924,070.00..1,528,616.21 rows=160,964 width=20) (actual time=3,493.974..3,691.777 rows=206,387 loops=1)

  • Workers Planned: 2
  • Params Evaluated: $1
  • Workers Launched: 2
5. 20.374 3,599.347 ↓ 1.0 68,796 3 / 3

Hash Left Join (cost=923,070.00..1,511,519.81 rows=67,068 width=20) (actual time=3,474.917..3,599.347 rows=68,796 loops=3)

  • Hash Cond: ((cbl.cable_type = lt.cable_type) AND (COALESCE(cbl.laying_type, 0) = ("left"((lt.ltid)::text, 1))::integer))
6. 149.487 3,575.916 ↓ 1.0 68,016 3 / 3

Parallel Hash Left Join (cost=922,786.47..1,509,213.97 rows=67,068 width=24) (actual time=3,471.844..3,575.916 rows=68,016 loops=3)

  • Hash Cond: ((n.dp_oid = cbl.dp_oid) AND (n.dp_otype = cbl.dp_otype))
7. 114.603 2,929.605 ↓ 1.0 68,016 3 / 3

Nested Loop Left Join (cost=829,366.62..1,407,826.09 rows=67,068 width=24) (actual time=1,376.720..2,929.605 rows=68,016 loops=3)

8. 439.046 1,522.698 ↓ 1.0 68,016 3 / 3

Parallel Hash Semi Join (cost=829,360.18..1,270,246.41 rows=67,068 width=16) (actual time=1,376.621..1,522.698 rows=68,016 loops=3)

  • Hash Cond: (o.down_oid = ct.dp_oid)
9. 1,032.788 1,032.788 ↓ 1.5 1,497,757 3 / 3

Parallel Seq Scan on oolnk o (cost=0.00..423,953.03 rows=981,275 width=16) (actual time=0.019..1,032.788 rows=1,497,757 loops=3)

  • Filter: ((dp_otype = 1500100) AND (down_otype = 1500002))
  • Rows Removed by Filter: 5718458
10. 20.184 50.864 ↑ 1.5 100,021 3 / 3

Parallel Hash (cost=826,882.86..826,882.86 rows=150,985 width=8) (actual time=50.864..50.864 rows=100,021 loops=3)

  • Buckets: 131072 Batches: 8 Memory Usage: 2528kB
11. 30.680 30.680 ↑ 1.5 100,021 3 / 3

Parallel Index Scan using csf_trans_generation_dp_otype_action_code_valid_idx on csf_trans ct (cost=0.57..826,882.86 rows=150,985 width=8) (actual time=0.068..30.680 rows=100,021 loops=3)

  • Index Cond: ((generation = $1) AND (dp_otype = 1500002) AND ((action_code)::text = 'A'::text))
12. 612.327 1,292.304 ↑ 1.0 1 204,048 / 3

Hash Right Join (cost=6.44..8.48 rows=1 width=16) (actual time=0.011..0.019 rows=1 loops=204,048)

  • Hash Cond: (org.org_unit_id = n.org_unit)
13. 339.897 339.897 ↑ 1.0 75 203,938 / 3

Seq Scan on org_unit_cst org (cost=0.00..1.75 rows=75 width=8) (actual time=0.001..0.005 rows=75 loops=203,938)

14. 68.016 340.080 ↑ 1.0 1 204,048 / 3

Hash (cost=6.43..6.43 rows=1 width=16) (actual time=0.005..0.005 rows=1 loops=204,048)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
15. 272.064 272.064 ↑ 1.0 1 204,048 / 3

Index Scan using elname_oid_idx on elname n (cost=0.43..6.43 rows=1 width=16) (actual time=0.004..0.004 rows=1 loops=204,048)

  • Index Cond: (dp_oid = o.up_oid)
  • Filter: (dp_otype = o.up_otype)
16. 253.187 496.824 ↑ 1.3 1,093,723 3 / 3

Parallel Hash (cost=66,236.54..66,236.54 rows=1,367,154 width=16) (actual time=496.824..496.824 rows=1,093,723 loops=3)

  • Buckets: 131072 Batches: 64 Memory Usage: 3456kB
17. 243.637 243.637 ↑ 1.3 1,093,723 3 / 3

Parallel Seq Scan on elcbl cbl (cost=0.00..66,236.54 rows=1,367,154 width=16) (actual time=0.012..243.637 rows=1,093,723 loops=3)

18. 1.667 3.057 ↑ 1.0 5,989 3 / 3

Hash (cost=192.61..192.61 rows=6,061 width=28) (actual time=3.057..3.057 rows=5,989 loops=3)

  • Buckets: 8192 Batches: 1 Memory Usage: 421kB
19. 1.390 1.390 ↑ 1.0 6,061 3 / 3

Seq Scan on linetype_cl lt (cost=0.00..192.61 rows=6,061 width=28) (actual time=0.027..1.390 rows=6,061 loops=3)

Planning time : 1.911 ms
Execution time : 4,055.384 ms