explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DKFSF

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 643.235 ↓ 0.0 0 1

Nested Loop Left Join (cost=797,768.12..812,988.52 rows=94 width=92) (actual time=643.235..643.235 rows=0 loops=1)

  • Join Filter: ((inventaire2.magasin_id = selectiondatemag.magasin_id) AND (inventaire2.ga_id = groupe_article.id) AND (inventaire2.date_inventaire = date((casse.date - '10:15:00'::interval))))
2.          

CTE selectiondatemag

3. 0.000 643.230 ↓ 0.0 0 1

Unique (cost=796,352.43..796,352.44 rows=2 width=12) (actual time=643.230..643.230 rows=0 loops=1)

4. 0.058 643.230 ↓ 0.0 0 1

Sort (cost=796,352.43..796,352.43 rows=2 width=12) (actual time=643.230..643.230 rows=0 loops=1)

  • Sort Key: (date((cas.date - '10:15:00'::interval))), scas.magasin_id
  • Sort Method: quicksort Memory: 25kB
5. 0.002 643.172 ↓ 0.0 0 1

Append (cost=389,605.94..796,352.42 rows=2 width=12) (actual time=643.172..643.172 rows=0 loops=1)

6. 0.000 2.165 ↓ 0.0 0 1

Unique (cost=389,605.94..389,605.95 rows=1 width=12) (actual time=2.165..2.165 rows=0 loops=1)

7. 0.007 2.165 ↓ 0.0 0 1

Sort (cost=389,605.94..389,605.95 rows=1 width=12) (actual time=2.165..2.165 rows=0 loops=1)

  • Sort Key: (date((cas.date - '10:15:00'::interval))), scas.magasin_id
  • Sort Method: quicksort Memory: 25kB
8. 0.000 2.158 ↓ 0.0 0 1

Nested Loop (cost=8.71..389,605.93 rows=1 width=12) (actual time=2.158..2.158 rows=0 loops=1)

  • Join Filter: (lscas.stock_id = scas.id)
9. 0.000 2.158 ↓ 0.0 0 1

Nested Loop (cost=8.71..389,432.54 rows=1 width=16) (actual time=2.158..2.158 rows=0 loops=1)

10. 0.029 2.158 ↓ 0.0 0 1

Hash Semi Join (cost=8.28..389,432.07 rows=1 width=16) (actual time=2.158..2.158 rows=0 loops=1)

  • Hash Cond: (cas.id = cca.id_src)
  • Join Filter: ((((cca.statut_etl)::text = '1'::text) AND ((date((cas.date - '10:15:00'::interval)) + '7 days'::interval) > now())) OR ((cca.statut_etl_reprise)::text = '1'::text))
11. 0.022 0.022 ↑ 12,435,231.0 1 1

Seq Scan on casse cas (cost=0.00..356,781.31 rows=12,435,231 width=24) (actual time=0.021..0.022 rows=1 loops=1)

12. 0.002 2.107 ↓ 0.0 0 1

