explain.depesz.com

PostgreSQL's explain analyze made readable

Result: YIwg : créations de pp

Settings
# exclusive inclusive rows x rows loops node
1. 0.007 162,394.474 ↓ 3.0 3 1

HashSetOp Except (cost=1,591,961.18..3,187,718.34 rows=1 width=399) (actual time=162,394.474..162,394.474 rows=3 loops=1)

2. 0.002 162,394.467 ↑ 1,321.7 3 1

Append (cost=1,591,961.18..3,187,569.65 rows=3,965 width=399) (actual time=151,396.368..162,394.467 rows=3 loops=1)

3. 0.001 151,396.368 ↓ 3.0 3 1

Subquery Scan on *SELECT* 1 (cost=1,591,961.18..1,591,961.20 rows=1 width=399) (actual time=151,396.367..151,396.368 rows=3 loops=1)

4. 0.015 151,396.367 ↓ 3.0 3 1

HashAggregate (cost=1,591,961.18..1,591,961.19 rows=1 width=399) (actual time=151,396.367..151,396.367 rows=3 loops=1)

  • Group Key: thxd.codegreffe, thxd.nogestion, thxd.codepersonne, thxd.dtenreggreffe, thxd.dteffet, thxd.noformalite, thxd.noliasse, thxd.noevt, thxd.codeevt, '5900'::text, 'PP'::text, 0, thxd.valeuravant, thxd.valeurapres, thxd.texte
5. 16,793.624 151,396.352 ↓ 3.0 3 1

Merge Join (cost=9.73..1,591,961.14 rows=1 width=399) (actual time=41,292.705..151,396.352 rows=3 loops=1)

  • Merge Cond: ((ta_personne_rcs_dw.codepersonne)::text = (thxd.codepersonne)::text)
  • Join Filter: ((v.codepp)::text = (ta_personne_rcs_dw.codepp)::text)
  • Rows Removed by Join Filter: 136094967
6. 1,757.329 18,888.979 ↑ 1.0 4,831,812 1

GroupAggregate (cost=0.43..632,550.13 rows=4,896,370 width=14) (actual time=0.113..18,888.979 rows=4,831,812 loops=1)

  • Group Key: ta_personne_rcs_dw.codepersonne, ta_personne_rcs_dw.codepp
7. 17,131.650 17,131.650 ↑ 1.0 4,841,731 1

Index Only Scan using ix_ta_personne_rcs_dw_clepp on ta_personne_rcs_dw (cost=0.43..559,104.58 rows=4,896,370 width=14) (actual time=0.098..17,131.650 rows=4,841,731 loops=1)

  • Heap Fetches: 4841731
8. 50,661.352 115,713.749 ↓ 136,094,970.0 136,094,970 1

Materialize (cost=9.30..898,201.64 rows=1 width=938) (actual time=12,910.534..115,713.749 rows=136,094,970 loops=1)

9. 17,331.373 65,052.397 ↓ 136,023,195.0 136,023,195 1

Nested Loop (cost=9.30..898,201.64 rows=1 width=938) (actual time=12,910.532..65,052.397 rows=136,023,195 loops=1)

10. 134.226 134.226 ↓ 2.0 28,427 1

Index Scan using ix_ta_histo_xml_dw_codep_3 on ta_histo_xml_dw thxd (cost=0.42..12,308.09 rows=13,996 width=399) (actual time=0.014..134.226 rows=28,427 loops=1)

  • Filter: (((codeevt)::text = ANY ('{5997,5998}'::text[])) AND (btraite = 0))
  • Rows Removed by Filter: 50909
11. 7,606.276 47,586.798 ↓ 4,785.0 4,785 28,427

Materialize (cost=8.88..885,718.60 rows=1 width=539) (actual time=0.454..1.674 rows=4,785 loops=28,427)

12. 8,255.921 39,980.522 ↓ 4,785.0 4,785 1

Nested Loop (cost=8.88..885,718.59 rows=1 width=539) (actual time=12,910.513..39,980.522 rows=4,785 loops=1)

  • Join Filter: ((v.codepp)::text = (tpd.codepp)::text)
  • Rows Removed by Join Filter: 86048655
13. 1.898 12,914.766 ↓ 4,785.0 4,785 1

Subquery Scan on v (cost=8.45..885,710.13 rows=1 width=516) (actual time=12,909.590..12,914.766 rows=4,785 loops=1)

14. 1,086.713 12,912.868 ↓ 4,785.0 4,785 1

