explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7yKd

Settings
# exclusive inclusive rows x rows loops node
1. 236.025 6,900.606 ↑ 30,680.0 10 1

GroupAggregate (cost=10,463,388.28..11,273,955.78 rows=306,800 width=197) (actual time=6,656.309..6,900.606 rows=10 loops=1)

  • Group Key: fin.lot, fin.lot_libl, "G_PARAM_GENERIC"."PARAM_VALUE", c.moment
  • Buffers: shared hit=85610, temp read=5660 written=5640
2. 396.787 6,664.538 ↑ 90.0 142,805 1

Sort (cost=10,463,388.28..10,495,532.53 rows=12,857,702 width=196) (actual time=6,615.008..6,664.538 rows=142,805 loops=1)

  • Sort Key: fin.lot, fin.lot_libl, "G_PARAM_GENERIC"."PARAM_VALUE", c.moment
  • Sort Method: external sort Disk: 15112kB
  • Buffers: shared hit=85605, temp read=5660 written=5640
3. 196.487 6,267.751 ↑ 90.0 142,805 1

Nested Loop Left Join (cost=4,314,978.20..5,253,573.50 rows=12,857,702 width=196) (actual time=5,754.932..6,267.751 rows=142,805 loops=1)

  • Buffers: shared hit=85605, temp read=3771 written=3751
4. 143.667 6,071.264 ↑ 90.0 142,805 1

Merge Left Join (cost=4,314,977.93..5,092,843.93 rows=12,857,702 width=157) (actual time=5,754.895..6,071.264 rows=142,805 loops=1)

  • Merge 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: 1087
  • Buffers: shared hit=85602, temp read=3771 written=3751
5. 244.460 5,242.580 ↑ 3.5 142,805 1

Sort (cost=4,256,257.60..4,257,521.19 rows=505,436 width=180) (actual time=5,105.390..5,242.580 rows=142,805 loops=1)

  • Sort Key: fin.lot
  • Sort Method: external merge Disk: 19208kB
  • Buffers: shared hit=57068, temp read=3771 written=3751
6. 89.766 4,998.120 ↑ 3.5 142,805 1

