explain.depesz.com

PostgreSQL's explain analyze made readable

Result: sLWk : Optimization for: plan #ipj0

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 359.161 6,652.881 ↑ 40.1 40,359 1

Hash Left Join (cost=269,091.69..363,960.70 rows=1,619,028 width=1,472) (actual time=6,244.775..6,652.881 rows=40,359 loops=1)

  • Hash Cond: (pts.id = conn_pts.points_id)
2.          

CTE less_network_points

3. 7.773 2,856.390 ↑ 1.1 40,359 1

Hash Left Join (cost=73,608.53..189,863.05 rows=46,302 width=596) (actual time=1,086.680..2,856.390 rows=40,359 loops=1)

  • Hash Cond: (nptx.network_point_types_id = npt.id)
4. 1,081.728 2,848.604 ↑ 1.1 40,359 1

Hash Right Join (cost=73,607.08..189,714.30 rows=46,302 width=16) (actual time=1,086.652..2,848.604 rows=40,359 loops=1)

  • Hash Cond: ((pftx.feature_id = np.id) AND (pftx.layer_info_id = ftlx.layer_info_id))
  • Filter: (pftx.deleted_by IS NULL)
  • Rows Removed by Filter: 760250
5. 680.451 680.451 ↑ 1.0 2,841,566 1

Seq Scan on plans_features_types_xref pftx (cost=0.00..62,846.66 rows=2,841,566 width=16) (actual time=0.017..680.451 rows=2,841,566 loops=1)

6. 138.341 1,086.425 ↑ 1.0 800,609 1

Hash (cost=57,687.94..57,687.94 rows=800,609 width=12) (actual time=1,086.425..1,086.425 rows=800,609 loops=1)

  • Buckets: 131072 Batches: 16 Memory Usage: 3164kB
7. 141.718 948.084 ↑ 1.0 800,609 1

Hash Left Join (cost=25,472.46..57,687.94 rows=800,609 width=12) (actual time=250.415..948.084 rows=800,609 loops=1)

  • Hash Cond: (nptx.network_point_types_id = ftlx.feature_type_id)
8. 389.670 806.339 ↑ 1.0 800,609 1

Hash Left Join (cost=25,469.70..55,469.40 rows=800,609 width=8) (actual time=250.376..806.339 rows=800,609 loops=1)

  • Hash Cond: (np.id = nptx.network_points_id)
9. 166.491 166.491 ↑ 1.0 800,609 1

Seq Scan on network_points np (cost=0.00..18,514.09 rows=800,609 width=4) (actual time=0.011..166.491 rows=800,609 loops=1)

10. 132.364 250.178 ↑ 1.0 800,609 1

Hash (cost=12,334.09..12,334.09 rows=800,609 width=8) (actual time=250.178..250.178 rows=800,609 loops=1)

  • Buckets: 131072 Batches: 16 Memory Usage: 2984kB
11. 117.814 117.814 ↑ 1.0 800,609 1

Seq Scan on network_points_types_xref nptx (cost=0.00..12,334.09 rows=800,609 width=8) (actual time=0.009..117.814 rows=800,609 loops=1)

12. 0.010 0.027 ↑ 1.0 78 1

