explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ojHp

Settings
# exclusive inclusive rows x rows loops node
1. 1.137 3,806.798 ↑ 200.0 1 1

GroupAggregate (cost=1,034,256.31..1,039,601.17 rows=200 width=664) (actual time=3,806.797..3,806.798 rows=1 loops=1)

  • Group Key: cp.building_id, cp.status, cp.company_id, cp.manager_id
2.          

CTE grouped_natures

3. 0.000 0.000 ↓ 0.0 0

ProjectSet (cost=3.32..12.68 rows=1,800 width=8) (never executed)

4. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=3.32..3.54 rows=18 width=36) (never executed)

  • Group Key: COALESCE(ref_property_nature.parent_id, ref_property_nature.id)
5. 0.000 0.000 ↓ 0.0 0

Seq Scan on ref_property_nature (cost=0.00..3.23 rows=18 width=8) (never executed)

  • Filter: (order_in_owner_realestate IS NOT NULL)
6.          

CTE customer_patrimony

7. 0.035 3,804.990 ↑ 236.5 4 1

HashAggregate (cost=1,002,247.90..1,002,257.36 rows=946 width=388) (actual time=3,804.977..3,804.990 rows=4 loops=1)

  • Group Key: "*SELECT* 1".manager_id, "*SELECT* 1".firstname, "*SELECT* 1".lastname, "*SELECT* 1".company_id, "*SELECT* 1".ref_naf_code_id, "*SELECT* 1".birthdate, "*SELECT* 1".building_id, "*SELECT* 1".position_x, "*SELECT* 1".position_y, "*SELECT* 1".longitude, "*SELECT* 1".latitude, "*SELECT* 1".status, "*SELECT* 1".raison_sociale, "*SELECT* 1".address, "*SELECT* 1".gis_department_id, (NULL::date), "*SELECT* 1".ref_property_nature_id, (NULL::text), "*SELECT* 1".surface, (NULL::character varying), (NULL::integer), (NULL::date), (NULL::date)
8. 0.004 3,804.955 ↑ 236.5 4 1

Append (cost=444,102.21..1,002,193.50 rows=946 width=388) (actual time=3,804.863..3,804.955 rows=4 loops=1)

9. 0.001 0.087 ↓ 0.0 0 1

Subquery Scan on *SELECT* 1 (cost=444,102.21..444,721.91 rows=846 width=388) (actual time=0.086..0.087 rows=0 loops=1)

10. 0.001 0.086 ↓ 0.0 0 1

GroupAggregate (cost=444,102.21..444,713.45 rows=846 width=392) (actual time=0.086..0.086 rows=0 loops=1)

  • Group Key: gp.building_id, cm.company_id, gp.ref_property_nature_id
11. 0.008 0.085 ↓ 0.0 0 1

Sort (cost=444,102.21..444,104.33 rows=846 width=215) (actual time=0.085..0.085 rows=0 loops=1)

  • Sort Key: gp.building_id, cm.company_id, gp.ref_property_nature_id
  • Sort Method: quicksort Memory: 25kB
12. 0.000 0.077 ↓ 0.0 0 1

Nested Loop Left Join (cost=151.00..444,061.08 rows=846 width=215) (actual time=0.077..0.077 rows=0 loops=1)

  • Join Filter: (gn.child_id = gp.ref_property_nature_id)
13. 0.001 0.077 ↓ 0.0 0 1

Nested Loop Left Join (cost=151.00..440,236.08 rows=94 width=211) (actual time=0.077..0.077 rows=0 loops=1)

14. 0.000 0.076 ↓ 0.0 0 1

Nested Loop Left Join (cost=144.30..439,228.42 rows=94 width=199) (actual time=0.076..0.076 rows=0 loops=1)

15. 0.004 0.076 ↓ 0.0 0 1

Nested Loop (cost=143.74..438,662.42 rows=94 width=109) (actual time=0.076..0.076 rows=0 loops=1)

16. 0.036 0.036 ↑ 17.0 1 1

Index Scan using company_manager_manager_id on company_manager cm (cost=0.43..13.35 rows=17 width=31) (actual time=0.035..0.036 rows=1 loops=1)

  • Index Cond: (manager_id = ANY ('{5555,4785}'::integer[]))
  • Filter: (is_ref_job_position_active AND (ref_job_position_id <> ALL ('{69,70,77,78,79,82}'::integer[])))