HashSetOp Except (cost=8.45..885,710.12 rows=1 width=23) (actual time=12,909.590..12,912.868 rows=4,785 loops=1)

15. 418.248 11,826.155 ↑ 1.0 6,607,318 1

Append (cost=8.45..868,869.71 rows=6,736,165 width=23) (actual time=12.769..11,826.155 rows=6,607,318 loops=1)

16. 1.459 16.126 ↓ 16,446.0 16,446 1

Subquery Scan on *SELECT* 1_1 (cost=8.45..8.47 rows=1 width=23) (actual time=12.769..16.126 rows=16,446 loops=1)

17. 6.447 14.667 ↓ 16,446.0 16,446 1

HashAggregate (cost=8.45..8.46 rows=1 width=23) (actual time=12.768..14.667 rows=16,446 loops=1)

  • Group Key: ta_pp_dw.codepp
18. 8.220 8.220 ↓ 17,984.0 17,984 1

Index Scan using ix_ta_pp_dw_btr_2 on ta_pp_dw (cost=0.43..8.45 rows=1 width=23) (actual time=0.022..8.220 rows=17,984 loops=1)

  • Index Cond: (btraite = 0)
19. 715.165 11,391.781 ↑ 1.0 6,590,872 1

Subquery Scan on *SELECT* 2_1 (cost=0.56..868,861.24 rows=6,736,164 width=23) (actual time=0.031..11,391.781 rows=6,590,872 loops=1)

20. 1,166.437 10,676.616 ↑ 1.0 6,590,872 1

Unique (cost=0.56..801,499.60 rows=6,736,164 width=23) (actual time=0.030..10,676.616 rows=6,590,872 loops=1)

21. 9,510.179 9,510.179 ↑ 1.0 6,593,687 1

Index Scan using ix_ta_pp_dw_cle_2 on ta_pp_dw ta_pp_dw_1 (cost=0.56..784,659.19 rows=6,736,164 width=23) (actual time=0.029..9,510.179 rows=6,593,687 loops=1)

  • Filter: (btraite = ANY ('{1,2}'::integer[]))
  • Rows Removed by Filter: 17984
22. 18,809.835 18,809.835 ↓ 17,984.0 17,984 4,785

Index Scan using ix_ta_pp_dw_btr_2 on ta_pp_dw tpd (cost=0.43..8.45 rows=1 width=23) (actual time=0.007..3.931 rows=17,984 loops=4,785)

  • Index Cond: (btraite = 0)
23. 0.001 10,998.097 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2 (cost=1,595,529.17..1,595,608.45 rows=3,964 width=399) (actual time=10,998.097..10,998.097 rows=0 loops=1)

24. 0.007 10,998.096 ↓ 0.0 0 1

HashAggregate (cost=1,595,529.17..1,595,568.81 rows=3,964 width=399) (actual time=10,998.096..10,998.096 rows=0 loops=1)

  • Group Key: thxd_1.codegreffe, thxd_1.nogestion, thxd_1.codepersonne, thxd_1.dtenreggreffe, thxd_1.dteffet, thxd_1.noformalite, thxd_1.noliasse, thxd_1.noevt, thxd_1.codeevt, '5900'::text, 'PP'::text, 0, thxd_1.valeuravant, thxd_1.valeurapres, thxd_1.texte
25. 0.329 10,998.089 ↓ 0.0 0 1

Hash Join (cost=889,442.09..1,594,817.27 rows=18,984 width=399) (actual time=10,998.089..10,998.089 rows=0 loops=1)

  • Hash Cond: ((ta_personne_rcs_dw_1.codepersonne)::text = (thxd_1.codepersonne)::text)
26. 0.013 10,946.071 ↓ 0.0 0 1

Hash Join (cost=885,719.16..1,585,838.77 rows=24,482 width=10) (actual time=10,946.071..10,946.071 rows=0 loops=1)

  • Hash Cond: ((ta_personne_rcs_dw_1.codepp)::text = (v_1.codepp)::text)
27. 0.002 0.022 ↑ 4,896,370.0 1 1

GroupAggregate (cost=0.43..632,550.13 rows=4,896,370 width=14) (actual time=0.022..0.022 rows=1 loops=1)

  • Group Key: ta_personne_rcs_dw_1.codepersonne, ta_personne_rcs_dw_1.codepp
28. 0.020 0.020 ↑ 699,481.4 7 1

Index Only Scan using ix_ta_personne_rcs_dw_clepp on ta_personne_rcs_dw ta_personne_rcs_dw_1 (cost=0.43..559,104.58 rows=4,896,370 width=14) (actual time=0.013..0.020 rows=7 loops=1)

  • Heap Fetches: 7
