explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ViKd : PP 3

Settings
# exclusive inclusive rows x rows loops node
1. 0.018 22,886.070 ↓ 3.0 3 1

HashSetOp Except (cost=665,265.53..1,339,003.82 rows=1 width=399) (actual time=22,886.069..22,886.070 rows=3 loops=1)

2. 0.001 22,886.052 ↑ 1,321.7 3 1

Append (cost=665,265.53..1,338,855.13 rows=3,965 width=399) (actual time=12,511.821..22,886.052 rows=3 loops=1)

3. 0.003 12,511.821 ↓ 3.0 3 1

Subquery Scan on *SELECT* 1 (cost=665,265.53..665,265.55 rows=1 width=399) (actual time=12,511.820..12,511.821 rows=3 loops=1)

4. 0.026 12,511.818 ↓ 3.0 3 1

HashAggregate (cost=665,265.53..665,265.54 rows=1 width=399) (actual time=12,511.818..12,511.818 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. 42.775 12,511.792 ↓ 3.0 3 1

Nested Loop (cost=173,851.88..665,265.49 rows=1 width=399) (actual time=10,578.953..12,511.792 rows=3 loops=1)

  • Join Filter: ((ta_personne_rcs_dw.codepp)::text = (v.codepp)::text)
  • Rows Removed by Join Filter: 588552
6. 0.723 4,614.975 ↓ 123.0 123 1

Nested Loop (cost=173,843.43..290,132.33 rows=1 width=426) (actual time=2,752.263..4,614.975 rows=123 loops=1)

7. 407.027 4,609.725 ↓ 503.0 503 1

Hash Join (cost=173,843.01..290,131.81 rows=1 width=37) (actual time=2,752.227..4,609.725 rows=503 loops=1)

  • Hash Cond: ((ta_personne_rcs_dw.codepp)::text = (tpd.codepp)::text)
8. 3,686.419 4,192.334 ↑ 1.0 4,831,850 1

HashAggregate (cost=173,834.55..222,798.25 rows=4,896,370 width=14) (actual time=2,728.132..4,192.334 rows=4,831,850 loops=1)

  • Group Key: ta_personne_rcs_dw.codepersonne, ta_personne_rcs_dw.codepp
9. 505.915 505.915 ↑ 1.0 4,841,768 1

Seq Scan on ta_personne_rcs_dw (cost=0.00..149,352.70 rows=4,896,370 width=14) (actual time=0.005..505.915 rows=4,841,768 loops=1)

10. 3.310 10.364 ↓ 17,984.0 17,984 1

Hash (cost=8.45..8.45 rows=1 width=23) (actual time=10.364..10.364 rows=17,984 loops=1)

  • Buckets: 32768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1225kB
11. 7.054 7.054 ↓ 17,984.0 17,984 1

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.024..7.054 rows=17,984 loops=1)

  • Index Cond: (btraite = 0)
12. 4.527 4.527 ↓ 0.0 0 503

Index Scan using ix_ta_histo_xml_dw_codep_3 on ta_histo_xml_dw thxd (cost=0.42..0.51 rows=1 width=399) (actual time=0.009..0.009 rows=0 loops=503)

  • Index Cond: ((codepersonne)::text = (ta_personne_rcs_dw.codepersonne)::text)
  • Filter: (((codeevt)::text = ANY ('{5997,5998}'::text[])) AND (btraite = 0))
  • Rows Removed by Filter: 0
13. 55.104 7,854.042 ↓ 4,785.0 4,785 123

Subquery Scan on v (cost=8.45..375,133.15 rows=1 width=516) (actual time=62.875..63.854 rows=4,785 loops=123)

14. 870.096 7,798.938 ↓ 4,785.0 4,785 123

HashSetOp Except (cost=8.45..375,133.14 rows=1 width=23) (actual time=62.875..63.406 rows=4,785 loops=123)

15. 403.402 6,928.842 ↑ 1.0 6,607,318 1

Append (cost=8.45..358,603.96 rows=6,611,672 width=23) (actual time=11.327..6,928.842 rows=6,607,318 loops=1)

16. 1.476 14.799 ↓ 16,446.0 16,446 1

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

17. 6.817 13.323 ↓ 16,446.0 16,446 1

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

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

  • Index Cond: (btraite = 0)
19. 578.046 6,510.641 ↑ 1.0 6,590,872 1

Subquery Scan on *SELECT* 2_1 (cost=226,362.07..358,595.49 rows=6,611,671 width=23) (actual time=4,111.718..6,510.641 rows=6,590,872 loops=1)

20. 3,952.993 5,932.595 ↑ 1.0 6,590,872 1

HashAggregate (cost=226,362.07..292,478.78 rows=6,611,671 width=23) (actual time=4,111.717..5,932.595 rows=6,590,872 loops=1)

  • Group Key: ta_pp_dw_1.codepp
