explain.depesz.com

PostgreSQL's explain analyze made readable

Result: QdCA

Settings
# exclusive inclusive rows x rows loops node
1. 423.807 6,552,964.941 ↓ 357,815.0 357,815 1

Gather (cost=105,368.51..131,193.70 rows=1 width=223) (actual time=659.937..6,552,964.941 rows=357,815 loops=1)

  • Workers Planned: 2
  • Workers Launched: 0
  • Buffers: shared hit=2,493,482,282
2. 1,155.822 6,552,541.134 ↓ 357,815.0 357,815 1

Nested Loop Left Join (cost=104,368.51..130,193.60 rows=1 width=223) (actual time=647.520..6,552,541.134 rows=357,815 loops=1)

  • Buffers: shared hit=2,493,482,282
3. 1,533.391 6,547,449.347 ↓ 357,815.0 357,815 1

Nested Loop Left Join (cost=104,368.08..130,187.31 rows=1 width=198) (actual time=647.496..6,547,449.347 rows=357,815 loops=1)

  • Buffers: shared hit=2,491,992,452
4. 2,465.408 6,545,200.326 ↓ 357,815.0 357,815 1

Nested Loop (cost=104,367.66..130,186.39 rows=1 width=174) (actual time=647.486..6,545,200.326 rows=357,815 loops=1)

  • Join Filter: (il_dwh_contrat.dl_soc_id = il_dwh_part_comm_dossier_1.dl_soc_id)
  • Buffers: shared hit=2,491,815,181
5. 1,904.030 17,262.763 ↓ 178,907.5 357,815 1

Nested Loop (cost=104,367.23..129,533.01 rows=2 width=148) (actual time=636.306..17,262.763 rows=357,815 loops=1)

  • Join Filter: (il_dwh_contrat.dl_soc_id = il_dwh_fact_detail_dossier.dl_soc_id)
  • Buffers: shared hit=1,902,921
6. 1,631.447 3,043.967 ↓ 141.8 362,199 1

Merge Join (cost=104,366.79..104,844.13 rows=2,555 width=83) (actual time=636.244..3,043.967 rows=362,199 loops=1)

  • Merge Cond: ((il_dwh_fact_principal_dossier.dl_soc_id = il_dwh_contrat.dl_soc_id) AND (il_dwh_fact_principal_dossier.contrat_id = il_dwh_contrat.contrat_id))
  • Buffers: shared hit=23,944
7. 632.580 1,009.713 ↓ 6.3 362,199 1

Sort (cost=103,428.81..103,573.02 rows=57,684 width=51) (actual time=623.248..1,009.713 rows=362,199 loops=1)

  • Sort Key: il_dwh_fact_principal_dossier.dl_soc_id, il_dwh_fact_principal_dossier.contrat_id
  • Sort Method: quicksort Memory: 62,674kB
  • Buffers: shared hit=22,822
8. 321.557 377.133 ↓ 6.3 362,199 1

Parallel Bitmap Heap Scan on il_dwh_fact_principal_dossier (cost=2,708.84..98,867.19 rows=57,684 width=51) (actual time=69.205..377.133 rows=362,199 loops=1)

  • Recheck Cond: ((dl_datextract = to_date(' 2018.12.31 '::text, 'YYYY.MM.DD'::text)) AND (dl_soc_id = ANY ('{3,8}'::integer[])) AND (dl_flg_bcl = 1))
  • Filter: ((dl_flg_inv = 1) AND ((type_partie)::text = 'ACTI'::text) AND ((type_objet_facturable)::text = 'OUBF'::text) AND ((type_emission)::text = 'CPER'::text) AND ((etat)::text = 'FACT'::text))
  • Rows Removed by Filter: 43,261
  • Heap Blocks: exact=21,255
  • Buffers: shared hit=22,818
9. 55.576 55.576 ↓ 2.5 405,460 1

Bitmap Index Scan on il_dwh_fact_principal_dossier_pkey (cost=0.00..2,674.23 rows=161,246 width=0) (actual time=55.576..55.576 rows=405,460 loops=1)

  • Index Cond: ((dl_datextract = to_date(' 2018.12.31 '::text, 'YYYY.MM.DD'::text)) AND (dl_soc_id = ANY ('{3,8}'::integer[])) AND (dl_flg_bcl = 1))
  • Buffers: shared hit=1,563
10. 395.993 402.807 ↓ 265.8 364,639 1

Sort (cost=937.99..941.42 rows=1,372 width=32) (actual time=12.982..402.807 rows=364,639 loops=1)

  • Sort Key: il_dwh_contrat.dl_soc_id, il_dwh_contrat.contrat_id
  • Sort Method: quicksort Memory: 569kB
  • Buffers: shared hit=1,122
