explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Uwfa

Settings
# exclusive inclusive rows x rows loops node
1. 4,908.726 496,382.735 ↑ 1,271.2 576,059 1

Unique (cost=803,690,098.76..838,473,499.46 rows=732,282,120 width=454) (actual time=489,862.990..496,382.735 rows=576,059 loops=1)

2. 43,306.928 491,474.009 ↑ 66.1 11,072,478 1

Sort (cost=803,690,098.76..805,520,804.06 rows=732,282,120 width=454) (actual time=489,862.989..491,474.009 rows=11,072,478 loops=1)

  • Sort Key: per_vm_societaire_q.societaire_nu, (first_value(con_vm_contrat_q.entite_comm_resp_cd) OVER (?)), per_vm_societaire_q.succession_cd, per_vm_societaire_q.titre_cd, per_vm_societaire_q.mail_lb, per_vm_societaire_q.accepte_amdm_cd, per_vm_societaire_q.accepte_partenaire_cd, (first_value(con_vm_vehicule_q.marque_vehicule_lb) OVER (?)), (first_value(con_vm_vehicule_q.appel_vehicule_lb) OVER (?)), (CASE WHEN (con_vm_contrat_q_1.contrat_id IS NOT NULL) THEN 'OUI'::text ELSE 'NON'::text END), (first_value((CASE WHEN (con_ces.numero_contrat_id IS NOT NULL) THEN 'OUI'::text ELSE 'NON'::text END)) OVER (?)), (CASE WHEN (per_parrainage_societaire.parrain_id IS NOT NULL) THEN 'OUI'::text ELSE 'NON'::text END), (CASE WHEN (per_parrainage_societaire_1.filleuil_id IS NOT NULL) THEN 'OUI'::text ELSE 'NON'::text END), (CASE WHEN (form.personne_id IS NOT NULL) THEN 'OUI'::text ELSE 'NON'::text END), (CASE WHEN ((oas400_fdosvep0.etagen)::text = 'R'::text) THEN 'OUI'::text ELSE 'NON'::text END), (CASE WHEN ((oas400_fdosvep0.etagen)::text = 'E'::text) THEN 'OUI'::text ELSE 'NON'::text END), per_vm_societaire_q.bureau_gestion_cd, equ.equipe_amdm_lb
  • Sort Method: external merge Disk: 1,390,136kB
3. 7,104.362 448,167.081 ↑ 66.1 11,072,478 1

WindowAgg (cost=501,643,130.01..519,950,183.01 rows=732,282,120 width=454) (actual time=439,319.053..448,167.081 rows=11,072,478 loops=1)

4. 8,459.883 441,062.719 ↑ 66.1 11,072,478 1

Sort (cost=501,643,130.01..503,473,835.31 rows=732,282,120 width=322) (actual time=439,319.026..441,062.719 rows=11,072,478 loops=1)

  • Sort Key: con_vm_contrat_q.societaire_nu, (CASE WHEN (con_ces.numero_contrat_id IS NOT NULL) THEN 'OUI'::text ELSE 'NON'::text END) DESC
  • Sort Method: external merge Disk: 1,283,272kB
5. 10,741.687 432,602.836 ↑ 66.1 11,072,478 1

WindowAgg (cost=246,507,984.06..264,815,037.06 rows=732,282,120 width=322) (actual time=419,826.724..432,602.836 rows=11,072,478 loops=1)

6. 12,084.633 421,861.149 ↑ 66.1 11,072,478 1

Sort (cost=246,507,984.06..248,338,689.36 rows=732,282,120 width=226) (actual time=419,826.687..421,861.149 rows=11,072,478 loops=1)

  • Sort Key: con_vm_contrat_q.societaire_nu, con_vm_contrat_q.saisie_contrat_dt
  • Sort Method: external merge Disk: 1,055,872kB
7. 214,260.080 409,776.516 ↑ 66.1 11,072,478 1

Nested Loop Left Join (cost=6,885,226.53..44,863,758.22 rows=732,282,120 width=226) (actual time=92,162.353..409,776.516 rows=11,072,478 loops=1)

  • Join Filter: CASE WHEN (per_vm_societaire_q.bureau_gestion_cd = 99) THEN (equ.equipe_amdm_cd = '-2'::integer) WHEN (per_vm_societaire_q.bureau_gestion_cd = '-1'::integer) THEN (equ.equipe_amdm_cd = '-1'::integer) ELSE (equ.bureau_rattachmnt_cd = per_vm_societaire_q.bureau_gestion_cd) END
  • Rows Removed by Join Filter: 2,978,772,783