21. 1,979.602 1,979.602 ↑ 1.0 6,593,687 1

Seq Scan on ta_pp_dw ta_pp_dw_1 (cost=0.00..209,832.89 rows=6,611,671 width=23) (actual time=0.009..1,979.602 rows=6,593,687 loops=1)

  • Filter: (btraite = ANY ('{1,2}'::integer[]))
  • Rows Removed by Filter: 17984
22. 0.001 10,374.230 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2 (cost=673,510.30..673,589.58 rows=3,964 width=399) (actual time=10,374.230..10,374.230 rows=0 loops=1)

23. 0.011 10,374.229 ↓ 0.0 0 1

HashAggregate (cost=673,510.30..673,549.94 rows=3,964 width=399) (actual time=10,374.229..10,374.229 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
24. 0.063 10,374.218 ↓ 0.0 0 1

Hash Join (cost=551,974.22..672,798.40 rows=18,984 width=399) (actual time=10,374.218..10,374.218 rows=0 loops=1)

  • Hash Cond: ((ta_personne_rcs_dw_1.codepersonne)::text = (thxd_1.codepersonne)::text)
25. 0.024 10,353.878 ↓ 0.0 0 1

Hash Join (cost=548,976.30..665,509.91 rows=24,482 width=10) (actual time=10,353.878..10,353.878 rows=0 loops=1)

  • Hash Cond: ((ta_personne_rcs_dw_1.codepp)::text = (v_1.codepp)::text)
26. 2,231.256 2,701.843 ↑ 4,896,370.0 1 1

HashAggregate (cost=173,834.55..222,798.25 rows=4,896,370 width=14) (actual time=2,701.843..2,701.843 rows=1 loops=1)

  • Group Key: ta_personne_rcs_dw_1.codepersonne, ta_personne_rcs_dw_1.codepp
27. 470.587 470.587 ↑ 1.0 4,841,768 1

Seq Scan on ta_personne_rcs_dw ta_personne_rcs_dw_1 (cost=0.00..149,352.70 rows=4,896,370 width=14) (actual time=0.004..470.587 rows=4,841,768 loops=1)

28. 0.001 7,652.011 ↓ 0.0 0 1

Hash (cost=375,141.74..375,141.74 rows=1 width=539) (actual time=7,652.011..7,652.011 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
29. 2.244 7,652.010 ↓ 0.0 0 1

Nested Loop (cost=9.01..375,141.74 rows=1 width=539) (actual time=7,652.010..7,652.010 rows=0 loops=1)

30. 0.755 7,611.486 ↓ 4,785.0 4,785 1

Subquery Scan on v_1 (cost=8.45..375,133.15 rows=1 width=516) (actual time=7,609.773..7,611.486 rows=4,785 loops=1)

31. 801.520 7,610.731 ↓ 4,785.0 4,785 1

HashSetOp Except (cost=8.45..375,133.14 rows=1 width=23) (actual time=7,609.772..7,610.731 rows=4,785 loops=1)

32. 375.986 6,809.211 ↑ 1.0 6,607,318 1

Append (cost=8.45..358,603.96 rows=6,611,672 width=23) (actual time=11.387..6,809.211 rows=6,607,318 loops=1)

33. 1.467 14.923 ↓ 16,446.0 16,446 1

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

34. 6.965 13.456 ↓ 16,446.0 16,446 1

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

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

  • Index Cond: (btraite = 0)
36. 578.734 6,418.302 ↑ 1.0 6,590,872 1

Subquery Scan on *SELECT* 2_2 (cost=226,362.07..358,595.49 rows=6,611,671 width=23) (actual time=4,051.755..6,418.302 rows=6,590,872 loops=1)

37. 3,893.106 5,839.568 ↑ 1.0 6,590,872 1

HashAggregate (cost=226,362.07..292,478.78 rows=6,611,671 width=23) (actual time=4,051.754..5,839.568 rows=6,590,872 loops=1)

  • Group Key: ta_pp_dw_3.codepp
38. 1,946.462 1,946.462 ↑ 1.0 6,593,687 1

Seq Scan on ta_pp_dw ta_pp_dw_3 (cost=0.00..209,832.89 rows=6,611,671 width=23) (actual time=0.010..1,946.462 rows=6,593,687 loops=1)

  • Filter: (btraite = ANY ('{1,2}'::integer[]))
  • Rows Removed by Filter: 17984
39. 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
40. 7.484 20.277 ↓ 2.0 28,427 1

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

  • Buckets: 32768 (originally 16384) Batches: 1 (originally 1) Memory Usage: 3117kB
41. 10.207 12.793 ↓ 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.820..12.793 rows=28,427 loops=1)

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

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