Hash (cost=8.27..8.27 rows=1 width=8) (actual time=2.106..2.107 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
13. 2.105 2.105 ↓ 0.0 0 1

Index Scan using cdc_casse_id_src_idx1 on cdc_casse cca (cost=0.25..8.27 rows=1 width=8) (actual time=2.105..2.105 rows=0 loops=1)

  • Filter: (((statut_etl)::text = '1'::text) OR ((statut_etl_reprise)::text = '1'::text))
  • Rows Removed by Filter: 5
14. 0.000 0.000 ↓ 0.0 0

Index Scan using ligne_stock_pkey on ligne_stock lscas (cost=0.43..0.47 rows=1 width=16) (never executed)

  • Index Cond: (id = cas.ligne_stock_id)
15. 0.000 0.000 ↓ 0.0 0

Seq Scan on stock scas (cost=0.00..137.06 rows=2,906 width=16) (never executed)

16. 0.000 641.005 ↓ 0.0 0 1

Unique (cost=406,746.43..406,746.44 rows=1 width=12) (actual time=641.005..641.005 rows=0 loops=1)

17. 0.010 641.005 ↓ 0.0 0 1

Sort (cost=406,746.43..406,746.43 rows=1 width=12) (actual time=641.004..641.005 rows=0 loops=1)

  • Sort Key: (date((cas_1.date - '10:15:00'::interval))), scas_1.magasin_id
  • Sort Method: quicksort Memory: 25kB
18. 0.001 640.995 ↓ 0.0 0 1

Nested Loop (cost=149,854.88..406,746.42 rows=1 width=12) (actual time=640.995..640.995 rows=0 loops=1)

19. 0.000 640.994 ↓ 0.0 0 1

Nested Loop (cost=149,854.60..406,746.11 rows=1 width=16) (actual time=640.994..640.994 rows=0 loops=1)

20. 0.014 640.994 ↓ 0.0 0 1

Hash Semi Join (cost=149,854.17..406,745.63 rows=1 width=16) (actual time=640.993..640.994 rows=0 loops=1)

  • Hash Cond: (cas_1.id = cca_1.id_src)
  • Join Filter: ((((cca_1.statut_etl)::text = '1'::text) AND ((date((cas_1.date - '10:15:00'::interval)) + '7 days'::interval) > now())) OR ((cca_1.statut_etl_reprise)::text = '1'::text))
21. 0.012 0.012 ↑ 6,861,898.0 1 1

Seq Scan on don cas_1 (cost=0.00..238,878.98 rows=6,861,898 width=24) (actual time=0.012..0.012 rows=1 loops=1)

22. 0.000 640.968 ↓ 0.0 0 1

Hash (cost=149,854.16..149,854.16 rows=1 width=8) (actual time=640.967..640.968 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
23. 18.228 654.595 ↓ 0.0 0 1

Gather (cost=1,000.00..149,854.16 rows=1 width=8) (actual time=640.966..654.595 rows=0 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
24. 636.367 636.367 ↓ 0.0 0 3

Parallel Seq Scan on cdc_don cca_1 (cost=0.00..148,854.06 rows=1 width=8) (actual time=636.367..636.367 rows=0 loops=3)

  • Filter: (((statut_etl)::text = '1'::text) OR ((statut_etl_reprise)::text = '1'::text))
  • Rows Removed by Filter: 3604459
25. 0.000 0.000 ↓ 0.0 0

Index Scan using ligne_stock_pkey on ligne_stock lscas_1 (cost=0.43..0.48 rows=1 width=16) (never executed)

  • Index Cond: (id = cas_1.ligne_stock_id)
26. 0.000 0.000 ↓ 0.0 0

Index Scan using stock_pkey on stock scas_1 (cost=0.28..0.30 rows=1 width=16) (never executed)

  • Index Cond: (id = lscas_1.stock_id)
27.          

CTE inventaire2

28. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=1.27..1,413.97 rows=1 width=45) (never executed)

29. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.85..1,413.49 rows=1 width=45) (never executed)

  • Join Filter: (selectiondatemag_1.magasin_id = im.magasin_id)
30. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.42..732.93 rows=1,421 width=37) (never executed)

31. 0.000 0.000 ↓ 0.0 0

CTE Scan on selectiondatemag selectiondatemag_1 (cost=0.00..0.04 rows=2 width=12) (never executed)

32. 0.000 0.000 ↓ 0.0 0

Index Scan using inventaire_date_inventaire_idx on inventaire inv (cost=0.42..359.35 rows=710 width=29) (never executed)

  • Index Cond: (date_inventaire = selectiondatemag_1.date_casse)
  • Filter: (((statut)::text = 'TERMINE'::text) AND ((type)::text = 'COMPTABLE'::text))
33. 0.000 0.000 ↓ 0.0 0

Index Only Scan using join_inventaire_magasin_pkey on join_inventaire_magasin im (cost=0.42..0.47 rows=1 width=16) (never executed)

  • Index Cond: (inventaire_id = inv.id)
  • Heap Fetches: 0
34. 0.000 0.000 ↓ 0.0 0

Index Only Scan using join_inventaire_groupe_article_pkey on join_inventaire_groupe_article iga (cost=0.42..0.47 rows=1 width=16) (never executed)

  • Index Cond: (inventaire_id = inv.id)
  • Heap Fetches: 0
35. 0.001 643.235 ↓ 0.0 0 1

