explain.depesz.com

PostgreSQL's explain analyze made readable

Result: tQgQ

Settings
# exclusive inclusive rows x rows loops node
1. 234.721 8,862.810 ↑ 30,680.0 10 1

GroupAggregate (cost=10,463,388.28..11,273,955.78 rows=306,800 width=197) (actual time=8,623.195..8,862.810 rows=10 loops=1)

  • Group Key: fin.lot, fin.lot_libl, "G_PARAM_GENERIC"."PARAM_VALUE", c.moment
2. 395.214 8,628.046 ↑ 90.0 142,805 1

Sort (cost=10,463,388.28..10,495,532.53 rows=12,857,702 width=196) (actual time=8,582.794..8,628.046 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
3. 190.411 8,232.832 ↑ 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=7,738.922..8,232.832 rows=142,805 loops=1)

4. 131.354 8,042.421 ↑ 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=7,738.863..8,042.421 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
5. 239.049 7,228.814 ↑ 3.5 142,805 1

Sort (cost=4,256,257.60..4,257,521.19 rows=505,436 width=180) (actual time=7,092.683..7,228.814 rows=142,805 loops=1)

  • Sort Key: fin.lot
  • Sort Method: external merge Disk: 19208kB
6. 87.814 6,989.765 ↑ 3.5 142,805 1

Hash Left Join (cost=1,634,576.56..4,118,536.69 rows=505,436 width=180) (actual time=6,905.797..6,989.765 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
7. 0.006 2,653.941 ↑ 1,534.0 10 1

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

8. 6.550 2,653.935 ↑ 1,534.0 10 1

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

  • Group Key: g_correspondance_refresh_1.lot, g_correspondance_refresh_1.libelle_flux, z_1.job
9. 207.631 2,647.385 ↑ 86.1 7,684 1

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

  • Hash Cond: ((z_1.job)::text = (g_correspondance_refresh_1.flux_fin)::text)
10. 2,439.703 2,439.703 ↑ 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.652..2,439.703 rows=654,585 loops=1)

  • Filter: (duration IS NOT NULL)
  • Rows Removed by Filter: 654585
11. 0.019 0.051 ↑ 13.0 10 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
12. 0.032 0.032 ↑ 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.024..0.032 rows=10 loops=1)

13. 88.103 4,248.010 ↑ 8.7 150,644 1

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

  • Buckets: 65536 Batches: 64 Memory Usage: 1833kB
14. 2,497.051 4,159.907 ↑ 8.7 150,644 1

Nested Loop (cost=0.00..1,546,597.11 rows=1,317,955 width=63) (actual time=0.139..4,159.907 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
15. 353.686 353.686 ↑ 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.012..353.686 rows=654,585 loops=1)

  • Filter: (duration IS NOT NULL)
  • Rows Removed by Filter: 654585
16. 1,309.162 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)

17. 0.008 0.008 ↑ 13.0 10 1

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

18. 36.136 682.253 ↓ 9.3 142,359 1

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

  • Sort Key: c.lot
  • Sort Method: quicksort Memory: 25kB
19. 0.004 646.117 ↑ 1,534.0 10 1

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

20. 77.373 646.113 ↑ 1,534.0 10 1

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

  • Group Key: g_correspondance_refresh_2.lot, g_correspondance_refresh_2.libelle_flux, z_2.job
21. 239.737 568.740 ↑ 4.6 142,960 1

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

  • Hash Cond: ((z_2.job)::text = (g_correspondance_refresh_2.flux_debut)::text)
22. 328.977 328.977 ↑ 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.018..328.977 rows=654,585 loops=1)

  • Filter: (duration IS NOT NULL)
  • Rows Removed by Filter: 654585
23. 0.018 0.026 ↑ 13.0 10 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
24. 0.008 0.008 ↑ 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.008 rows=10 loops=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)

26. 0.040 0.040 ↑ 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.039..0.040 rows=1 loops=1)

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

SubPlan (forGroupAggregate)

28. 0.023 0.023 ↑ 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.019..0.023 rows=10 loops=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.002..0.005 rows=10 loops=1)

30. 0.004 0.004 ↑ 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.002..0.004 rows=10 loops=1)

31. 0.007 0.007 ↑ 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.004..0.007 rows=10 loops=1)

32. 0.004 0.004 ↑ 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.004 rows=10 loops=1)