Hash Left Join (cost=1,634,576.56..4,118,536.69 rows=505,436 width=180) (actual time=4,912.359..4,998.120 rows=142,805 loops=1)

  • Hash Cond: ((fin.lot)::text = (g_correspondance_refresh.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: 7839
  • Buffers: shared hit=57068, temp read=1360 written=1340
7. 0.007 540.938 ↑ 1,534.0 10 1

Subquery Scan on fin (cost=57,347.01..57,653.81 rows=15,340 width=133) (actual time=540.896..540.938 rows=10 loops=1)

  • Buffers: shared hit=28534
8. 4.719 540.931 ↑ 1,534.0 10 1

HashAggregate (cost=57,347.01..57,500.41 rows=15,340 width=133) (actual time=540.894..540.931 rows=10 loops=1)

  • Group Key: g_correspondance_refresh_1.lot, g_correspondance_refresh_1.libelle_flux, z_1.job
  • Buffers: shared hit=28534
9. 200.427 536.212 ↑ 86.1 7,684 1

Hash Join (cost=12.93..50,731.79 rows=661,522 width=133) (actual time=0.093..536.212 rows=7,684 loops=1)

  • Hash Cond: ((z_1.job)::text = (g_correspondance_refresh_1.flux_fin)::text)
  • Buffers: shared hit=28534
10. 335.766 335.766 ↑ 1.0 654,585 1

Seq Scan on "STATS_SE1" z_1 (cost=0.00..41,622.94 rows=661,522 width=39) (actual time=0.011..335.766 rows=654,585 loops=1)

  • Filter: (duration IS NOT NULL)
  • Rows Removed by Filter: 654585
  • Buffers: shared hit=28533
11. 0.012 0.019 ↑ 13.0 10 1

Hash (cost=11.30..11.30 rows=130 width=232) (actual time=0.019..0.019 rows=10 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
12. 0.007 0.007 ↑ 13.0 10 1

Seq Scan on g_correspondance_refresh g_correspondance_refresh_1 (cost=0.00..11.30 rows=130 width=232) (actual time=0.005..0.007 rows=10 loops=1)

  • Buffers: shared hit=1
13. 96.457 4,367.416 ↑ 8.7 150,644 1

Hash (cost=1,546,597.11..1,546,597.11 rows=1,317,955 width=63) (actual time=4,367.416..4,367.416 rows=150,644 loops=1)

  • Buckets: 65536 Batches: 64 Memory Usage: 1833kB
  • Buffers: shared hit=28534, temp written=1320
14. 2,604.737 4,270.959 ↑ 8.7 150,644 1

Nested Loop (cost=0.00..1,546,597.11 rows=1,317,955 width=63) (actual time=0.141..4,270.959 rows=150,644 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: 6395206
  • Buffers: shared hit=28534
15. 357.052 357.052 ↑ 1.0 654,585 1

Seq Scan on "STATS_SE1" z (cost=0.00..41,622.94 rows=661,522 width=47) (actual time=0.016..357.052 rows=654,585 loops=1)

  • Filter: (duration IS NOT NULL)
  • Rows Removed by Filter: 654585
  • Buffers: shared hit=28533
16. 1,309.161 1,309.170 ↑ 13.0 10 654,585

Materialize (cost=0.00..11.95 rows=130 width=292) (actual time=0.000..0.002 rows=10 loops=654,585)

  • Buffers: shared hit=1
17. 0.009 0.009 ↑ 13.0 10 1

Seq Scan on g_correspondance_refresh (cost=0.00..11.30 rows=130 width=292) (actual time=0.005..0.009 rows=10 loops=1)

  • Buffers: shared hit=1
18. 35.570 685.017 ↓ 9.3 142,359 1

Sort (cost=58,720.33..58,758.68 rows=15,340 width=55) (actual time=649.485..685.017 rows=142,359 loops=1)

  • Sort Key: c.lot
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=28534
19. 0.007 649.447 ↑ 1,534.0 10 1

Subquery Scan on c (cost=57,347.01..57,653.81 rows=15,340 width=55) (actual time=649.419..649.447 rows=10 loops=1)

  • Buffers: shared hit=28534
20. 77.295 649.440 ↑ 1,534.0 10 1

HashAggregate (cost=57,347.01..57,500.41 rows=15,340 width=133) (actual time=649.417..649.440 rows=10 loops=1)

  • Group Key: g_correspondance_refresh_2.lot, g_correspondance_refresh_2.libelle_flux, z_2.job
  • Buffers: shared hit=28534
21. 245.206 572.145 ↑ 4.6 142,960 1

Hash Join (cost=12.93..50,731.79 rows=661,522 width=133) (actual time=0.069..572.145 rows=142,960 loops=1)

  • Hash Cond: ((z_2.job)::text = (g_correspondance_refresh_2.flux_debut)::text)
  • Buffers: shared hit=28534
22. 326.917 326.917 ↑ 1.0 654,585 1

Seq Scan on "STATS_SE1" z_2 (cost=0.00..41,622.94 rows=661,522 width=39) (actual time=0.019..326.917 rows=654,585 loops=1)

  • Filter: (duration IS NOT NULL)
  • Rows Removed by Filter: 654585
  • Buffers: shared hit=28533
23. 0.013 0.022 ↑ 13.0 10 1

Hash (cost=11.30..11.30 rows=130 width=232) (actual time=0.022..0.022 rows=10 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
24. 0.009 0.009 ↑ 13.0 10 1

Seq Scan on g_correspondance_refresh g_correspondance_refresh_2 (cost=0.00..11.30 rows=130 width=232) (actual time=0.006..0.009 rows=10 loops=1)

  • Buffers: shared hit=1
25. 0.000 0.000 ↑ 1.0 1 142,805

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

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

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

SubPlan (forGroupAggregate)

28. 0.022 0.022 ↑ 13.0 10 1

Seq Scan on g_correspondance_refresh g_correspondance_refresh_7 (cost=0.00..11.30 rows=130 width=138) (actual time=0.018..0.022 rows=10 loops=1)

  • Buffers: shared hit=1
29. 0.005 0.005 ↑ 13.0 10 1

Seq Scan on g_correspondance_refresh g_correspondance_refresh_6 (cost=0.00..11.30 rows=130 width=138) (actual time=0.003..0.005 rows=10 loops=1)

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

Seq Scan on g_correspondance_refresh g_correspondance_refresh_5 (cost=0.00..11.30 rows=130 width=138) (actual time=0.003..0.005 rows=10 loops=1)

  • Buffers: shared hit=1
31. 0.006 0.006 ↑ 13.0 10 1

Seq Scan on g_correspondance_refresh g_correspondance_refresh_4 (cost=0.00..11.30 rows=130 width=138) (actual time=0.003..0.006 rows=10 loops=1)

  • Buffers: shared hit=1
32. 0.005 0.005 ↑ 13.0 10 1

Seq Scan on g_correspondance_refresh g_correspondance_refresh_3 (cost=0.00..11.30 rows=130 width=138) (actual time=0.001..0.005 rows=10 loops=1)

  • Buffers: shared hit=1