Hash (cost=1.78..1.78 rows=78 width=8) (actual time=0.027..0.027 rows=78 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
13. 0.017 0.017 ↑ 1.0 78 1

Seq Scan on feature_types_layers_xref ftlx (cost=0.00..1.78 rows=78 width=8) (actual time=0.009..0.017 rows=78 loops=1)

14. 0.005 0.013 ↑ 1.0 20 1

Hash (cost=1.20..1.20 rows=20 width=584) (actual time=0.013..0.013 rows=20 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
15. 0.008 0.008 ↑ 1.0 20 1

Seq Scan on network_point_types npt (cost=0.00..1.20 rows=20 width=584) (actual time=0.005..0.008 rows=20 loops=1)

16.          

CTE attr

17. 59.973 737.099 ↓ 174.0 40,359 1

GroupAggregate (cost=2,282.61..2,287.25 rows=232 width=36) (actual time=667.655..737.099 rows=40,359 loops=1)

  • Group Key: npa.network_points_id
18. 65.524 677.126 ↓ 363.6 84,345 1

Sort (cost=2,282.61..2,283.19 rows=232 width=24) (actual time=667.631..677.126 rows=84,345 loops=1)

  • Sort Key: npa.network_points_id
  • Sort Method: external merge Disk: 2496kB
19. 132.685 611.602 ↓ 363.6 84,345 1

Nested Loop (cost=13.96..2,273.50 rows=232 width=24) (actual time=0.225..611.602 rows=84,345 loops=1)

  • Join Filter: (npta.attribute_names_id = npa.attribute_names_id)
  • Rows Removed by Join Filter: 906674
20. 16.542 22.257 ↓ 287.2 91,332 1

Hash Join (cost=13.53..1,115.52 rows=318 width=21) (actual time=0.201..22.257 rows=91,332 loops=1)

  • Hash Cond: (np_1.network_point_types_id = npta.network_point_types_id)
21. 5.520 5.520 ↑ 1.1 40,359 1

CTE Scan on less_network_points np_1 (cost=0.00..926.04 rows=46,302 width=8) (actual time=0.000..5.520 rows=40,359 loops=1)

22. 0.009 0.195 ↓ 62.0 62 1

Hash (cost=13.52..13.52 rows=1 width=21) (actual time=0.195..0.195 rows=62 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
23. 0.021 0.186 ↓ 62.0 62 1

Hash Join (cost=11.18..13.52 rows=1 width=21) (actual time=0.167..0.186 rows=62 loops=1)

  • Hash Cond: (npta.attribute_names_id = an.id)
24. 0.087 0.087 ↑ 1.0 106 1

Seq Scan on network_point_types_attributes npta (cost=0.00..2.06 rows=106 width=8) (actual time=0.082..0.087 rows=106 loops=1)

25. 0.003 0.078 ↑ 1.2 5 1

Hash (cost=11.10..11.10 rows=6 width=13) (actual time=0.078..0.078 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
26. 0.075 0.075 ↑ 1.2 5 1

Seq Scan on attribute_names an (cost=0.00..11.10 rows=6 width=13) (actual time=0.013..0.075 rows=5 loops=1)

  • Filter: ((name)::text = ANY ('{"Splitter Configuration","Number of Ports",ID,Name,Location,"Structure Id"}'::text[]))
  • Rows Removed by Filter: 458
27. 456.660 456.660 ↓ 1.1 11 91,332

Index Scan using network_point_attributes_network_points_id_idx on network_point_attributes npa (cost=0.43..3.52 rows=10 width=19) (actual time=0.002..0.005 rows=11 loops=91,332)

  • Index Cond: (network_points_id = np_1.id)
28. 7.071 3,786.248 ↑ 2.5 40,359 1

Hash Left Join (cost=10,716.36..24,367.13 rows=99,541 width=1,244) (actual time=3,732.934..3,786.248 rows=40,359 loops=1)

  • Hash Cond: (pts.id = gpon.point_id)
29. 16.695 3,776.706 ↓ 2.3 40,359 1

Hash Left Join (cost=10,607.82..20,734.38 rows=17,903 width=1,212) (actual time=3,730.446..3,776.706 rows=40,359 loops=1)

  • Hash Cond: (pts.id = point_attr.network_points_id)
30. 5.519 3,750.595 ↓ 2.6 40,359 1

Hash Left Join (cost=10,600.28..20,065.50 rows=15,434 width=1,180) (actual time=3,721.013..3,750.595 rows=40,359 loops=1)

  • Hash Cond: (npr.parent_id = parent_attr.network_points_id)
31. 17.536 2,988.129 ↓ 2.6 40,359 1

Hash Right Join (cost=10,592.74..19,575.41 rows=15,434 width=1,148) (actual time=2,964.040..2,988.129 rows=40,359 loops=1)

  • Hash Cond: (ppts.id = npr.parent_id)
32. 6.620 6.620 ↑ 1.1 40,359 1

CTE Scan on less_network_points ppts (cost=0.00..926.04 rows=46,302 width=556) (actual time=0.017..6.620 rows=40,359 loops=1)

33. 7.376 2,963.973 ↓ 2.6 40,359 1

Hash (cost=9,223.81..9,223.81 rows=15,434 width=596) (actual time=2,963.973..2,963.973 rows=40,359 loops=1)

  • Buckets: 8192 Batches: 4 Memory Usage: 3214kB
34. 26.035 2,956.597 ↓ 2.6 40,359 1

Hash Left Join (cost=5,029.80..9,223.81 rows=15,434 width=596) (actual time=1,142.664..2,956.597 rows=40,359 loops=1)

  • Hash Cond: (pts.id = npr.child_id)
35. 2,875.980 2,875.980 ↓ 2.6 40,359 1

CTE Scan on less_network_points pts (cost=0.00..1,041.80 rows=15,434 width=592) (actual time=1,086.683..2,875.980 rows=40,359 loops=1)

  • Filter: (id > 1000)
36. 28.202 54.582 ↑ 1.0 158,080 1

Hash (cost=2,435.80..2,435.80 rows=158,080 width=8) (actual time=54.582..54.582 rows=158,080 loops=1)

  • Buckets: 131072 Batches: 4 Memory Usage: 2572kB
37. 26.380 26.380 ↑ 1.0 158,080 1

Seq Scan on network_point_relation npr (cost=0.00..2,435.80 rows=158,080 width=8) (actual time=0.009..26.380 rows=158,080 loops=1)

38. 6.099 756.947 ↓ 174.0 40,359 1

Hash (cost=4.64..4.64 rows=232 width=36) (actual time=756.947..756.947 rows=40,359 loops=1)

  • Buckets: 65536 (originally 1024) Batches: 1 (originally 1) Memory Usage: 4026kB
39. 750.848 750.848 ↓ 174.0 40,359 1

CTE Scan on attr parent_attr (cost=0.00..4.64 rows=232 width=36) (actual time=667.658..750.848 rows=40,359 loops=1)

40. 4.873 9.416 ↓ 174.0 40,359 1

Hash (cost=4.64..4.64 rows=232 width=36) (actual time=9.416..9.416 rows=40,359 loops=1)

  • Buckets: 65536 (originally 1024) Batches: 1 (originally 1) Memory Usage: 4026kB
41. 4.543 4.543 ↓ 174.0 40,359 1

CTE Scan on attr point_attr (cost=0.00..4.64 rows=232 width=36) (actual time=0.018..4.543 rows=40,359 loops=1)

42. 0.172 2.471 ↑ 1.0 1,112 1

Hash (cost=94.64..94.64 rows=1,112 width=36) (actual time=2.471..2.471 rows=1,112 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 138kB
43. 0.097 2.299 ↑ 1.0 1,112 1

Subquery Scan on gpon (cost=0.28..94.64 rows=1,112 width=36) (actual time=0.048..2.299 rows=1,112 loops=1)

44. 1.805 2.202 ↑ 1.0 1,112 1

GroupAggregate (cost=0.28..83.52 rows=1,112 width=40) (actual time=0.047..2.202 rows=1,112 loops=1)

  • Group Key: gp.id
45. 0.397 0.397 ↑ 1.0 1,112 1

Index Scan using gpon_circuits_pkey on gpon_circuits gp (cost=0.28..64.06 rows=1,112 width=48) (actual time=0.031..0.397 rows=1,112 loops=1)

46. 81.042 2,507.472 ↓ 1.8 6,012 1

Hash (cost=66,184.36..66,184.36 rows=3,253 width=36) (actual time=2,507.472..2,507.472 rows=6,012 loops=1)

  • Buckets: 4096 (originally 4096) Batches: 64 (originally 1) Memory Usage: 4529kB
47. 1.124 2,426.430 ↓ 1.8 6,012 1

Subquery Scan on conn_pts (cost=61,728.98..66,184.36 rows=3,253 width=36) (actual time=272.993..2,426.430 rows=6,012 loops=1)

48. 2,093.874 2,425.306 ↓ 1.8 6,012 1

GroupAggregate (cost=61,728.98..66,151.83 rows=3,253 width=36) (actual time=272.991..2,425.306 rows=6,012 loops=1)

  • Group Key: conppts.points_id
49. 220.908 331.432 ↑ 1.0 438,219 1

Sort (cost=61,728.98..62,824.53 rows=438,219 width=39) (actual time=272.959..331.432 rows=438,219 loops=1)

  • Sort Key: conppts.points_id
  • Sort Method: external merge Disk: 12728kB
50. 76.712 110.524 ↑ 1.0 438,219 1

Hash Left Join (cost=36.29..8,681.03 rows=438,219 width=39) (actual time=0.319..110.524 rows=438,219 loops=1)

  • Hash Cond: (conppts.circuit_id = cc.id)
51. 33.509 33.509 ↑ 1.0 438,219 1

Seq Scan on connection_points conppts (cost=0.00..7,494.19 rows=438,219 width=28) (actual time=0.010..33.509 rows=438,219 loops=1)

52. 0.158 0.303 ↑ 1.0 1,213 1

Hash (cost=21.13..21.13 rows=1,213 width=15) (actual time=0.303..0.303 rows=1,213 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 75kB
53. 0.145 0.145 ↑ 1.0 1,213 1

Seq Scan on circuits cc (cost=0.00..21.13 rows=1,213 width=15) (actual time=0.008..0.145 rows=1,213 loops=1)

Planning time : 2.480 ms
Execution time : 6,657.124 ms