explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Nu7l

Settings
# exclusive inclusive rows x rows loops node
1. 104.215 5,287.676 ↑ 345.4 10 1

GroupAggregate (cost=165,412.50..165,711.72 rows=3,454 width=125) (actual time=5,174.929..5,287.676 rows=10 loops=1)

  • Group Key: fin.lot, fin.lot_libl, "G_PARAM_GENERIC"."PARAM_VALUE", c.moment
  • Buffers: shared hit=57106 read=28504 written=6769, temp read=816 written=816
2. 1,485.429 5,183.428 ↓ 17.9 61,660 1

Sort (cost=165,412.50..165,421.14 rows=3,454 width=124) (actual time=5,144.502..5,183.428 rows=61,660 loops=1)

  • Sort Key: fin.lot, fin.lot_libl, "G_PARAM_GENERIC"."PARAM_VALUE", c.moment
  • Sort Method: external merge Disk: 6520kB
  • Buffers: shared hit=57101 read=28504 written=6769, temp read=816 written=816
3. 138.256 3,697.999 ↓ 17.9 61,660 1

Nested Loop Left Join (cost=66,554.62..165,209.51 rows=3,454 width=124) (actual time=1,261.825..3,697.999 rows=61,660 loops=1)

  • Buffers: shared hit=57101 read=28504 written=6769
4. 49.444 3,004.803 ↓ 17.9 61,660 1

Hash Left Join (cost=66,554.34..165,158.04 rows=3,454 width=85) (actual time=727.923..3,004.803 rows=61,660 loops=1)

  • Hash Cond: ((fin.lot)::text = (c.lot)::text)
  • Join Filter: (((fin.job)::text <> (c.job)::text) AND (fin.moment_fin <= c.moment))
  • Rows Removed by Join Filter: 501
  • Buffers: shared hit=57100 read=28502 written=6769
5. 54.255 2,674.295 ↓ 34.9 61,660 1

Hash Right Join (cost=32,980.88..131,163.62 rows=1,765 width=108) (actual time=446.823..2,674.295 rows=61,660 loops=1)

  • Hash Cond: ((g_correspondance_refresh.lot)::text = (fin.lot)::text)
  • Join Filter: (CASE WHEN (((fin.job)::text <> (z.job)::text) AND (fin.moment_fin >= z.moment)) THEN 1 WHEN (((fin.job)::text = (z.job)::text) AND (fin.moment_fin = z.moment)) THEN 1 ELSE 0 END = 1)
  • Rows Removed by Join Filter: 3542
  • Buffers: shared hit=28566 read=28502 written=6769
6. 1,450.849 2,175.257 ↓ 1.1 65,202 1

Nested Loop (cost=0.00..81,269.20 rows=59,818 width=50) (actual time=2.015..2,175.257 rows=65,202 loops=1)

  • Join Filter: (((z.job)::text = (g_correspondance_refresh.flux_fin)::text) OR ((z.job)::text = (g_correspondance_refresh.flux_debut)::text))
  • Rows Removed by Join Filter: 2784948
  • Buffers: shared hit=28534
7. 154.378 154.378 ↑ 1.0 285,015 1

Seq Scan on "STATS_SE1" z (cost=0.00..31,389.40 rows=285,021 width=47) (actual time=0.009..154.378 rows=285,015 loops=1)

  • Filter: (duration IS NOT NULL)
  • Rows Removed by Filter: 625
  • Buffers: shared hit=28533
8. 570.022 570.030 ↑ 1.0 10 285,015

Materialize (cost=0.00..1.15 rows=10 width=74) (actual time=0.000..0.002 rows=10 loops=285,015)

  • Buffers: shared hit=1
9. 0.008 0.008 ↑ 1.0 10 1

Seq Scan on g_correspondance_refresh (cost=0.00..1.10 rows=10 width=74) (actual time=0.003..0.008 rows=10 loops=1)

  • Buffers: shared hit=1
10. 0.020 444.783 ↑ 118.0 10 1

