explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 54DV : PP 2

Settings
# exclusive inclusive rows x rows loops node
1. 0.011 145,894.980 ↓ 3.0 3 1

HashSetOp Except (cost=1,579,051.16..3,161,898.29 rows=1 width=399) (actual time=145,894.979..145,894.980 rows=3 loops=1)

2. 0.001 145,894.969 ↑ 1,321.7 3 1

Append (cost=1,579,051.16..3,161,749.61 rows=3,965 width=399) (actual time=136,274.640..145,894.969 rows=3 loops=1)

3. 0.002 136,274.642 ↓ 3.0 3 1

Subquery Scan on *SELECT* 1 (cost=1,579,051.16..1,579,051.18 rows=1 width=399) (actual time=136,274.640..136,274.642 rows=3 loops=1)

4. 0.020 136,274.640 ↓ 3.0 3 1

HashAggregate (cost=1,579,051.16..1,579,051.17 rows=1 width=399) (actual time=136,274.639..136,274.640 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,742.084 136,274.620 ↓ 3.0 3 1

Merge Join (cost=9.73..1,579,051.12 rows=1 width=399) (actual time=37,980.408..136,274.620 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,759.940 6,900.524 ↑ 1.0 4,831,812 1

GroupAggregate (cost=0.43..632,550.13 rows=4,896,370 width=14) (actual time=0.094..6,900.524 rows=4,831,812 loops=1)

  • Group Key: ta_personne_rcs_dw.codepersonne, ta_personne_rcs_dw.codepp
7. 5,140.584 5,140.584 ↑ 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.055..5,140.584 rows=4,841,731 loops=1)

  • Heap Fetches: 4841731
8. 50,527.968 112,632.012 ↓ 136,094,970.0 136,094,970 1

Materialize (cost=9.30..885,291.62 rows=1 width=938) (actual time=9,593.729..112,632.012 rows=136,094,970 loops=1)

9. 17,419.986 62,104.044 ↓ 136,023,195.0 136,023,195 1

Nested Loop (cost=9.30..885,291.62 rows=1 width=938) (actual time=9,593.725..62,104.044 rows=136,023,195 loops=1)

10. 138.949 138.949 ↓ 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.045..138.949 rows=28,427 loops=1)

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

Materialize (cost=8.88..872,808.58 rows=1 width=539) (actual time=0.338..1.567 rows=4,785 loops=28,427)

12. 8,230.862 36,610.247 ↓ 4,785.0 4,785 1

Nested Loop (cost=8.88..872,808.57 rows=1 width=539) (actual time=9,593.674..36,610.247 rows=4,785 loops=1)

  • Join Filter: ((v.codepp)::text = (tpd.codepp)::text)
  • Rows Removed by Join Filter: 86048655
13. 1.974 9,598.260 ↓ 4,785.0 4,785 1

Subquery Scan on v (cost=8.45..872,800.11 rows=1 width=516) (actual time=9,592.761..9,598.260 rows=4,785 loops=1)

14. 994.062 9,596.286 ↓ 4,785.0 4,785 1

HashSetOp Except (cost=8.45..872,800.10 rows=1 width=23) (actual time=9,592.760..9,596.286 rows=4,785 loops=1)

15. 399.567 8,602.224 ↑ 1.0 6,607,318 1

Append (cost=8.45..856,270.92 rows=6,611,672 width=23) (actual time=14.473..8,602.224 rows=6,607,318 loops=1)

16. 1.511 17.994 ↓ 16,446.0 16,446 1

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

17. 6.976 16.483 ↓ 16,446.0 16,446 1

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

  • Group Key: ta_pp_dw.codepp
18. 9.507 9.507 ↓ 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.055..9.507 rows=17,984 loops=1)

  • Index Cond: (btraite = 0)
19. 686.387 8,184.663 ↑ 1.0 6,590,872 1

Subquery Scan on *SELECT* 2_1 (cost=0.56..856,262.45 rows=6,611,671 width=23) (actual time=0.039..8,184.663 rows=6,590,872 loops=1)

20. 1,121.436 7,498.276 ↑ 1.0 6,590,872 1

Unique (cost=0.56..790,145.74 rows=6,611,671 width=23) (actual time=0.038..7,498.276 rows=6,590,872 loops=1)

21. 6,376.840 6,376.840 ↑ 1.0 6,593,687 1