17. 0.003 0.036 ↓ 0.0 0 1

Bitmap Heap Scan on gen_properties gp (cost=143.31..25,729.56 rows=7,333 width=82) (actual time=0.036..0.036 rows=0 loops=1)

  • Recheck Cond: ((cm.company_id = head_group_company_id) OR (cm.company_id = company_id))
  • Filter: (building_id IS NOT NULL)
18. 0.002 0.033 ↓ 0.0 0 1

BitmapOr (cost=143.31..143.31 rows=7,334 width=0) (actual time=0.033..0.033 rows=0 loops=1)

19. 0.010 0.010 ↓ 0.0 0 1

Bitmap Index Scan on gen_properties_head_group_company_id (cost=0.00..120.74 rows=6,472 width=0) (actual time=0.010..0.010 rows=0 loops=1)

  • Index Cond: (cm.company_id = head_group_company_id)
20. 0.021 0.021 ↓ 0.0 0 1

Bitmap Index Scan on gen_properties_company_id (cost=0.00..18.90 rows=862 width=0) (actual time=0.021..0.021 rows=0 loops=1)

  • Index Cond: (cm.company_id = company_id)
21. 0.000 0.000 ↓ 0.0 0

Index Scan using gen_building_building_id on gen_building gb (cost=0.56..6.01 rows=1 width=94) (never executed)

  • Index Cond: (building_id = gp.building_id)
22. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on gen_implantation_search gis (cost=6.69..10.71 rows=1 width=24) (never executed)

  • Recheck Cond: ((company_id = gp.company_id) AND (building_id = gp.building_id))
23. 0.000 0.000 ↓ 0.0 0

BitmapAnd (cost=6.69..6.69 rows=1 width=0) (never executed)

24. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on gen_implantation_search_company_id (cost=0.00..3.18 rows=9 width=0) (never executed)

  • Index Cond: (company_id = gp.company_id)
25. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on gen_implantation_search_building_id (cost=0.00..3.26 rows=19 width=0) (never executed)

  • Index Cond: (building_id = gp.building_id)
26. 0.000 0.000 ↓ 0.0 0

CTE Scan on grouped_natures gn (cost=0.00..36.00 rows=1,800 width=8) (never executed)

27. 0.000 0.046 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2 (cost=70.35..15,072.95 rows=53 width=344) (actual time=0.046..0.046 rows=0 loops=1)

28. 0.001 0.046 ↓ 0.0 0 1

Hash Left Join (cost=70.35..15,072.29 rows=53 width=245) (actual time=0.046..0.046 rows=0 loops=1)

  • Hash Cond: (gis_1.ref_property_nature_id = gn_1.child_id)
29. 0.000 0.045 ↓ 0.0 0 1

Nested Loop Left Join (cost=11.85..15,005.36 rows=19 width=201) (actual time=0.045..0.045 rows=0 loops=1)

30. 0.000 0.045 ↓ 0.0 0 1

Nested Loop Left Join (cost=11.42..14,976.84 rows=19 width=201) (actual time=0.045..0.045 rows=0 loops=1)

31. 0.000 0.045 ↓ 0.0 0 1

Nested Loop Left Join (cost=10.99..14,948.45 rows=19 width=197) (actual time=0.045..0.045 rows=0 loops=1)

  • Join Filter: ((rtei.id)::text = gis_1.ref_tranche_effectif_id)
32. 0.003 0.045 ↓ 0.0 0 1

Nested Loop (cost=10.99..14,940.97 rows=19 width=190) (actual time=0.044..0.045 rows=0 loops=1)

33. 0.014 0.014 ↑ 17.0 1 1

Index Scan using company_manager_manager_id on company_manager cm_1 (cost=0.43..13.35 rows=17 width=31) (actual time=0.013..0.014 rows=1 loops=1)

  • Index Cond: (manager_id = ANY ('{5555,4785}'::integer[]))
  • Filter: (is_ref_job_position_active AND (ref_job_position_id <> ALL ('{69,70,77,78,79,82}'::integer[])))
34. 0.007 0.028 ↓ 0.0 0 1

