explain.depesz.com

PostgreSQL's explain analyze made readable

Result: NV0s

Settings
# exclusive inclusive rows x rows loops node
1. 103.377 2,797.532 ↑ 345.4 10 1

GroupAggregate (cost=165,412.50..165,711.72 rows=3,454 width=125) (actual time=2,685.495..2,797.532 rows=10 loops=1)

  • Group Key: fin.lot, fin.lot_libl, "G_PARAM_GENERIC"."PARAM_VALUE", c.moment
  • Buffers: shared hit=85610, temp read=816 written=816
2. 186.664 2,694.111 ↓ 17.9 61,660 1

Sort (cost=165,412.50..165,421.14 rows=3,454 width=124) (actual time=2,666.656..2,694.111 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=85605, temp read=816 written=816
3. 85.293 2,507.447 ↓ 17.9 61,660 1

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

  • Buffers: shared hit=85605
4. 45.037 2,422.154 ↓ 17.9 61,660 1

Hash Left Join (cost=66,554.34..165,158.04 rows=3,454 width=85) (actual time=488.930..2,422.154 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=85602
5. 51.647 2,108.416 ↓ 34.9 61,660 1

Hash Right Join (cost=32,980.88..131,163.62 rows=1,765 width=108) (actual time=220.196..2,108.416 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=57068
6. 1,123.981 1,838.673 ↓ 1.1 65,202 1

Nested Loop (cost=0.00..81,269.20 rows=59,818 width=50) (actual time=2.075..1,838.673 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. 144.662 144.662 ↑ 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.011..144.662 rows=285,015 loops=1)

  • Filter: (duration IS NOT NULL)
  • Rows Removed by Filter: 625
  • Buffers: shared hit=28533
8. 570.019 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.011 0.011 ↑ 1.0 10 1

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

  • Buffers: shared hit=1
10. 0.024 218.096 ↑ 118.0 10 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 18kB
  • Buffers: shared hit=28534
11. 0.006 218.072 ↑ 118.0 10 1

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

  • Buffers: shared hit=28534
12. 2.070 218.066 ↑ 118.0 10 1

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

  • Group Key: g_correspondance_refresh_1.lot, g_correspondance_refresh_1.libelle_flux, z_1.job
  • Buffers: shared hit=28534
13. 85.237 215.996 ↑ 7.1 3,387 1

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

  • Hash Cond: ((z_1.job)::text = (g_correspondance_refresh_1.flux_fin)::text)
  • Buffers: shared hit=28534
14. 130.742 130.742 ↑ 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.011..130.742 rows=285,015 loops=1)

  • Filter: (duration IS NOT NULL)
  • Rows Removed by Filter: 625
  • Buffers: shared hit=28533
15. 0.010 0.017 ↑ 1.0 10 1

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

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

  • Buffers: shared hit=1
17. 0.025 268.701 ↑ 118.0 10 1

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

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

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

  • Buffers: shared hit=28534
19. 33.086 268.671 ↑ 118.0 10 1

HashAggregate (cost=33,535.11..33,546.91 rows=1,180 width=61) (actual time=268.664..268.671 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. 105.251 235.585 ↓ 1.7 61,815 1

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

  • Hash Cond: ((z_2.job)::text = (g_correspondance_refresh_2.flux_debut)::text)
  • Buffers: shared hit=28534
21. 130.313 130.313 ↑ 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.005..130.313 rows=285,015 loops=1)

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

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

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

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

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

  • Buffers: shared hit=3
25. 0.023 0.023 ↑ 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=0.022..0.023 rows=1 loops=1)

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

SubPlan (forGroupAggregate)

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

  • Buffers: shared hit=1
28. 0.007 0.007 ↑ 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.007 rows=10 loops=1)

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

  • Buffers: shared hit=1
30. 0.007 0.007 ↑ 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.007 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