explain.depesz.com

PostgreSQL's explain analyze made readable

Result: pdbr

Settings
# exclusive inclusive rows x rows loops node
1. 0.006 52,043.928 ↑ 1.0 1 1

Aggregate (cost=47,463.61..47,463.62 rows=1 width=32) (actual time=52,043.928..52,043.928 rows=1 loops=1)

2.          

CTE zonages

3. 7.849 1,943.667 ↑ 2.6 4,565 1

Hash Join (cost=4,414.13..44,643.82 rows=11,889 width=12,923) (actual time=32.031..1,943.667 rows=4,565 loops=1)

  • Hash Cond: (z.idcontact = h.idcontact)
4. 4.055 1,924.643 ↓ 1.5 4,565 1

Nested Loop (cost=2,881.66..24,065.69 rows=3,141 width=12,902) (actual time=20.795..1,924.643 rows=4,565 loops=1)

5. 6.929 1,906.689 ↓ 1.3 4,633 1

Nested Loop (cost=2,881.37..22,713.85 rows=3,444 width=12,902) (actual time=20.415..1,906.689 rows=4,633 loops=1)

6. 1.348 247.067 ↓ 2.3 1,603 1

Hash Join (cost=2,881.09..10,523.51 rows=709 width=12,898) (actual time=20.154..247.067 rows=1,603 loops=1)

  • Hash Cond: (z.idouvrage = o.idouvrage)
7. 225.783 226.459 ↓ 1.8 2,563 1

Bitmap Heap Scan on zone z (cost=40.43..7,679.19 rows=1,392 width=12,898) (actual time=0.827..226.459 rows=2,563 loops=1)

  • Recheck Cond: (idouvrage < 1000)
  • Filter: (st_isempty(geometrie) IS FALSE)
  • Heap Blocks: exact=1473
8. 0.676 0.676 ↓ 1.2 2,563 1

Bitmap Index Scan on idx_zone_idouvrage (cost=0.00..40.08 rows=2,087 width=0) (actual time=0.676..0.676 rows=2,563 loops=1)

  • Index Cond: (idouvrage < 1000)
9. 5.464 19.260 ↑ 1.0 45,733 1