Index Only Scan using ix_ta_pp_dw_cle_3 on ta_pp_dw ta_pp_dw_1 (cost=0.56..773,616.56 rows=6,611,671 width=23) (actual time=0.037..6,376.840 rows=6,593,687 loops=1)

  • Filter: (btraite = ANY ('{1,2}'::integer[]))
  • Rows Removed by Filter: 17984
  • Heap Fetches: 6611671
22. 18,781.125 18,781.125 ↓ 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.925 rows=17,984 loops=4,785)

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

Subquery Scan on *SELECT* 2 (cost=1,582,619.15..1,582,698.43 rows=3,964 width=399) (actual time=9,620.326..9,620.326 rows=0 loops=1)

24. 0.006 9,620.325 ↓ 0.0 0 1

HashAggregate (cost=1,582,619.15..1,582,658.79 rows=3,964 width=399) (actual time=9,620.325..9,620.325 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.339 9,620.319 ↓ 0.0 0 1

Hash Join (cost=876,532.06..1,581,907.25 rows=18,984 width=399) (actual time=9,620.319..9,620.319 rows=0 loops=1)

  • Hash Cond: ((ta_personne_rcs_dw_1.codepersonne)::text = (thxd_1.codepersonne)::text)
26. 0.011 9,562.835 ↓ 0.0 0 1

Hash Join (cost=872,809.14..1,572,928.75 rows=24,482 width=10) (actual time=9,562.835..9,562.835 rows=0 loops=1)

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

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

  • Group Key: ta_personne_rcs_dw_1.codepersonne, ta_personne_rcs_dw_1.codepp
28. 0.019 0.019 ↑ 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.012..0.019 rows=7 loops=1)

  • Heap Fetches: 7
29. 0.000 9,562.804 ↓ 0.0 0 1

Hash (cost=872,808.70..872,808.70 rows=1 width=539) (actual time=9,562.804..9,562.804 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
30. 1.837 9,562.804 ↓ 0.0 0 1

Nested Loop (cost=9.01..872,808.70 rows=1 width=539) (actual time=9,562.804..9,562.804 rows=0 loops=1)

31. 0.754 9,522.687 ↓ 4,785.0 4,785 1

Subquery Scan on v_1 (cost=8.45..872,800.11 rows=1 width=516) (actual time=9,521.002..9,522.687 rows=4,785 loops=1)

32. 975.821 9,521.933 ↓ 4,785.0 4,785 1

HashSetOp Except (cost=8.45..872,800.10 rows=1 width=23) (actual time=9,521.001..9,521.933 rows=4,785 loops=1)

33. 397.596 8,546.112 ↑ 1.0 6,607,318 1

Append (cost=8.45..856,270.92 rows=6,611,672 width=23) (actual time=11.490..8,546.112 rows=6,607,318 loops=1)

34. 1.484 14.952 ↓ 16,446.0 16,446 1

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

35. 6.794 13.468 ↓ 16,446.0 16,446 1

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

  • Group Key: ta_pp_dw_2.codepp
36. 6.674 6.674 ↓ 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.674 rows=17,984 loops=1)

  • Index Cond: (btraite = 0)
37. 684.623 8,133.564 ↑ 1.0 6,590,872 1

Subquery Scan on *SELECT* 2_2 (cost=0.56..856,262.45 rows=6,611,671 width=23) (actual time=0.019..8,133.564 rows=6,590,872 loops=1)

38. 1,133.123 7,448.941 ↑ 1.0 6,590,872 1

Unique (cost=0.56..790,145.74 rows=6,611,671 width=23) (actual time=0.018..7,448.941 rows=6,590,872 loops=1)

39. 6,315.818 6,315.818 ↑ 1.0 6,593,687 1

Index Only Scan using ix_ta_pp_dw_cle_3 on ta_pp_dw ta_pp_dw_3 (cost=0.56..773,616.56 rows=6,611,671 width=23) (actual time=0.017..6,315.818 rows=6,593,687 loops=1)

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

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

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

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

  • Buckets: 16384 Batches: 2 Memory Usage: 1567kB
42. 9.957 12.556 ↓ 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.829..12.556 rows=28,427 loops=1)

  • Recheck Cond: (((codeevt)::text = ANY ('{5997,5998}'::text[])) AND (btraite = 0))
  • Heap Blocks: exact=1927
43. 2.599 2.599 ↓ 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.599..2.599 rows=28,427 loops=1)

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