8. 6,140.343 106,936.612 ↓ 2.0 11,072,478 1

Hash Right Join (cost=6,885,220.24..8,249,524.33 rows=5,424,312 width=176) (actual time=92,162.251..106,936.612 rows=11,072,478 loops=1)

  • Hash Cond: (oas400_fdosvep0.nusoc = per_vm_societaire_q.societaire_nu)
9. 8,638.308 8,638.308 ↑ 1.0 562,386 1

Seq Scan on oas400_fdosvep0 (cost=0.00..1,072,321.86 rows=562,386 width=9) (actual time=0.287..8,638.308 rows=562,386 loops=1)

10. 2,165.357 92,157.961 ↓ 1.1 6,131,040 1

Hash (cost=6,684,986.34..6,684,986.34 rows=5,424,312 width=175) (actual time=92,157.961..92,157.961 rows=6,131,040 loops=1)

  • Buckets: 2,097,152 Batches: 4 Memory Usage: 169,184kB
11. 4,798.424 89,992.604 ↓ 1.1 6,131,040 1

Hash Join (cost=2,497,470.77..6,684,986.34 rows=5,424,312 width=175) (actual time=7,050.757..89,992.604 rows=6,131,040 loops=1)

  • Hash Cond: (con_vm_contrat_q.societaire_nu = per_vm_societaire_q.societaire_nu)
12. 0.000 78,180.989 ↓ 1.2 12,958,828 1

Gather (cost=4,315.39..3,922,764.53 rows=10,741,165 width=51) (actual time=37.120..78,180.989 rows=12,958,828 loops=1)

  • Workers Planned: 7
  • Workers Launched: 7
13. 376.334 83,834.300 ↓ 1.1 1,619,854 8 / 8

Parallel Hash Left Join (cost=3,315.39..2,847,648.03 rows=1,534,452 width=51) (actual time=25.584..83,834.300 rows=1,619,854 loops=8)

  • Hash Cond: ((con_vm_contrat_q.contrat_id)::text = (con_ces.numero_contrat_id)::text)
14. 170.743 83,433.762 ↓ 1.1 1,613,580 8 / 8

Nested Loop (cost=0.56..2,816,703.82 rows=1,534,452 width=51) (actual time=1.097..83,433.762 rows=1,613,580 loops=8)

15. 2,585.788 2,585.788 ↑ 1.1 1,613,545 8 / 8

Parallel Seq Scan on con_vm_contrat_q (cost=0.00..1,179,819.27 rows=1,733,026 width=42) (actual time=0.025..2,585.788 rows=1,613,545 loops=8)

  • Filter: (((categorie_produit_cd)::text <> 'S'::text) AND (sid_publie_in = 1) AND ((((entite_comm_resp_cd)::text = 'DECO'::text) AND ((contrat_distrib_lb)::text = 'En direct'::text)) OR ((entite_comm_resp_cd)::text = 'LCDM'::text)))
  • Rows Removed by Filter: 1,298,777
16. 80,677.231 80,677.231 ↑ 1.0 1 12,908,357 / 8

Index Scan using xpk_con_vm_vehicule_q on con_vm_vehicule_q (cost=0.56..0.93 rows=1 width=46) (actual time=0.041..0.050 rows=1 loops=12,908,357)

  • Index Cond: (((contrat_id)::text = (con_vm_contrat_q.contrat_id)::text) AND (rang_contrat_id = con_vm_contrat_q.rang_contrat_id) AND (numero_maj_id = con_vm_contrat_q.numero_maj_id))
  • Filter: (sid_publie_in = 1)
  • Rows Removed by Filter: 2
17. 1.885 24.204 ↑ 4.6 4,810 8 / 8

Parallel Hash (cost=3,036.47..3,036.47 rows=22,268 width=21) (actual time=24.204..24.204 rows=4,810 loops=8)

  • Buckets: 65,536 Batches: 1 Memory Usage: 2,720kB