Bitmap Heap Scan on gen_implantation_search gis_1 (cost=10.55..877.31 rows=78 width=163) (actual time=0.028..0.028 rows=0 loops=1)

  • Recheck Cond: ((cm_1.company_id = head_group_company_id) OR (cm_1.company_id = company_id))
  • Filter: ((occupier_type)::text <> 'domiciliation'::text)
  • Rows Removed by Filter: 1
  • Heap Blocks: exact=1
35. 0.002 0.021 ↓ 0.0 0 1

BitmapOr (cost=10.55..10.55 rows=218 width=0) (actual time=0.020..0.021 rows=0 loops=1)

36. 0.010 0.010 ↓ 0.0 0 1

Bitmap Index Scan on gen_implantation_search_head_group_company_id (cost=0.00..6.01 rows=210 width=0) (actual time=0.010..0.010 rows=0 loops=1)

  • Index Cond: (cm_1.company_id = head_group_company_id)
37. 0.009 0.009 ↑ 9.0 1 1

Bitmap Index Scan on gen_implantation_search_company_id (cost=0.00..4.50 rows=9 width=0) (actual time=0.009..0.009 rows=1 loops=1)

  • Index Cond: (cm_1.company_id = company_id)
38. 0.000 0.000 ↓ 0.0 0

Materialize (cost=0.00..1.54 rows=18 width=13) (never executed)

39. 0.000 0.000 ↓ 0.0 0

Seq Scan on ref_tranche_effectif_i18n rtei (cost=0.00..1.45 rows=18 width=13) (never executed)

  • Filter: ((culture)::text = 'fr_FR'::text)
40. 0.000 0.000 ↓ 0.0 0

Index Scan using establishment_occupied_surface_estimation_pkey on establishment_occupied_surface_estimation surf (cost=0.43..1.49 rows=1 width=12) (never executed)

  • Index Cond: (gis_1.establishment_id = establishment_id)
41. 0.000 0.000 ↓ 0.0 0

Index Scan using establishment_effectif_estimation_pkey on establishment_effectif_estimation estimation_effectifs (cost=0.43..1.49 rows=1 width=8) (never executed)

  • Index Cond: (establishment_id = gis_1.establishment_id)
42. 0.000 0.000 ↓ 0.0 0

Hash (cost=36.00..36.00 rows=1,800 width=8) (never executed)

43. 0.000 0.000 ↓ 0.0 0

CTE Scan on grouped_natures gn_1 (cost=0.00..36.00 rows=1,800 width=8) (never executed)

44. 0.008 3,804.818 ↑ 11.8 4 1

Subquery Scan on *SELECT* 3 (cost=542,328.70..542,393.92 rows=47 width=388) (actual time=3,804.728..3,804.818 rows=4 loops=1)

45. 1.052 3,804.810 ↑ 11.8 4 1

GroupAggregate (cost=542,328.70..542,393.33 rows=47 width=334) (actual time=3,804.723..3,804.810 rows=4 loops=1)

  • Group Key: cm_2.company_id, cd.building_id, bcs.ref_dgi_cadastre_nature_id
  • Filter: (bool_or(gfs.constructed) IS FALSE)
46. 0.087 3,803.758 ↑ 11.8 8 1

Sort (cost=542,328.70..542,328.94 rows=94 width=165) (actual time=3,803.756..3,803.758 rows=8 loops=1)

  • Sort Key: cm_2.company_id, cd.building_id, bcs.ref_dgi_cadastre_nature_id
  • Sort Method: quicksort Memory: 27kB
47. 0.030 3,803.671 ↑ 11.8 8 1

Nested Loop (cost=15.58..542,325.62 rows=94 width=165) (actual time=222.324..3,803.671 rows=8 loops=1)

48. 0.036 3,803.473 ↑ 14.5 6 1

Nested Loop Left Join (cost=15.02..539,641.03 rows=87 width=167) (actual time=222.293..3,803.473 rows=6 loops=1)

49. 0.024 3,803.359 ↑ 14.5 6 1

Nested Loop (cost=14.45..539,007.78 rows=87 width=77) (actual time=222.270..3,803.359 rows=6 loops=1)

50. 0.080 3,803.251 ↑ 14.5 6 1

