explain.depesz.com

PostgreSQL's explain analyze made readable

Result: HOek

Settings
# exclusive inclusive rows x rows loops node
1. 88.410 13,471.478 ↓ 1.3 206,387 1

Sort (cost=1,566,833.93..1,567,236.34 rows=160,964 width=32) (actual time=13,450.650..13,471.478 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. 105.032 13,383.068 ↓ 1.3 206,387 1

WindowAgg (cost=1,545,840.68..1,549,059.96 rows=160,964 width=32) (actual time=13,252.240..13,383.068 rows=206,387 loops=1)

3. 61.656 13,278.036 ↓ 1.3 206,387 1

Sort (cost=1,545,840.68..1,546,243.09 rows=160,964 width=20) (actual time=13,252.230..13,278.036 rows=206,387 loops=1)

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

Gather (cost=924,070.00..1,528,616.21 rows=160,964 width=20) (actual time=12,859.813..13,216.380 rows=206,387 loops=1)

  • Workers Planned: 2
  • Params Evaluated: $1
  • Workers Launched: 2
5. 21.489 13,119.109 ↓ 1.0 68,796 3 / 3

Hash Left Join (cost=923,070.00..1,511,519.81 rows=67,068 width=20) (actual time=12,835.711..13,119.109 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. 319.438 13,094.534 ↓ 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=12,832.607..13,094.534 rows=68,016 loops=3)

  • Hash Cond: ((n.dp_oid = cbl.dp_oid) AND (n.dp_otype = cbl.dp_otype))
7. 104.548 12,271.244 ↓ 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=10,620.329..12,271.244 rows=68,016 loops=3)

8. 571.100 10,806.376 ↓ 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=10,620.165..10,806.376 rows=68,016 loops=3)

  • Hash Cond: (o.down_oid = ct.dp_oid)
9. 10,184.932 10,184.932 ↓ 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.014..10,184.932 rows=1,497,757 loops=3)

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

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

  • Buckets: 131072 Batches: 8 Memory Usage: 2528kB
11. 29.953 29.953 ↑ 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.046..29.953 rows=100,021 loops=3)

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

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

  • Hash Cond: (org.org_unit_id = n.org_unit)
13. 407.876 407.876 ↑ 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.006 rows=75 loops=203,938)

14. 0.000 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. 340.080 340.080 ↑ 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.005..0.005 rows=1 loops=204,048)

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

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

  • Buckets: 131072 Batches: 64 Memory Usage: 3488kB
17. 247.097 247.097 ↑ 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.013..247.097 rows=1,093,723 loops=3)

18. 1.661 3.086 ↑ 1.0 5,989 3 / 3

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

  • Buckets: 8192 Batches: 1 Memory Usage: 421kB
19. 1.425 1.425 ↑ 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.036..1.425 rows=6,061 loops=3)

Planning time : 2.129 ms
Execution time : 13,580.388 ms