explain.depesz.com

PostgreSQL's explain analyze made readable

Result: c6B7

Settings
# exclusive inclusive rows x rows loops node
1. 103.656 2,965.546 ↑ 345.4 10 1

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

  • Output: fin.lot_libl, CASE WHEN (c.moment IS NOT NULL) THEN c.moment ELSE max(CASE WHEN (hashed SubPlan 1) THEN z.moment ELSE NULL::timestamp without time zone END) END, "G_PARAM_GENERIC"."PARAM_VALUE", max(CASE WHEN (hashed SubPlan 2)
  • Group Key: fin.lot, fin.lot_libl, "G_PARAM_GENERIC"."PARAM_VALUE", c.moment
  • Buffers: shared hit=85610, temp read=816 written=816
2. 182.979 2,861.855 ↓ 17.9 61,661 1

Sort (cost=165,412.50..165,421.14 rows=3,454 width=124) (actual time=2,833.647..2,861.855 rows=61,661 loops=1)

  • Output: fin.lot_libl, "G_PARAM_GENERIC"."PARAM_VALUE", fin.lot, c.moment, z.job, z.moment, fin.moment_fin, z.duration
  • 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. 88.686 2,678.876 ↓ 17.9 61,661 1

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

  • Output: fin.lot_libl, "G_PARAM_GENERIC"."PARAM_VALUE", fin.lot, c.moment, z.job, z.moment, fin.moment_fin, z.duration
  • Buffers: shared hit=85605
4. 45.868 2,590.190 ↓ 17.9 61,661 1

Hash Left Join (cost=66,554.34..165,158.04 rows=3,454 width=85) (actual time=499.050..2,590.190 rows=61,661 loops=1)

  • Output: fin.lot_libl, fin.moment_fin, fin.lot, z.job, z.moment, z.duration, c.moment
  • 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: 502
  • Buffers: shared hit=85602
5. 56.980 2,267.064 ↓ 34.9 61,661 1

Hash Right Join (cost=32,980.88..131,163.62 rows=1,765 width=108) (actual time=221.762..2,267.064 rows=61,661 loops=1)

  • Output: fin.lot_libl, fin.moment_fin, fin.lot, fin.job, z.job, z.moment, z.duration
  • 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: 3543
  • Buffers: shared hit=57068
6. 985.986 1,988.383 ↓ 1.1 65,204 1

Nested Loop (cost=0.00..81,269.20 rows=59,818 width=50) (actual time=0.039..1,988.383 rows=65,204 loops=1)

  • Output: z.job, z.moment, z.duration, g_correspondance_refresh.lot
  • 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: 2785186
  • Buffers: shared hit=28534
7. 147.280 147.280 ↓ 1.0 285,039 1

Seq Scan on talend_fi."STATS_SE1" z (cost=0.00..31,389.40 rows=285,021 width=47) (actual time=0.008..147.280 rows=285,039 loops=1)

  • Output: z.moment, z.pid, z.father_pid, z.root_pid, z.system_pid, z.project, z.job, z.job_repository_id, z.job_version, z.context, z.origin, z.message_type, z.message, z.duration
  • Filter: (z.duration IS NOT NULL)
  • Rows Removed by Filter: 650
  • Buffers: shared hit=28533
8. 855.109 855.117 ↑ 1.0 10 285,039

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

  • Output: g_correspondance_refresh.flux_fin, g_correspondance_refresh.flux_debut, g_correspondance_refresh.lot
  • Buffers: shared hit=1
9. 0.008 0.008 ↑ 1.0 10 1

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

  • Output: g_correspondance_refresh.flux_fin, g_correspondance_refresh.flux_debut, g_correspondance_refresh.lot
  • Buffers: shared hit=1
10. 0.027 221.701 ↑ 118.0 10 1

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

  • Output: fin.lot_libl, fin.moment_fin, fin.lot, fin.job
  • Buckets: 2048 Batches: 1 Memory Usage: 18kB
  • Buffers: shared hit=28534
11. 0.006 221.674 ↑ 118.0 10 1

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

  • Output: fin.lot_libl, fin.moment_fin, fin.lot, fin.job
  • Buffers: shared hit=28534
12. 2.126 221.668 ↑ 118.0 10 1

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

  • Output: g_correspondance_refresh_1.lot, g_correspondance_refresh_1.libelle_flux, z_1.job, max(z_1.moment)
  • Group Key: g_correspondance_refresh_1.lot, g_correspondance_refresh_1.libelle_flux, z_1.job
  • Buffers: shared hit=28534
13. 86.788 219.542 ↑ 7.1 3,388 1

Hash Join (cost=1.23..32,700.99 rows=24,154 width=61) (actual time=0.039..219.542 rows=3,388 loops=1)

  • Output: g_correspondance_refresh_1.lot, g_correspondance_refresh_1.libelle_flux, z_1.job, z_1.moment
  • Hash Cond: ((z_1.job)::text = (g_correspondance_refresh_1.flux_fin)::text)
  • Buffers: shared hit=28534
14. 132.735 132.735 ↓ 1.0 285,039 1

Seq Scan on talend_fi."STATS_SE1" z_1 (cost=0.00..31,389.40 rows=285,021 width=39) (actual time=0.011..132.735 rows=285,039 loops=1)

  • Output: z_1.moment, z_1.pid, z_1.father_pid, z_1.root_pid, z_1.system_pid, z_1.project, z_1.job, z_1.job_repository_id, z_1.job_version, z_1.context, z_1.origin, z_1.message_type, z
  • Filter: (z_1.duration IS NOT NULL)
  • Rows Removed by Filter: 650
  • Buffers: shared hit=28533
15. 0.011 0.019 ↑ 1.0 10 1

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

  • Output: g_correspondance_refresh_1.lot, g_correspondance_refresh_1.libelle_flux, g_correspondance_refresh_1.flux_fin
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
16. 0.008 0.008 ↑ 1.0 10 1

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

  • Output: g_correspondance_refresh_1.lot, g_correspondance_refresh_1.libelle_flux, g_correspondance_refresh_1.flux_fin
  • Buffers: shared hit=1
17. 0.025 277.258 ↑ 118.0 10 1

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

  • Output: c.moment, c.job, c.lot
  • Buckets: 2048 Batches: 1 Memory Usage: 17kB
  • Buffers: shared hit=28534
18. 0.006 277.233 ↑ 118.0 10 1

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

  • Output: c.moment, c.job, c.lot
  • Buffers: shared hit=28534
19. 34.041 277.227 ↑ 118.0 10 1

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

  • Output: g_correspondance_refresh_2.lot, g_correspondance_refresh_2.libelle_flux, z_2.job, max(z_2.moment)
  • Group Key: g_correspondance_refresh_2.lot, g_correspondance_refresh_2.libelle_flux, z_2.job
  • Buffers: shared hit=28534
20. 108.292 243.186 ↓ 1.7 61,816 1

Hash Join (cost=1.23..33,175.42 rows=35,969 width=61) (actual time=1.994..243.186 rows=61,816 loops=1)

  • Output: g_correspondance_refresh_2.lot, g_correspondance_refresh_2.libelle_flux, z_2.job, z_2.moment
  • Hash Cond: ((z_2.job)::text = (g_correspondance_refresh_2.flux_debut)::text)
  • Buffers: shared hit=28534
21. 134.879 134.879 ↓ 1.0 285,039 1

Seq Scan on talend_fi."STATS_SE1" z_2 (cost=0.00..31,389.40 rows=285,021 width=39) (actual time=0.003..134.879 rows=285,039 loops=1)

  • Output: z_2.moment, z_2.pid, z_2.father_pid, z_2.root_pid, z_2.system_pid, z_2.project, z_2.job, z_2.job_repository_id, z_2.job_version, z_2.context, z_2.origin, z_2.message_type, z_2.mes
  • Filter: (z_2.duration IS NOT NULL)
  • Rows Removed by Filter: 650
  • Buffers: shared hit=28533
22. 0.011 0.015 ↑ 1.0 10 1

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

  • Output: g_correspondance_refresh_2.lot, g_correspondance_refresh_2.libelle_flux, g_correspondance_refresh_2.flux_debut
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
23. 0.004 0.004 ↑ 1.0 10 1

Seq Scan on talend_fi.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)

  • Output: g_correspondance_refresh_2.lot, g_correspondance_refresh_2.libelle_flux, g_correspondance_refresh_2.flux_debut
  • Buffers: shared hit=1
24. 0.000 0.000 ↑ 1.0 1 61,661

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

  • Output: "G_PARAM_GENERIC"."PARAM_VALUE
  • Buffers: shared hit=3
25. 0.024 0.024 ↑ 1.0 1 1

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

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

SubPlan (forGroupAggregate)

27. 0.018 0.018 ↑ 1.0 10 1

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

  • Output: g_correspondance_refresh_7.flux_debut
  • Buffers: shared hit=1
28. 0.004 0.004 ↑ 1.0 10 1

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

  • Output: g_correspondance_refresh_6.flux_debut
  • Buffers: shared hit=1
29. 0.003 0.003 ↑ 1.0 10 1

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

  • Output: g_correspondance_refresh_5.flux_debut
  • Buffers: shared hit=1
30. 0.006 0.006 ↑ 1.0 10 1

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

  • Output: g_correspondance_refresh_4.flux_debut
  • Buffers: shared hit=1
31. 0.004 0.004 ↑ 1.0 10 1

Seq Scan on talend_fi.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)

  • Output: g_correspondance_refresh_3.flux_debut
  • Buffers: shared hit=1