Hash (cost=32,966.13..32,966.13 rows=1,180 width=61) (actual time=444.783..444.783 rows=10 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 18kB
  • Buffers: shared hit=32 read=28502 written=6769
11. 0.011 444.763 ↑ 118.0 10 1

Subquery Scan on fin (cost=32,942.53..32,966.13 rows=1,180 width=61) (actual time=444.749..444.763 rows=10 loops=1)

  • Buffers: shared hit=32 read=28502 written=6769
12. 2.486 444.752 ↑ 118.0 10 1

HashAggregate (cost=32,942.53..32,954.33 rows=1,180 width=61) (actual time=444.748..444.752 rows=10 loops=1)

  • Group Key: g_correspondance_refresh_1.lot, g_correspondance_refresh_1.libelle_flux, z_1.job
  • Buffers: shared hit=32 read=28502 written=6769
13. 93.118 442.266 ↑ 7.1 3,387 1

Hash Join (cost=1.23..32,700.99 rows=24,154 width=61) (actual time=125.636..442.266 rows=3,387 loops=1)

  • Hash Cond: ((z_1.job)::text = (g_correspondance_refresh_1.flux_fin)::text)
  • Buffers: shared hit=32 read=28502 written=6769
14. 347.464 347.464 ↑ 1.0 285,015 1

Seq Scan on "STATS_SE1" z_1 (cost=0.00..31,389.40 rows=285,021 width=39) (actual time=0.027..347.464 rows=285,015 loops=1)

  • Filter: (duration IS NOT NULL)
  • Rows Removed by Filter: 625
  • Buffers: shared hit=32 read=28501 written=6769
15. 0.016 1.684 ↑ 1.0 10 1

Hash (cost=1.10..1.10 rows=10 width=64) (actual time=1.684..1.684 rows=10 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared read=1
16. 1.668 1.668 ↑ 1.0 10 1

Seq Scan on g_correspondance_refresh g_correspondance_refresh_1 (cost=0.00..1.10 rows=10 width=64) (actual time=1.663..1.668 rows=10 loops=1)

  • Buffers: shared read=1
17. 0.029 281.064 ↑ 118.0 10 1

Hash (cost=33,558.71..33,558.71 rows=1,180 width=42) (actual time=281.064..281.064 rows=10 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 17kB
  • Buffers: shared hit=28534
18. 0.005 281.035 ↑ 118.0 10 1

Subquery Scan on c (cost=33,535.11..33,558.71 rows=1,180 width=42) (actual time=281.025..281.035 rows=10 loops=1)

  • Buffers: shared hit=28534
19. 37.181 281.030 ↑ 118.0 10 1

HashAggregate (cost=33,535.11..33,546.91 rows=1,180 width=61) (actual time=281.024..281.030 rows=10 loops=1)

  • Group Key: g_correspondance_refresh_2.lot, g_correspondance_refresh_2.libelle_flux, z_2.job
  • Buffers: shared hit=28534
20. 111.759 243.849 ↓ 1.7 61,815 1

Hash Join (cost=1.23..33,175.42 rows=35,969 width=61) (actual time=1.062..243.849 rows=61,815 loops=1)

  • Hash Cond: ((z_2.job)::text = (g_correspondance_refresh_2.flux_debut)::text)
  • Buffers: shared hit=28534
21. 132.071 132.071 ↑ 1.0 285,015 1

Seq Scan on "STATS_SE1" z_2 (cost=0.00..31,389.40 rows=285,021 width=39) (actual time=0.006..132.071 rows=285,015 loops=1)

  • Filter: (duration IS NOT NULL)
  • Rows Removed by Filter: 625
  • Buffers: shared hit=28533
22. 0.013 0.019 ↑ 1.0 10 1

Hash (cost=1.10..1.10 rows=10 width=51) (actual time=0.019..0.019 rows=10 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
23. 0.006 0.006 ↑ 1.0 10 1

Seq Scan on g_correspondance_refresh g_correspondance_refresh_2 (cost=0.00..1.10 rows=10 width=51) (actual time=0.002..0.006 rows=10 loops=1)

  • Buffers: shared hit=1
24. 21.048 554.940 ↑ 1.0 1 61,660

Materialize (cost=0.28..8.30 rows=1 width=39) (actual time=0.009..0.009 rows=1 loops=61,660)

  • Buffers: shared hit=1 read=2
25. 533.892 533.892 ↑ 1.0 1 1

Index Scan using "G_PARAM_GENERIC_pkey" on "G_PARAM_GENERIC" (cost=0.28..8.29 rows=1 width=39) (actual time=533.888..533.892 rows=1 loops=1)

  • Index Cond: ((("SRC_ENV")::text = 'PDSE1'::text) AND (("PARAM_CODE")::text = 'REFRESH_TIME'::text))
  • Buffers: shared hit=1 read=2
26.          

SubPlan (forGroupAggregate)

27. 0.014 0.014 ↑ 1.0 10 1

Seq Scan on g_correspondance_refresh g_correspondance_refresh_7 (cost=0.00..1.10 rows=10 width=29) (actual time=0.011..0.014 rows=10 loops=1)

  • Buffers: shared hit=1
28. 0.003 0.003 ↑ 1.0 10 1

Seq Scan on g_correspondance_refresh g_correspondance_refresh_6 (cost=0.00..1.10 rows=10 width=29) (actual time=0.002..0.003 rows=10 loops=1)

  • Buffers: shared hit=1
29. 0.006 0.006 ↑ 1.0 10 1

Seq Scan on g_correspondance_refresh g_correspondance_refresh_5 (cost=0.00..1.10 rows=10 width=29) (actual time=0.002..0.006 rows=10 loops=1)

  • Buffers: shared hit=1
30. 0.005 0.005 ↑ 1.0 10 1

Seq Scan on g_correspondance_refresh g_correspondance_refresh_4 (cost=0.00..1.10 rows=10 width=29) (actual time=0.003..0.005 rows=10 loops=1)

  • Buffers: shared hit=1
31. 0.005 0.005 ↑ 1.0 10 1

Seq Scan on g_correspondance_refresh g_correspondance_refresh_3 (cost=0.00..1.10 rows=10 width=29) (actual time=0.002..0.005 rows=10 loops=1)

  • Buffers: shared hit=1