18. 22.319 22.319 ↑ 4.6 4,810 8 / 8

Parallel Seq Scan on con_ces (cost=0.00..3,036.47 rows=22,268 width=21) (actual time=0.085..22.319 rows=4,810 loops=8)

  • Filter: ((sid_publie_in = 1) AND ((status_cd)::text = 'E'::text))
  • Rows Removed by Filter: 8,411
19. 192.709 7,013.191 ↓ 1.3 598,159 1

Hash (cost=2,487,582.00..2,487,582.00 rows=445,871 width=124) (actual time=7,013.191..7,013.191 rows=598,159 loops=1)

  • Buckets: 1,048,576 (originally 524288) Batches: 1 (originally 1) Memory Usage: 46,845kB
20. 89.876 6,820.482 ↓ 1.3 598,159 1

Hash Left Join (cost=2,142,967.30..2,487,582.00 rows=445,871 width=124) (actual time=4,874.939..6,820.482 rows=598,159 loops=1)

  • Hash Cond: ((per_vm_societaire_q.personne_id)::text = (per_parrainage_societaire_1.filleuil_id)::text)
21. 101.365 6,725.003 ↓ 1.3 598,159 1

Hash Left Join (cost=2,142,556.00..2,483,782.98 rows=445,871 width=124) (actual time=4,869.295..6,725.003 rows=598,159 loops=1)

  • Hash Cond: ((per_vm_societaire_q.personne_id)::text = (per_parrainage_societaire.parrain_id)::text)
22. 279.951 6,609.862 ↓ 1.3 597,971 1

Hash Right Join (cost=2,142,144.70..2,477,197.28 rows=445,871 width=103) (actual time=4,855.446..6,609.862 rows=597,971 loops=1)

  • Hash Cond: ((form.personne_id)::text = (per_vm_societaire_q.personne_id)::text)
23. 0.000 1,536.020 ↑ 2.3 29,060 1

Gather (cost=1,000.00..334,352.41 rows=67,871 width=21) (actual time=1.719..1,536.020 rows=29,060 loops=1)

  • Workers Planned: 6
  • Workers Launched: 6
24. 1,552.905 1,552.905 ↑ 2.7 4,151 7 / 7

Parallel Seq Scan on con_vm_formation_conducteur_q form (cost=0.00..326,565.31 rows=11,312 width=21) (actual time=22.102..1,552.905 rows=4,151 loops=7)

  • Filter: ((obtention_dt >= '2018-08-31 00:00:00'::timestamp without time zone) AND (obtention_dt <= '2020-08-31'::date) AND ((type_formation_lb)::text = 'Formation PERF'::text) AND ((statut_formation_cd)::text = 'VALIDATED'::text))
  • Rows Removed by Filter: 1,436,088
25. 182.231 4,793.891 ↓ 1.3 597,902 1

Hash (cost=2,135,571.31..2,135,571.31 rows=445,871 width=82) (actual time=4,793.891..4,793.891 rows=597,902 loops=1)

  • Buckets: 1,048,576 (originally 524288) Batches: 1 (originally 1) Memory Usage: 59,077kB
26. 169.837 4,611.660 ↓ 1.3 597,902 1

Hash Right Join (cost=2,040,069.27..2,135,571.31 rows=445,871 width=82) (actual time=4,466.739..4,611.660 rows=597,902 loops=1)

  • Hash Cond: (con_vm_contrat_q_1.societaire_nu = per_vm_societaire_q.societaire_nu)
27. 918.108 918.108 ↓ 4.5 27,764 1

Index Scan using xfk_con_vm_contrat_q_2 on con_vm_contrat_q con_vm_contrat_q_1 (cost=0.56..95,348.29 rows=6,160 width=25) (actual time=0.316..918.108 rows=27,764 loops=1)

  • Index Cond: ((derniere_image_cd = 'O'::bpchar) AND ((statut_contrat_cd)::text = 'E'::text))
  • Filter: ((sid_publie_in = 1) AND ((produit_cd)::text = 'MMAG'::text))
  • Rows Removed by Filter: 868,880
28. 174.035 3,523.715 ↓ 1.3 597,902 1