29. 0.001 10,946.036 ↓ 0.0 0 1

Hash (cost=885,718.72..885,718.72 rows=1 width=539) (actual time=10,946.036..10,946.036 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
30. 2.389 10,946.035 ↓ 0.0 0 1

Nested Loop (cost=9.01..885,718.72 rows=1 width=539) (actual time=10,946.035..10,946.035 rows=0 loops=1)

31. 0.552 10,900.581 ↓ 4,785.0 4,785 1

Subquery Scan on v_1 (cost=8.45..885,710.13 rows=1 width=516) (actual time=10,899.101..10,900.581 rows=4,785 loops=1)

32. 1,003.141 10,900.029 ↓ 4,785.0 4,785 1

HashSetOp Except (cost=8.45..885,710.12 rows=1 width=23) (actual time=10,899.099..10,900.029 rows=4,785 loops=1)

33. 402.732 9,896.888 ↑ 1.0 6,607,318 1

Append (cost=8.45..868,869.71 rows=6,736,165 width=23) (actual time=11.561..9,896.888 rows=6,607,318 loops=1)

34. 1.425 15.047 ↓ 16,446.0 16,446 1

Subquery Scan on *SELECT* 1_2 (cost=8.45..8.47 rows=1 width=23) (actual time=11.561..15.047 rows=16,446 loops=1)

35. 6.828 13.622 ↓ 16,446.0 16,446 1

HashAggregate (cost=8.45..8.46 rows=1 width=23) (actual time=11.559..13.622 rows=16,446 loops=1)

  • Group Key: ta_pp_dw_2.codepp
36. 6.794 6.794 ↓ 17,984.0 17,984 1

Index Scan using ix_ta_pp_dw_btr_2 on ta_pp_dw ta_pp_dw_2 (cost=0.43..8.45 rows=1 width=23) (actual time=0.012..6.794 rows=17,984 loops=1)

  • Index Cond: (btraite = 0)
37. 693.142 9,479.109 ↑ 1.0 6,590,872 1

Subquery Scan on *SELECT* 2_2 (cost=0.56..868,861.24 rows=6,736,164 width=23) (actual time=0.020..9,479.109 rows=6,590,872 loops=1)

38. 1,102.424 8,785.967 ↑ 1.0 6,590,872 1

Unique (cost=0.56..801,499.60 rows=6,736,164 width=23) (actual time=0.019..8,785.967 rows=6,590,872 loops=1)

39. 7,683.543 7,683.543 ↑ 1.0 6,593,687 1

Index Scan using ix_ta_pp_dw_cle_2 on ta_pp_dw ta_pp_dw_3 (cost=0.56..784,659.19 rows=6,736,164 width=23) (actual time=0.018..7,683.543 rows=6,593,687 loops=1)

  • Filter: (btraite = ANY ('{1,2}'::integer[]))
  • Rows Removed by Filter: 17984
40. 43.065 43.065 ↓ 0.0 0 4,785

Index Scan using ix_ta_pp_dw_cle_2 on ta_pp_dw tpd_1 (cost=0.56..8.58 rows=1 width=23) (actual time=0.009..0.009 rows=0 loops=4,785)

  • Index Cond: ((codepp)::text = (v_1.codepp)::text)
  • Filter: (btraite = ANY ('{1,2}'::integer[]))
  • Rows Removed by Filter: 1
41. 39.030 51.689 ↓ 2.0 28,427 1

Hash (cost=2,822.97..2,822.97 rows=13,996 width=399) (actual time=51.689..51.689 rows=28,427 loops=1)

  • Buckets: 16384 Batches: 2 Memory Usage: 1567kB
42. 10.098 12.659 ↓ 2.0 28,427 1

Bitmap Heap Scan on ta_histo_xml_dw thxd_1 (cost=312.03..2,822.97 rows=13,996 width=399) (actual time=2.788..12.659 rows=28,427 loops=1)

  • Recheck Cond: (((codeevt)::text = ANY ('{5997,5998}'::text[])) AND (btraite = 0))
  • Heap Blocks: exact=1927
43. 2.561 2.561 ↓ 2.0 28,427 1

Bitmap Index Scan on ix_thxd_codeevtbtraite_2 (cost=0.00..308.54 rows=13,996 width=0) (actual time=2.561..2.561 rows=28,427 loops=1)

  • Index Cond: (((codeevt)::text = ANY ('{5997,5998}'::text[])) AND (btraite = 0))