11. 6.814 6.814 ↓ 3.5 4,823 1

Index Scan using olm_il_dwh_contrat_date_dl_flg_bcl_dl_flg_inv on il_dwh_contrat (cost=0.42..866.49 rows=1,372 width=32) (actual time=0.071..6.814 rows=4,823 loops=1)

  • Index Cond: ((dl_datextract = to_date(' 2018.12.31 '::text, 'YYYY.MM.DD'::text)) AND (dl_flg_bcl = 1) AND (dl_flg_inv = 1))
  • Buffers: shared hit=1,122
12. 12,314.766 12,314.766 ↑ 1.0 1 362,199

Index Scan using olm_bidwh_il_dwh_fact_detail_dossier_fact_principal_dossier_id on il_dwh_fact_detail_dossier (cost=0.44..9.65 rows=1 width=65) (actual time=0.027..0.034 rows=1 loops=362,199)

  • Index Cond: (fact_principal_dossier_id = il_dwh_fact_principal_dossier.fact_principal_dossier_id)
  • Filter: ((type_couv IS NULL) AND (dl_flg_inv = 1) AND (dl_flg_bcl = 1) AND ((COALESCE(type_coti, 'BASE'::character varying))::text = 'BASE'::text) AND ((type_part)::text = 'TOTA'::text) AND ((type_mnt)::text = 'TOTA'::text) AND (il_dwh_fact_principal_dossier.dl_soc_id = dl_soc_id) AND (dl_datextract = to_date('2018.12.31 '::text, 'YYYY.MM.DD'::text)))
  • Rows Removed by Filter: 16
  • Buffers: shared hit=1,878,977
13. 6,525,472.155 6,525,472.155 ↑ 1.0 1 357,815

Index Scan using il_dwh_part_comm_dossier_pkey on il_dwh_part_comm_dossier il_dwh_part_comm_dossier_1 (cost=0.43..326.68 rows=1 width=26) (actual time=6.569..18.237 rows=1 loops=357,815)

  • Index Cond: ((dl_datextract = to_date(' 2018.12.31 '::text, 'YYYY.MM.DD'::text)) AND (dl_soc_id = il_dwh_fact_detail_dossier.dl_soc_id) AND (dl_flg_bcl = 1) AND (dl_flg_inv = 1))
  • Filter: (((type_part_comm)::text = 'EMPL'::text) AND (il_dwh_fact_detail_dossier.dossier_id = dossier_id))
  • Rows Removed by Filter: 47,321
  • Buffers: shared hit=2,489,912,260
14. 715.630 715.630 ↓ 0.0 0 357,815

Index Scan using olm_il_dwh_part_comm_dossier_part_comm_dossier_id on il_dwh_part_comm_dossier (cost=0.42..0.91 rows=1 width=24) (actual time=0.002..0.002 rows=0 loops=357,815)

  • Index Cond: (part_comm_dossier_id = il_dwh_fact_principal_dossier.part_comm_dossier_id)
  • Filter: ((dl_flg_bcl = 1) AND (dl_flg_inv = 1) AND (dl_datextract = il_dwh_fact_principal_dossier.dl_datextract) AND (dl_flg_bcl = il_dwh_fact_principal_dossier.dl_flg_bcl) AND (dl_flg_inv = il_dwh_fact_principal_dossier.dl_flg_inv) AND (dl_soc_id = il_dwh_fact_principal_dossier.dl_soc_id) AND (dl_datextract = to_date(' 2018.12.31 '::text, 'YYYY.MM.DD'::text)))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=177,271
15. 3,935.965 3,935.965 ↓ 0.0 0 357,815

Index Scan using olm_il_dwh_part_dossier_dossier_gen_id on il_dwh_part_dossier (cost=0.43..6.27 rows=1 width=25) (actual time=0.011..0.011 rows=0 loops=357,815)

  • Index Cond: (dossier_gen_id = il_dwh_fact_detail_dossier.dossier_gen_id)
  • Filter: ((dl_flg_bcl = 1) AND (dl_flg_inv = 1) AND ((role)::text = 'AFFI'::text) AND (dl_flg_bcl = il_dwh_fact_detail_dossier.dl_flg_bcl) AND (dl_flg_inv = il_dwh_fact_detail_dossier.dl_flg_inv) AND (dl_soc_id = il_dwh_fact_detail_dossier.dl_soc_id))
  • Rows Removed by Filter: 3
  • Buffers: shared hit=1,489,830
Planning time : 8.682 ms
Execution time : 6,553,288.237 ms