explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9jPE

Settings
# exclusive inclusive rows x rows loops node
1. 103.959 12,208.690 ↑ 801.7 10 1

GroupAggregate (cost=290,168.42..290,855.49 rows=8,017 width=125) (actual time=12,096.200..12,208.690 rows=10 loops=1)

  • Group Key: fin.lot, fin.lot_libl, "G_PARAM_GENERIC"."PARAM_VALUE", c.moment
  • Buffers: shared hit=57076 read=28534 dirtied=28305 written=21666, temp read=816 written=816
2. 193.866 12,104.696 ↓ 7.7 61,660 1

Sort (cost=290,168.42..290,188.47 rows=8,017 width=124) (actual time=12,076.161..12,104.696 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=57071 read=28534 dirtied=28305 written=21666, temp read=816 written=816
3. 30.709 11,910.830 ↓ 7.7 61,660 1

Nested Loop Left Join (cost=91,905.32..289,648.57 rows=8,017 width=124) (actual time=9,688.194..11,910.830 rows=61,660 loops=1)

  • Buffers: shared hit=57071 read=28534 dirtied=28305 written=21666
4. 52.454 11,818.461 ↓ 7.7 61,660 1

Hash Left Join (cost=91,905.05..289,540.06 rows=8,017 width=85) (actual time=9,685.547..11,818.461 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=57069 read=28533 dirtied=28305 written=21666
5. 57.175 11,457.637 ↓ 15.1 61,660 1

Hash Right Join (cost=45,264.44..241,922.32 rows=4,097 width=108) (actual time=9,377.137..11,457.637 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=28535 read=28533 dirtied=28305 written=21666
6. 1,014.553 2,025.412 ↑ 2.1 65,202 1

Nested Loop (cost=0.00..157,390.42 rows=138,877 width=50) (actual time=2.059..2,025.412 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. 155.814 155.814 ↑ 2.3 285,015 1

Seq Scan on "STATS_SE1" z (cost=0.00..41,622.94 rows=661,522 width=47) (actual time=0.010..155.814 rows=285,015 loops=1)

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

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

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

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

  • Buffers: shared hit=1
10. 0.018 9,375.050 ↑ 118.0 10 1

Hash (cost=45,249.69..45,249.69 rows=1,180 width=61) (actual time=9,375.050..9,375.050 rows=10 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 18kB
  • Buffers: shared hit=1 read=28533 dirtied=28305 written=21666
11. 0.010 9,375.032 ↑ 118.0 10 1

Subquery Scan on fin (cost=45,226.09..45,249.69 rows=1,180 width=61) (actual time=9,375.019..9,375.032 rows=10 loops=1)

  • Buffers: shared hit=1 read=28533 dirtied=28305 written=21666
12. 3.718 9,375.022 ↑ 118.0 10 1

HashAggregate (cost=45,226.09..45,237.89 rows=1,180 width=61) (actual time=9,375.016..9,375.022 rows=10 loops=1)

  • Group Key: g_correspondance_refresh_1.lot, g_correspondance_refresh_1.libelle_flux, z_1.job
  • Buffers: shared hit=1 read=28533 dirtied=28305 written=21666
13. 106.006 9,371.304 ↑ 16.6 3,387 1

Hash Join (cost=1.23..44,665.48 rows=56,061 width=61) (actual time=5,488.665..9,371.304 rows=3,387 loops=1)

  • Hash Cond: ((z_1.job)::text = (g_correspondance_refresh_1.flux_fin)::text)
  • Buffers: shared hit=1 read=28533 dirtied=28305 written=21666
14. 9,265.272 9,265.272 ↑ 2.3 285,015 1

Seq Scan on "STATS_SE1" z_1 (cost=0.00..41,622.94 rows=661,522 width=39) (actual time=7.758..9,265.272 rows=285,015 loops=1)

  • Filter: (duration IS NOT NULL)
  • Rows Removed by Filter: 625
  • Buffers: shared read=28533 dirtied=28305 written=21666
15. 0.016 0.026 ↑ 1.0 10 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
16. 0.010 0.010 ↑ 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=0.007..0.010 rows=10 loops=1)

  • Buffers: shared hit=1
17. 0.023 308.370 ↑ 118.0 10 1

Hash (cost=46,625.86..46,625.86 rows=1,180 width=42) (actual time=308.370..308.370 rows=10 loops=1)

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

Subquery Scan on c (cost=46,602.25..46,625.86 rows=1,180 width=42) (actual time=308.335..308.347 rows=10 loops=1)

  • Buffers: shared hit=28534
19. 36.437 308.339 ↑ 118.0 10 1

HashAggregate (cost=46,602.25..46,614.06 rows=1,180 width=61) (actual time=308.334..308.339 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. 125.723 271.902 ↑ 1.4 61,815 1

Hash Join (cost=1.23..45,767.01 rows=83,524 width=61) (actual time=1.102..271.902 rows=61,815 loops=1)

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

Seq Scan on "STATS_SE1" z_2 (cost=0.00..41,622.94 rows=661,522 width=39) (actual time=0.006..146.157 rows=285,015 loops=1)

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
23. 0.004 0.004 ↑ 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.004 rows=10 loops=1)

  • Buffers: shared hit=1
24. 59.034 61.660 ↑ 1.0 1 61,660

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

  • Buffers: shared hit=2 read=1
25. 2.626 2.626 ↑ 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=2.625..2.626 rows=1 loops=1)

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

SubPlan (forGroupAggregate)

27. 0.015 0.015 ↑ 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.009..0.015 rows=10 loops=1)

  • Buffers: shared hit=1
28. 0.005 0.005 ↑ 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.005 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.004 0.004 ↑ 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.001..0.004 rows=10 loops=1)

  • Buffers: shared hit=1