Nested Loop (cost=1.71..15,217.64 rows=94 width=77) (actual time=643.235..643.235 rows=0 loops=1)

  • Join Filter: (groupe_article.rayon_id = rayon.id)
36. 0.000 643.234 ↓ 0.0 0 1

Nested Loop (cost=1.71..15,209.13 rows=94 width=82) (actual time=643.234..643.234 rows=0 loops=1)

  • Join Filter: (article.groupe_article_id = groupe_article.id)
37. 0.002 643.236 ↓ 0.0 0 1

Nested Loop (cost=1.71..15,175.68 rows=94 width=74) (actual time=643.234..643.236 rows=0 loops=1)

  • Join Filter: (article.unite_detail_id = unite_detail.id)
38. 0.000 643.234 ↓ 0.0 0 1

Nested Loop (cost=1.71..15,171.11 rows=94 width=77) (actual time=643.234..643.234 rows=0 loops=1)

39. 0.001 643.234 ↓ 0.0 0 1

Nested Loop (cost=1.42..15,142.23 rows=94 width=58) (actual time=643.234..643.234 rows=0 loops=1)

  • Join Filter: (selectiondatemag.date_casse = date((casse.date - '10:15:00'::interval)))
40. 0.000 643.233 ↓ 0.0 0 1

Nested Loop (cost=0.99..468.65 rows=1,991 width=39) (actual time=643.233..643.233 rows=0 loops=1)

41. 0.001 643.233 ↓ 0.0 0 1

Nested Loop (cost=0.56..18.07 rows=4 width=25) (actual time=643.233..643.233 rows=0 loops=1)

  • Join Filter: (selectiondatemag.magasin_id = stock.magasin_id)
42. 0.000 643.232 ↓ 0.0 0 1

Nested Loop (cost=0.28..16.64 rows=2 width=25) (actual time=643.232..643.232 rows=0 loops=1)

43. 643.232 643.232 ↓ 0.0 0 1

CTE Scan on selectiondatemag (cost=0.00..0.04 rows=2 width=12) (actual time=643.232..643.232 rows=0 loops=1)

44. 0.000 0.000 ↓ 0.0 0

Index Scan using magasin_pkey on magasin (cost=0.28..8.29 rows=1 width=13) (never executed)

  • Index Cond: (id = selectiondatemag.magasin_id)
45. 0.000 0.000 ↓ 0.0 0

Index Scan using stock_magasin_idx on stock (cost=0.28..0.69 rows=2 width=16) (never executed)

  • Index Cond: (magasin_id = magasin.id)
46. 0.000 0.000 ↓ 0.0 0

Index Scan using ligne_stock_uk on ligne_stock ls (cost=0.43..107.16 rows=548 width=30) (never executed)

  • Index Cond: (stock_id = stock.id)
47. 0.000 0.000 ↓ 0.0 0

Index Scan using casse_ligne_stock_id_idx on casse (cost=0.43..4.27 rows=177 width=39) (never executed)

  • Index Cond: (ligne_stock_id = ls.id)
48. 0.000 0.000 ↓ 0.0 0

Index Scan using article_pkey on article (cost=0.29..0.31 rows=1 width=35) (never executed)

  • Index Cond: (id = ls.article_id)
49. 0.000 0.000 ↓ 0.0 0

Materialize (cost=0.00..1.04 rows=3 width=13) (never executed)

50. 0.000 0.000 ↓ 0.0 0

Seq Scan on unite unite_detail (cost=0.00..1.03 rows=3 width=13) (never executed)

51. 0.000 0.000 ↓ 0.0 0

Materialize (cost=0.00..2.35 rows=23 width=16) (never executed)

52. 0.000 0.000 ↓ 0.0 0

Seq Scan on groupe_article (cost=0.00..2.23 rows=23 width=16) (never executed)

53. 0.000 0.000 ↓ 0.0 0

Materialize (cost=0.00..1.09 rows=6 width=11) (never executed)

54. 0.000 0.000 ↓ 0.0 0

Seq Scan on rayon (cost=0.00..1.06 rows=6 width=11) (never executed)

55. 0.000 0.000 ↓ 0.0 0

CTE Scan on inventaire2 (cost=0.00..0.02 rows=1 width=36) (never executed)

Planning time : 24.370 ms
Execution time : 657.454 ms