Hash (cost=2,034,495.32..2,034,495.32 rows=445,871 width=61) (actual time=3,523.715..3,523.715 rows=597,902 loops=1)

  • Buckets: 1,048,576 (originally 524288) Batches: 1 (originally 1) Memory Usage: 54,406kB
29. 1,279.242 3,349.680 ↓ 1.3 597,902 1

Bitmap Heap Scan on per_vm_societaire_q (cost=1,335,065.71..2,034,495.32 rows=445,871 width=61) (actual time=2,515.957..3,349.680 rows=597,902 loops=1)

  • Recheck Cond: ((sid_publie_in = 1) OR (sid_frachr_in = 1))
  • Filter: ((societaire_ann_adhesion_dt IS NULL) AND (NOT (hashed SubPlan 1)) AND (sid_publie_in = 1))
  • Rows Removed by Filter: 308,592
  • Heap Blocks: exact=44,344
30. 51.620 51.620 ↑ 1.9 906,494 1

Bitmap Index Scan on xfk_per_vm_societaire_q (cost=0.00..9,479.60 rows=1,722,441 width=0) (actual time=51.620..51.620 rows=906,494 loops=1)

31.          

SubPlan (for Bitmap Heap Scan)

32. 0.000 2,018.818 ↓ 1.0 1,682,418 1

Gather (cost=1,000.00..1,321,337.62 rows=1,654,811 width=4) (actual time=1.385..2,018.818 rows=1,682,418 loops=1)

  • Workers Planned: 7
  • Workers Launched: 7
33. 2,268.793 2,268.793 ↑ 1.1 210,302 8 / 8

Parallel Seq Scan on con_vm_contrat_q con_vm_contrat_q_2 (cost=0.00..1,154,856.52 rows=236,402 width=4) (actual time=0.067..2,268.793 rows=210,302 loops=8)

  • Filter: ((saisie_contrat_dt >= '2019-08-01 00:00:00'::timestamp without time zone) AND (sid_publie_in = 1))
  • Rows Removed by Filter: 2,702,020
34. 1.645 13.776 ↓ 1.0 8,683 1

Hash (cost=303.18..303.18 rows=8,650 width=21) (actual time=13.776..13.776 rows=8,683 loops=1)

  • Buckets: 16,384 Batches: 1 Memory Usage: 578kB
35. 12.131 12.131 ↓ 1.0 8,683 1

Seq Scan on per_parrainage_societaire (cost=0.00..303.18 rows=8,650 width=21) (actual time=0.382..12.131 rows=8,683 loops=1)

  • Filter: (sid_publie_in = 1)
  • Rows Removed by Filter: 107
36. 2.043 5.603 ↓ 1.0 8,683 1

Hash (cost=303.18..303.18 rows=8,650 width=21) (actual time=5.603..5.603 rows=8,683 loops=1)

  • Buckets: 16,384 Batches: 1 Memory Usage: 578kB
37. 3.560 3.560 ↓ 1.0 8,683 1

Seq Scan on per_parrainage_societaire per_parrainage_societaire_1 (cost=0.00..303.18 rows=8,650 width=21) (actual time=0.008..3.560 rows=8,683 loops=1)

  • Filter: (sid_publie_in = 1)
  • Rows Removed by Filter: 107
38. 88,579.654 88,579.824 ↑ 1.0 270 11,072,478

Materialize (cost=6.29..128.56 rows=270 width=26) (actual time=0.000..0.008 rows=270 loops=11,072,478)

39. 0.127 0.170 ↑ 1.0 270 1

Bitmap Heap Scan on org_vm_equipe_amdm equ (cost=6.29..127.21 rows=270 width=26) (actual time=0.065..0.170 rows=270 loops=1)

  • Recheck Cond: ((sid_publie_in = 1) OR (sid_frachr_in = 1))
  • Filter: ((sid_publie_in = 1) AND (equipe_amdm_cd <> ALL ('{-3,-4,-5,-6,-7}'::integer[])))
  • Rows Removed by Filter: 5
  • Heap Blocks: exact=12
40. 0.043 0.043 ↑ 1.9 275 1

Bitmap Index Scan on xpk_org_vm_equipe_amdm (cost=0.00..6.22 rows=529 width=0) (actual time=0.043..0.043 rows=275 loops=1)

Planning time : 18.436 ms
Execution time : 497,229.949 ms