Hash (cost=2,267.59..2,267.59 rows=45,846 width=4) (actual time=19.259..19.260 rows=45,733 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 2120kB
10. 13.796 13.796 ↑ 1.0 45,733 1

Seq Scan on ouvrage o (cost=0.00..2,267.59 rows=45,846 width=4) (actual time=0.007..13.796 rows=45,733 loops=1)

  • Filter: (statut = ANY ('{1,2}'::integer[]))
  • Rows Removed by Filter: 44314
11. 1,652.693 1,652.693 ↓ 3.0 3 1,603

Index Scan using idx_commune_geometry_spatial on commune_geometry acg (cost=0.28..17.18 rows=1 width=3,980) (actual time=0.686..1.031 rows=3 loops=1,603)

  • Index Cond: (geom && z.geometrie)
  • Filter: _st_intersects(geom, z.geometrie)
  • Rows Removed by Filter: 1
12. 13.899 13.899 ↑ 1.0 1 4,633

Index Scan using pk_commune on commune c (cost=0.29..0.39 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=4,633)

  • Index Cond: (idcommune = acg.idcommune)
  • Filter: statut
  • Rows Removed by Filter: 0
13. 5.430 11.175 ↑ 1.0 48,110 1

Hash (cost=931.10..931.10 rows=48,110 width=8) (actual time=11.175..11.175 rows=48,110 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 2392kB
14. 5.745 5.745 ↑ 1.0 48,110 1

Seq Scan on tmphasheddata h (cost=0.00..931.10 rows=48,110 width=8) (actual time=0.006..5.745 rows=48,110 loops=1)

15.          

CTE zonage_max

16. 0.003 0.018 ↑ 1.0 1 1

Result (cost=1.14..1.15 rows=1 width=4) (actual time=0.017..0.018 rows=1 loops=1)

17.          

Initplan (forResult)

18. 0.001 0.015 ↑ 1.0 1 1

Limit (cost=0.42..1.14 rows=1 width=4) (actual time=0.014..0.015 rows=1 loops=1)

19. 0.014 0.014 ↑ 558,462.0 1 1

Index Only Scan Backward using pk_zone on zone (cost=0.42..397,515.41 rows=558,462 width=4) (actual time=0.014..0.014 rows=1 loops=1)

  • Index Cond: (idzone IS NOT NULL)
  • Heap Fetches: 1
20.          

CTE zonage_a_regrouper

21. 1.317 2.885 ↓ 1.5 1,839 1

HashAggregate (cost=356.67..368.56 rows=1,189 width=20) (actual time=2.596..2.885 rows=1,839 loops=1)

  • Group Key: zonages.idcommune, zonages.idcontactgu, zonages.idouvragegu
22. 1.568 1.568 ↑ 2.6 4,565 1

CTE Scan on zonages (cost=0.00..237.78 rows=11,889 width=12) (actual time=0.024..1.568 rows=4,565 loops=1)

23.          

CTE geometry_a_grouper

24. 4.506 1,969.416 ↓ 1,279.0 1,279 1

GroupAggregate (cost=1,086.35..1,209.23 rows=1 width=76) (actual time=1,962.974..1,969.416 rows=1,279 loops=1)

  • Group Key: z_1.idcommune, z_1.idcontactgu, z_1.idouvragegu
25. 1.448 1,964.910 ↓ 4,005.0 4,005 1

Merge Join (cost=1,086.35..1,209.21 rows=1 width=48) (actual time=1,962.950..1,964.910 rows=4,005 loops=1)

  • Merge Cond: ((z_1.idcommune = zonage_a_regrouper.idcommune) AND (z_1.idcontactgu = zonage_a_regrouper.idcontactgu) AND (z_1.idouvragegu = zonage_a_regrouper.idouvragegu))
26. 2.612 1,959.537 ↑ 2.6 4,565 1

Sort (cost=1,042.51..1,072.23 rows=11,889 width=48) (actual time=1,959.231..1,959.537 rows=4,565 loops=1)

  • Sort Key: z_1.idcommune, z_1.idcontactgu, z_1.idouvragegu
  • Sort Method: quicksort Memory: 675kB
27. 1,956.925 1,956.925 ↑ 2.6 4,565 1

CTE Scan on zonages z_1 (cost=0.00..237.78 rows=11,889 width=48) (actual time=32.034..1,956.925 rows=4,565 loops=1)

28. 0.504 3.925 ↓ 10.1 4,003 1

Sort (cost=43.84..44.83 rows=396 width=12) (actual time=3.713..3.925 rows=4,003 loops=1)

  • Sort Key: zonage_a_regrouper.idcommune, zonage_a_regrouper.idcontactgu, zonage_a_regrouper.idouvragegu
  • Sort Method: quicksort Memory: 108kB
29. 3.421 3.421 ↓ 3.2 1,279 1

CTE Scan on zonage_a_regrouper (cost=0.00..26.75 rows=396 width=12) (actual time=2.598..3.421 rows=1,279 loops=1)

  • Filter: (nb > 1)
  • Rows Removed by Filter: 560
30.          

CTE geometry_a_regrouper

31. 0.419 1,970.907 ↓ 1,177.0 1,177 1

WindowAgg (cost=0.02..0.06 rows=1 width=40) (actual time=1,970.252..1,970.907 rows=1,177 loops=1)

32. 0.686 1,970.488 ↓ 1,177.0 1,177 1

HashAggregate (cost=0.02..0.03 rows=1 width=32) (actual time=1,970.248..1,970.488 rows=1,177 loops=1)

  • Group Key: geometry_a_grouper.regroupement
33. 1,969.802 1,969.802 ↓ 1,279.0 1,279 1

CTE Scan on geometry_a_grouper (cost=0.00..0.02 rows=1 width=32) (actual time=1,962.975..1,969.802 rows=1,279 loops=1)

34.          

CTE regroupement_des_geometry

35. 46,920.872 48,914.037 ↓ 11.8 1,177 1

GroupAggregate (cost=837.10..839.60 rows=100 width=72) (actual time=1,989.198..48,914.037 rows=1,177 loops=1)

  • Group Key: gar.idregroupement, gar.regroupement
36. 13.257 1,993.165 ↓ 37.7 3,767 1

Sort (cost=837.10..837.35 rows=100 width=12,915) (actual time=1,988.768..1,993.165 rows=3,767 loops=1)

  • Sort Key: gar.idregroupement, gar.regroupement
  • Sort Method: external sort Disk: 3864kB
37. 0.260 1,979.908 ↓ 37.7 3,767 1

Nested Loop (cost=0.42..833.78 rows=100 width=12,915) (actual time=1,970.655..1,979.908 rows=3,767 loops=1)

38. 0.917 1,972.114 ↓ 37.7 3,767 1

ProjectSet (cost=0.00..0.53 rows=100 width=44) (actual time=1,970.257..1,972.114 rows=3,767 loops=1)

39. 1,971.197 1,971.197 ↓ 1,177.0 1,177 1

CTE Scan on geometry_a_regrouper gar (cost=0.00..0.02 rows=1 width=40) (actual time=1,970.253..1,971.197 rows=1,177 loops=1)

40. 7.534 7.534 ↑ 1.0 1 3,767

Index Scan using pk_zone on zone z_2 (cost=0.42..8.32 rows=1 width=12,879) (actual time=0.002..0.002 rows=1 loops=3,767)

  • Index Cond: (idzone = (unnest(gar.regroupement)))
41.          

CTE zone_avec_regroupement

42. 29.501 49,088.392 ↓ 1,279.0 1,279 1

WindowAgg (cost=0.03..2.46 rows=1 width=84) (actual time=1,989.759..49,088.392 rows=1,279 loops=1)

43. 1.718 49,058.891 ↓ 1,279.0 1,279 1

Nested Loop (cost=0.03..2.45 rows=1 width=80) (actual time=1,989.755..49,058.891 rows=1,279 loops=1)

44. 5.194 49,055.894 ↓ 1,279.0 1,279 1

Hash Join (cost=0.03..2.42 rows=1 width=76) (actual time=1,989.735..49,055.894 rows=1,279 loops=1)

  • Hash Cond: (regroupement_des_geometry.regroupement = geometry_a_grouper_1.regroupement)
45. 49,050.188 49,050.188 ↓ 11.8 1,177 1

CTE Scan on regroupement_des_geometry (cost=0.00..2.00 rows=100 width=64) (actual time=1,989.200..49,050.188 rows=1,177 loops=1)

46. 0.297 0.512 ↓ 1,279.0 1,279 1

Hash (cost=0.02..0.02 rows=1 width=76) (actual time=0.512..0.512 rows=1,279 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 134kB
47. 0.215 0.215 ↓ 1,279.0 1,279 1

CTE Scan on geometry_a_grouper geometry_a_grouper_1 (cost=0.00..0.02 rows=1 width=76) (actual time=0.002..0.215 rows=1,279 loops=1)

48. 1.279 1.279 ↑ 1.0 1 1,279

CTE Scan on zonage_max (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1,279)

49.          

CTE zone_sans_regroupement

50. 0.691 2.682 ↓ 560.0 560 1

Hash Join (cost=26.86..398.40 rows=1 width=80) (actual time=0.337..2.682 rows=560 loops=1)

  • Hash Cond: ((zonages_1.idcommune = zag.idcommune) AND (zonages_1.idcontactgu = zag.idcontactgu) AND (zonages_1.idouvragegu = zag.idouvragegu))
51. 1.703 1.703 ↑ 2.6 4,565 1

CTE Scan on zonages zonages_1 (cost=0.00..237.78 rows=11,889 width=80) (actual time=0.007..1.703 rows=4,565 loops=1)

52. 0.072 0.288 ↓ 93.3 560 1

Hash (cost=26.75..26.75 rows=6 width=12) (actual time=0.288..0.288 rows=560 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 33kB
53. 0.216 0.216 ↓ 93.3 560 1

CTE Scan on zonage_a_regrouper zag (cost=0.00..26.75 rows=6 width=12) (actual time=0.003..0.216 rows=560 loops=1)

  • Filter: (nb = 1)
  • Rows Removed by Filter: 1279
54.          

CTE zonages_finaux

55. 0.418 49,210.621 ↓ 919.5 1,839 1

Append (cost=0.00..0.06 rows=2 width=84) (actual time=1,989.762..49,210.621 rows=1,839 loops=1)

56. 49,207.130 49,207.130 ↓ 1,279.0 1,279 1

CTE Scan on zone_avec_regroupement (cost=0.00..0.02 rows=1 width=84) (actual time=1,989.761..49,207.130 rows=1,279 loops=1)

57. 0.065 3.073 ↓ 560.0 560 1

Subquery Scan on *SELECT* 2 (cost=0.00..0.03 rows=1 width=84) (actual time=0.340..3.073 rows=560 loops=1)

58. 3.008 3.008 ↓ 560.0 560 1

CTE Scan on zone_sans_regroupement (cost=0.00..0.02 rows=1 width=80) (actual time=0.339..3.008 rows=560 loops=1)

59.          

CTE insertion_geometries

60. 2,367.105 51,892.041 ↓ 814.0 1,628 1

Insert on zonage_gu_geometry_tmp (cost=0.05..0.09 rows=2 width=36) (actual time=49,453.122..51,892.041 rows=1,628 loops=1)

61. 0.533 49,524.936 ↓ 814.0 1,628 1

Subquery Scan on *SELECT* (cost=0.05..0.09 rows=2 width=36) (actual time=49,451.760..49,524.936 rows=1,628 loops=1)

62. 20.144 49,524.403 ↓ 814.0 1,628 1

Unique (cost=0.05..0.07 rows=2 width=40) (actual time=49,451.759..49,524.403 rows=1,628 loops=1)

63. 176.467 49,504.259 ↓ 919.5 1,839 1

Sort (cost=0.05..0.06 rows=2 width=40) (actual time=49,451.758..49,504.259 rows=1,839 loops=1)

  • Sort Key: zonages_finaux.idzonagegugeometry, zonages_finaux.geom
  • Sort Method: external merge Disk: 100848kB
64. 49,327.792 49,327.792 ↓ 919.5 1,839 1

CTE Scan on zonages_finaux (cost=0.00..0.04 rows=2 width=40) (actual time=1,989.764..49,327.792 rows=1,839 loops=1)

65.          

CTE insertion_zonages

66. 9.297 33.316 ↓ 919.5 1,839 1

Insert on zonage_gu_tmp (cost=0.00..0.04 rows=2 width=48) (actual time=0.152..33.316 rows=1,839 loops=1)

67. 24.019 24.019 ↓ 919.5 1,839 1

CTE Scan on zonages_finaux zonages_finaux_1 (cost=0.00..0.04 rows=2 width=48) (actual time=0.016..24.019 rows=1,839 loops=1)

68. 0.003 52,043.922 ↑ 1.0 2 1

Append (cost=0.04..0.13 rows=2 width=8) (actual time=52,009.875..52,043.922 rows=2 loops=1)

69. 0.515 52,009.875 ↑ 1.0 1 1

Aggregate (cost=0.04..0.06 rows=1 width=8) (actual time=52,009.875..52,009.875 rows=1 loops=1)

70. 52,009.360 52,009.360 ↓ 814.0 1,628 1

CTE Scan on insertion_geometries (cost=0.00..0.04 rows=2 width=0) (actual time=49,453.125..52,009.360 rows=1,628 loops=1)

71. 0.150 34.044 ↑ 1.0 1 1

Aggregate (cost=0.04..0.06 rows=1 width=8) (actual time=34.044..34.044 rows=1 loops=1)

72. 33.894 33.894 ↓ 919.5 1,839 1

CTE Scan on insertion_zonages (cost=0.00..0.04 rows=2 width=0) (actual time=0.154..33.894 rows=1,839 loops=1)

Planning time : 2.636 ms
Execution time : 52,103.461 ms