Nested Loop Left Join (cost=14.02..538,385.63 rows=87 width=69) (actual time=222.249..3,803.251 rows=6 loops=1)

51. 1,580.930 3,803.015 ↑ 14.5 6 1

Hash Join (cost=13.58..537,178.04 rows=87 width=35) (actual time=222.196..3,803.015 rows=6 loops=1)

  • Hash Cond: (bco.company_id = cm_2.company_id)
52. 2,222.060 2,222.060 ↓ 1.0 17,163,498 1

Seq Scan on building_cadastre_owner bco (cost=0.00..429,917.98 rows=17,159,298 width=8) (actual time=0.035..2,222.060 rows=17,163,498 loops=1)

53. 0.006 0.025 ↑ 18.0 1 1

Hash (cost=13.35..13.35 rows=18 width=31) (actual time=0.025..0.025 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
54. 0.019 0.019 ↑ 18.0 1 1

Index Scan using company_manager_manager_id on company_manager cm_2 (cost=0.43..13.35 rows=18 width=31) (actual time=0.018..0.019 rows=1 loops=1)

  • Index Cond: (manager_id = ANY ('{5555,4785}'::integer[]))
  • Filter: ((company_id IS NOT NULL) AND (ref_job_position_id <> ALL ('{69,70,77,78,79,82}'::integer[])))
55. 0.156 0.156 ↑ 3.0 1 6

Index Scan using gen_foncier_search_building_cadastre_id on gen_foncier_search gfs (cost=0.44..13.85 rows=3 width=38) (actual time=0.025..0.026 rows=1 loops=6)

  • Index Cond: (building_cadastre_id = bco.building_cadastre_id)
56. 0.084 0.084 ↑ 1.0 1 6

Index Scan using building_cadastre_pkey on building_cadastre cd (cost=0.44..7.14 rows=1 width=8) (actual time=0.014..0.014 rows=1 loops=6)

  • Index Cond: (id = bco.building_cadastre_id)
  • Filter: ((building_id IS NOT NULL) AND (deleted_at IS NULL))
57. 0.078 0.078 ↑ 1.0 1 6

Index Scan using gen_building_building_id on gen_building gb_1 (cost=0.56..7.27 rows=1 width=94) (actual time=0.013..0.013 rows=1 loops=6)

  • Index Cond: (building_id = cd.building_id)
58. 0.168 0.168 ↑ 1.0 1 6

Index Scan using index_building_cadastre_subdivision_building_cadastre_id on building_cadastre_subdivision bcs (cost=0.57..30.85 rows=1 width=10) (actual time=0.027..0.028 rows=1 loops=6)

  • Index Cond: (building_cadastre_id = bco.building_cadastre_id)
  • Filter: ((deleted_at IS NULL) AND record_visible)
  • Rows Removed by Filter: 7
59. 0.025 3,805.661 ↑ 236.5 4 1

Sort (cost=31,986.27..31,988.64 rows=946 width=493) (actual time=3,805.660..3,805.661 rows=4 loops=1)

  • Sort Key: cp.building_id, cp.status, cp.company_id, cp.manager_id
  • Sort Method: quicksort Memory: 27kB
60. 0.008 3,805.636 ↑ 236.5 4 1

Nested Loop Left Join (cost=48.49..31,939.51 rows=946 width=493) (actual time=3,805.571..3,805.636 rows=4 loops=1)

61. 0.018 3,805.568 ↑ 236.5 4 1

Hash Left Join (cost=47.77..23,680.42 rows=946 width=454) (actual time=3,805.523..3,805.568 rows=4 loops=1)

  • Hash Cond: (cp.ref_naf_code_id = (rnci.id)::text)
62. 0.030 3,805.159 ↑ 236.5 4 1

Hash Left Join (cost=3.25..23,633.40 rows=946 width=409) (actual time=3,805.117..3,805.159 rows=4 loops=1)

  • Hash Cond: (cp.ref_dgi_cadastre_nature_id = (rdcni.id)::text)
63. 0.006 3,805.080 ↑ 236.5 4 1

Nested Loop Left Join (cost=1.44..23,625.39 rows=946 width=429) (actual time=3,805.042..3,805.080 rows=4 loops=1)

64. 0.006 3,805.062 ↑ 236.5 4 1

Nested Loop Left Join (cost=1.29..23,453.48 rows=946 width=408) (actual time=3,805.027..3,805.062 rows=4 loops=1)

65. 0.008 3,805.032 ↑ 236.5 4 1

Nested Loop Left Join (cost=0.86..15,666.15 rows=946 width=404) (actual time=3,805.007..3,805.032 rows=4 loops=1)

66. 0.009 3,805.020 ↑ 236.5 4 1

Nested Loop Left Join (cost=0.43..7,792.44 rows=946 width=396) (actual time=3,804.998..3,805.020 rows=4 loops=1)

  • Join Filter: (cp.status <> 'Occupant'::text)
67. 3,805.003 3,805.003 ↑ 236.5 4 1

CTE Scan on customer_patrimony cp (cost=0.00..18.92 rows=946 width=388) (actual time=3,804.985..3,805.003 rows=4 loops=1)

68. 0.008 0.008 ↓ 0.0 0 4

Index Scan using building_nature_price_sale_pkey on building_nature_price_sale sale (cost=0.43..8.20 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=4)

  • Index Cond: ((building_id = cp.building_id) AND (ref_property_nature_id = cp.ref_property_nature_id))
69. 0.004 0.004 ↓ 0.0 0 4

Index Scan using building_nature_price_rent_pkey on building_nature_price_rent rent (cost=0.43..8.32 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=4)

  • Index Cond: ((building_id = cp.building_id) AND (ref_property_nature_id = cp.ref_property_nature_id))
70. 0.024 0.024 ↓ 0.0 0 4

Index Scan using gen_building_stats_segmentation_pkey on gen_building_stats_segmentation gbss (cost=0.43..8.23 rows=1 width=8) (actual time=0.006..0.006 rows=0 loops=4)

  • Index Cond: (building_id = cp.building_id)
71. 0.012 0.012 ↓ 0.0 0 4

Index Scan using ref_property_nature_i18n_pkey on ref_property_nature_i18n rpni (cost=0.14..0.18 rows=1 width=21) (actual time=0.003..0.003 rows=0 loops=4)

  • Index Cond: ((id = cp.ref_property_nature_id) AND ((culture)::text = 'fr_FR'::text))
72. 0.019 0.049 ↑ 1.0 31 1

Hash (cost=1.43..1.43 rows=31 width=15) (actual time=0.049..0.049 rows=31 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
73. 0.030 0.030 ↑ 1.0 31 1

Seq Scan on ref_dgi_cadastre_nature_i18n rdcni (cost=0.00..1.43 rows=31 width=15) (actual time=0.024..0.030 rows=31 loops=1)

  • Filter: ((culture)::text = 'fr_FR'::text)
  • Rows Removed by Filter: 3
74. 0.156 0.391 ↑ 1.0 734 1

Hash (cost=35.35..35.35 rows=734 width=51) (actual time=0.390..0.391 rows=734 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 70kB
75. 0.235 0.235 ↑ 1.0 734 1

Seq Scan on ref_naf_code_i18n rnci (cost=0.00..35.35 rows=734 width=51) (actual time=0.011..0.235 rows=734 loops=1)

  • Filter: ((culture)::text = 'fr_FR'::text)
  • Rows Removed by Filter: 734
76. 0.012 0.060 ↑ 1.0 1 4

Nested Loop Left Join (cost=0.71..8.72 rows=1 width=43) (actual time=0.014..0.015 rows=1 loops=4)

77. 0.028 0.028 ↑ 1.0 1 4

Index Scan using gen_company_last_data_pkey on gen_company_last_data gcld (cost=0.44..8.43 rows=1 width=8) (actual time=0.007..0.007 rows=1 loops=4)

  • Index Cond: (company_id = cp.company_id)
78. 0.020 0.020 ↑ 1.0 1 4

Index Scan using ref_company_legal_form_i18n_pkey on ref_company_legal_form_i18n rclfi (cost=0.28..0.30 rows=1 width=43) (actual time=0.005..0.005 rows=1 loops=4)

  • Index Cond: ((gcld.ref_company_legal_form_id = id) AND ((culture)::text = 'fr_FR'::text))