explain.depesz.com

PostgreSQL's explain analyze made readable

Result: deMi

Settings
# exclusive inclusive rows x rows loops node
1. 505.546 1,514,014.734 ↓ 9.0 9 1

GroupAggregate (cost=9.80..99,051.74 rows=1 width=580) (actual time=130,824.356..1,514,014.734 rows=9 loops=1)

  • Output: dw_interm_diaire_a_types_uh.lb_type, sum(CASE WHEN ((resalys_a_campagne.cd_camp)::text = '2019'::text) THEN ca.montant_ca_comptable_ht ELSE '0'::double precision END), sum(CASE WHEN (((resalys_a_campagne.cd_camp)::text = '2018'::text) AND (dw_int (...)
  • Group Key: dw_interm_diaire_a_types_uh.lb_type
2. 556.032 1,513,509.188 ↓ 334,546.0 334,546 1

Nested Loop (cost=9.80..99,051.69 rows=1 width=572) (actual time=29,813.011..1,513,509.188 rows=334,546 loops=1)

  • Output: dw_interm_diaire_a_types_uh.lb_type, resalys_a_campagne.cd_camp, ca.montant_ca_comptable_ht, dw_interm_diaire_a_r_servation.res_dt_ok, ca.montant_pickup_7j_ca_comptable_ht, ca.montant_pickup_7j_ca_comptable_ht_ann_1, dw_interm_diaire_a_type (...)
  • Inner Unique: true
  • Join Filter: ((resalys_a_campagne.cd_camp)::text = (ca.cd_camp)::text)
  • Rows Removed by Join Filter: 510469
3. 139,123.403 1,512,096.808 ↓ 428,174.0 428,174 1

Nested Loop (cost=9.80..99,050.57 rows=1 width=802) (actual time=596.756..1,512,096.808 rows=428,174 loops=1)

  • Output: ca.montant_ca_comptable_ht, ca.montant_pickup_7j_ca_comptable_ht, ca.montant_pickup_7j_ca_comptable_ht_ann_1, ca.cd_camp, dw_interm_diaire_a_types_uh.lb_type, dw_interm_diaire_a_types_uh.stock_locatif, dw_interm_diaire_a_types_uh.cd_c (...)
  • Join Filter: (ca.realisation_date = dw_interm_diaire_a_semaines_18.date_)
  • Rows Removed by Join Filter: 1572683102
4. 288,828.559 1,260,363.643 ↓ 428,174.0 428,174 1

Nested Loop (cost=9.80..98,945.91 rows=1 width=810) (actual time=596.596..1,260,363.643 rows=428,174 loops=1)

  • Output: ca.montant_ca_comptable_ht, ca.montant_pickup_7j_ca_comptable_ht, ca.montant_pickup_7j_ca_comptable_ht_ann_1, ca.cd_camp, ca.realisation_date, dw_interm_diaire_a_types_uh.lb_type, dw_interm_diaire_a_types_uh.stock_locatif, dw_in (...)
  • Inner Unique: true
  • Join Filter: (ca.realisation_date = date_conso.date_)
  • Rows Removed by Join Filter: 3565461048
5. 677.049 673,525.980 ↓ 428,174.0 428,174 1

Nested Loop (cost=9.80..98,460.44 rows=1 width=806) (actual time=594.985..673,525.980 rows=428,174 loops=1)

  • Output: ca.montant_ca_comptable_ht, ca.montant_pickup_7j_ca_comptable_ht, ca.montant_pickup_7j_ca_comptable_ht_ann_1, ca.cd_camp, ca.realisation_date, dw_interm_diaire_a_types_uh.lb_type, dw_interm_diaire_a_types_uh.stock_locatif, (...)
  • Inner Unique: true
6. 357,369.812 670,636.686 ↓ 442,449.0 442,449 1

Nested Loop Left Join (cost=9.38..98,459.99 rows=1 width=810) (actual time=594.649..670,636.686 rows=442,449 loops=1)

  • Output: ca.montant_ca_comptable_ht, ca.montant_pickup_7j_ca_comptable_ht, ca.montant_pickup_7j_ca_comptable_ht_ann_1, ca.cd_camp, ca.booking_id, ca.realisation_date, dw_interm_diaire_a_types_uh.lb_type, dw_interm_diaire_a_ty (...)
  • Inner Unique: true
  • Join Filter: ((ca.cl__uh)::text = (resalys_a_uh.cl__uh)::text)
  • Rows Removed by Join Filter: 3514874049
  • Filter: ((resalys_a_uh.lb_cme IS NULL) OR ((resalys_a_uh.lb_cme)::text = ANY ('{,Non,NON}'::text[])))
7. 24,529.581 38,948.494 ↓ 442,449.0 442,449 1

Nested Loop (cost=9.38..97,882.01 rows=1 width=948) (actual time=594.620..38,948.494 rows=442,449 loops=1)

  • Output: ca.montant_ca_comptable_ht, ca.montant_pickup_7j_ca_comptable_ht, ca.montant_pickup_7j_ca_comptable_ht_ann_1, ca.cd_camp, ca.cl__uh, ca.booking_id, ca.realisation_date, dw_interm_diaire_a_types_uh.lb_type, dw_i (...)
  • Join Filter: (((ca.champ)::text = (dw_interm_diaire_a_types_uh.cl__type_uh)::text) AND ((ca.cd_camp)::text = dw_interm_diaire_a_types_uh.cd_camp))
  • Rows Removed by Join Filter: 230364966
8. 6.559 6.559 ↓ 139.0 417 1

Index Scan using w_a_types_uh_i2 on public.w_a_types_uh dw_interm_diaire_a_types_uh (cost=0.28..62.11 rows=3 width=694) (actual time=0.072..6.559 rows=417 loops=1)

  • Output: dw_interm_diaire_a_types_uh.cl__type_uh, dw_interm_diaire_a_types_uh.cd_type, dw_interm_diaire_a_types_uh.etab_num, dw_interm_diaire_a_types_uh.lb_type, dw_interm_diaire_a_types_uh.fl_emplacement, dw_inte (...)
  • Filter: (((dw_interm_diaire_a_types_uh.lb_categ)::text = 'Location'::text) AND ((dw_interm_diaire_a_types_uh.lb_type)::text <> ALL ('{Parcelle Résident,Parcelle TO,Parcelles TO,Résident}'::text[])))
  • Rows Removed by Filter: 220
9. 11,466.107 14,412.354 ↓ 1,076.8 553,495 417

Materialize (cost=9.10..97,794.21 rows=514 width=530) (actual time=0.002..34.562 rows=553,495 loops=417)

  • Output: ca.montant_ca_comptable_ht, ca.montant_pickup_7j_ca_comptable_ht, ca.montant_pickup_7j_ca_comptable_ht_ann_1, ca.champ, ca.cd_camp, ca.cl__uh, ca.booking_id, ca.realisation_date
10. 263.767 2,946.247 ↓ 1,076.8 553,495 1

Hash Join (cost=9.10..97,791.64 rows=514 width=530) (actual time=0.571..2,946.247 rows=553,495 loops=1)

  • Output: ca.montant_ca_comptable_ht, ca.montant_pickup_7j_ca_comptable_ht, ca.montant_pickup_7j_ca_comptable_ht_ann_1, ca.champ, ca.cd_camp, ca.cl__uh, ca.booking_id, ca.realisation_date
  • Inner Unique: true
  • Hash Cond: ((ca.cd_produit)::text = (resalys_a_produit.cd_produit)::text)
11. 574.695 2,682.334 ↓ 25.6 1,014,838 1

Nested Loop (cost=0.43..97,677.62 rows=39,585 width=748) (actual time=0.405..2,682.334 rows=1,014,838 loops=1)

  • Output: ca.montant_ca_comptable_ht, ca.montant_pickup_7j_ca_comptable_ht, ca.montant_pickup_7j_ca_comptable_ht_ann_1, ca.cd_produit, ca.champ, ca.cd_camp, ca.cl__uh, ca.booking_id, ca.realisation_date
12. 0.032 0.032 ↑ 1.0 1 1

Seq Scan on public.w_a_etablissement dw_interm_diaire_a_etabliss_5 (cost=0.00..1.10 rows=1 width=8) (actual time=0.029..0.032 rows=1 loops=1)

  • Output: dw_interm_diaire_a_etabliss_5.etab_num, dw_interm_diaire_a_etabliss_5.etab_nom, dw_interm_diaire_a_etabliss_5.etab_analytic_cpt, dw_interm_diaire_a_etabliss_5.eta_nom, dw_interm_diaire_a (...)
  • Filter: ((dw_interm_diaire_a_etabliss_5.etab_nom)::text = 'Camping Bela Basque (Parme) ****'::text)
  • Rows Removed by Filter: 7
13. 2,107.607 2,107.607 ↓ 25.6 1,014,838 1

Index Scan using sresalys_f_ca_i6 on public.sresalys_f_ca ca (cost=0.43..97,280.67 rows=39,585 width=756) (actual time=0.373..2,107.607 rows=1,014,838 loops=1)

  • Output: ca.cl__uh, ca.champ, ca.cle_type_uh_comm, ca.cle_tarif, ca.cl__tarification, ca.num_sej, ca.num_tar, ca.num_for, ca.cd_camp, ca.destination_id, ca.booking_id, ca.num_soc, ca.customer_id, (...)
  • Index Cond: (ca.destination_id = dw_interm_diaire_a_etabliss_5.etab_num)
14. 0.006 0.146 ↑ 1.0 4 1

Hash (cost=8.62..8.62 rows=4 width=36) (actual time=0.145..0.146 rows=4 loops=1)

  • Output: resalys_a_produit.cd_produit
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
15. 0.140 0.140 ↑ 1.0 4 1

Seq Scan on public.sresalys_a_produit resalys_a_produit (cost=0.00..8.62 rows=4 width=36) (actual time=0.067..0.140 rows=4 loops=1)

  • Output: resalys_a_produit.cd_produit
  • Filter: ((resalys_a_produit.cd_produit)::text = ANY ('{CPG,LOC,PLOYER,PSUP}'::text[]))
  • Rows Removed by Filter: 304
16. 274,318.380 274,318.380 ↑ 1.7 7,945 442,449

Seq Scan on public.sresalys_a_uh resalys_a_uh (cost=0.00..358.23 rows=13,523 width=412) (actual time=0.001..0.620 rows=7,945 loops=442,449)

  • Output: resalys_a_uh.cl__uh, resalys_a_uh.num_cha, resalys_a_uh.cha_nom, resalys_a_uh.lb_type, resalys_a_uh.cd_categ, resalys_a_uh.lb_categ, resalys_a_uh.cha_etat, resalys_a_uh.cha_nb_lit, resalys_a_uh.cha_max_oc, resa (...)
17. 2,212.245 2,212.245 ↑ 1.0 1 442,449

Index Scan using w_a_r_servation_pkey on public.w_a_r_servation dw_interm_diaire_a_r_servation (cost=0.42..0.45 rows=1 width=12) (actual time=0.005..0.005 rows=1 loops=442,449)

  • Output: dw_interm_diaire_a_r_servation.num_res, dw_interm_diaire_a_r_servation.res_status, dw_interm_diaire_a_r_servation.res_status2, dw_interm_diaire_a_r_servation.mor_mod_re, dw_interm_diaire_a_r_servation.num_voucher, dw (...)
  • Index Cond: (dw_interm_diaire_a_r_servation.num_res = ca.booking_id)
  • Filter: (((dw_interm_diaire_a_r_servation.cd_typec)::text <> 'PRO'::text) AND ((dw_interm_diaire_a_r_servation.res_status)::text = ANY ('{Ferme,Nominatif,Préfacturation}'::text[])))
  • Rows Removed by Filter: 0
18. 298,009.104 298,009.104 ↑ 1.1 8,328 428,174

Seq Scan on public.sdivers_a_temps date_conso (cost=0.00..371.32 rows=9,132 width=4) (actual time=0.001..0.696 rows=8,328 loops=428,174)

  • Output: date_conso.date_, date_conso.anmoisjour, date_conso.jour, date_conso.jour_semaine, date_conso.moisjour, date_conso.semaine, date_conso.semaine_compa, date_conso.semaine_samsam, date_conso.semaine_sam_sam2, date_conso.semai (...)
19. 112,609.762 112,609.762 ↑ 1.0 3,674 428,174

Seq Scan on public.wmod_les_in_i_semaines_vacance dw_interm_diaire_a_semaines_18 (cost=0.00..58.74 rows=3,674 width=4) (actual time=0.002..0.263 rows=3,674 loops=428,174)

  • Output: dw_interm_diaire_a_semaines_18.semaine, dw_interm_diaire_a_semaines_18.date_, dw_interm_diaire_a_semaines_18.d_but_semaine, dw_interm_diaire_a_semaines_18.fin_semaine, dw_interm_diaire_a_semaines_18.zone_a, dw_interm_diaire_a_se (...)
20. 856.348 856.348 ↑ 1.0 2 428,174

Seq Scan on public.sresalys_a_campagne resalys_a_campagne (cost=0.00..1.09 rows=2 width=20) (actual time=0.002..0.002 rows=2 loops=428,174)

  • Output: resalys_a_campagne.cd_camp, resalys_a_campagne.cam_deb, resalys_a_campagne.cam_fin, resalys_a_campagne.date_r_cup_ration, resalys_a_campagne.date_r_cup_ration2, resalys_a_campagne.clibell__campagne_, resalys_a_campagne.cam_buy_workspa (...)
  • Filter: ((resalys_a_campagne.cd_camp)::text = ANY ('{2018,2019}'::text[]))
  • Rows Removed by Filter: 5
Planning time : 19.177 ms
Execution time : 1,514,036.212 ms