explain.depesz.com

PostgreSQL's explain analyze made readable

Result: a8Oc

Settings
# exclusive inclusive rows x rows loops node
1. 28.225 38,200.344 ↓ 238.1 15,950 1

Subquery Scan on a (cost=822,420.58..822,587.10 rows=67 width=19) (actual time=37,619.666..38,200.344 rows=15,950 loops=1)

  • Filter: (a.qty < '0'::double precision)
  • Rows Removed by Filter: 79316
2.          

CTE article_with_needs

3. 572.787 4,178.112 ↑ 1.7 97,733 1

HashAggregate (cost=248,205.16..249,822.62 rows=161,746 width=24) (actual time=4,132.639..4,178.112 rows=97,733 loops=1)

  • Group Key: lacroix_mrp_1.article, lacroix_mrp_1.division, lacroix_mrp_1.buyer
4. 433.713 3,605.325 ↑ 1.5 1,087,872 1

Append (cost=0.00..236,074.22 rows=1,617,459 width=24) (actual time=0.033..3,605.325 rows=1,087,872 loops=1)

5. 2,137.344 2,137.344 ↑ 1.4 691,898 1

Seq Scan on lacroix_mrp lacroix_mrp_1 (cost=0.00..160,926.62 rows=972,002 width=24) (actual time=0.033..2,137.344 rows=691,898 loops=1)

  • Filter: ((quantity < '0'::double precision) AND (effective_at <= (('now'::cstring)::date + '70 days'::interval)))
  • Rows Removed by Filter: 3125285
6. 580.252 580.252 ↑ 1.6 260,573 1

Seq Scan on eolane_mrp eolane_mrp_1 (cost=0.00..44,474.31 rows=421,285 width=22) (actual time=0.045..580.252 rows=260,573 loops=1)

  • Filter: ((quantity < '0'::double precision) AND (effective_at <= (('now'::cstring)::date + '70 days'::interval)))
  • Rows Removed by Filter: 878374
7. 454.016 454.016 ↑ 1.7 135,401 1

Seq Scan on allcircuits_tis_mrp allcircuits_tis_mrp_1 (cost=0.00..30,673.29 rows=224,172 width=29) (actual time=0.020..454.016 rows=135,401 loops=1)

  • Filter: ((quantity < '0'::double precision) AND (effective_at <= (('now'::cstring)::date + '70 days'::interval)))
  • Rows Removed by Filter: 602212
8. 309.643 38,172.119 ↓ 476.3 95,266 1

Unique (cost=572,597.96..572,761.98 rows=200 width=32) (actual time=37,619.623..38,172.119 rows=95,266 loops=1)

9. 4,534.396 37,862.476 ↓ 36.9 1,211,122 1

Sort (cost=572,597.96..572,679.97 rows=32,804 width=32) (actual time=37,619.621..37,862.476 rows=1,211,122 loops=1)

  • Sort Key: lacroix_mrp.article
  • Sort Method: quicksort Memory: 124867kB
10. 1,131.838 33,328.080 ↓ 36.9 1,211,122 1

WindowAgg (cost=569,399.31..570,137.40 rows=32,804 width=32) (actual time=31,910.913..33,328.080 rows=1,211,122 loops=1)

11. 6,702.822 32,196.242 ↓ 36.9 1,211,122 1

Sort (cost=569,399.31..569,481.32 rows=32,804 width=32) (actual time=31,910.898..32,196.242 rows=1,211,122 loops=1)

  • Sort Key: lacroix_mrp.division, lacroix_mrp.article, lacroix_mrp.normalized_effective_at
  • Sort Method: quicksort Memory: 124867kB
12. 1,879.202 25,493.420 ↓ 36.9 1,211,122 1

Merge Join (cost=548,768.50..566,938.75 rows=32,804 width=32) (actual time=22,887.343..25,493.420 rows=1,211,122 loops=1)

  • Merge Cond: ((lacroix_mrp.article = article_with_needs.article) AND (lacroix_mrp.division = article_with_needs.division) AND (lacroix_mrp.buyer = article_with_needs.buyer))
13. 14,155.706 18,661.601 ↓ 1.0 1,641,999 1

Sort (cost=531,539.82..535,596.01 rows=1,622,475 width=40) (actual time=18,190.728..18,661.601 rows=1,641,999 loops=1)

  • Sort Key: lacroix_mrp.article, lacroix_mrp.division, lacroix_mrp.buyer
  • Sort Method: external sort Disk: 88392kB
14. 671.698 4,505.895 ↓ 1.0 1,642,052 1

Append (cost=0.00..364,183.43 rows=1,622,475 width=40) (actual time=0.016..4,505.895 rows=1,642,052 loops=1)

15. 2,440.936 2,440.936 ↑ 1.1 893,255 1

Seq Scan on lacroix_mrp (cost=0.00..246,813.23 rows=940,987 width=40) (actual time=0.015..2,440.936 rows=893,255 loops=1)

  • Filter: ((effective_at <= (('now'::cstring)::date + '70 days'::interval)) AND ((type = 'SB'::text) OR (type = 'AR'::text) OR (type = 'RP'::text) OR (type = 'WB'::text) OR (type = 'LK'::text) OR (type = 'LB'::text) OR (type = 'QM'::text) OR (type = 'BB'::text) OR (type = 'LA'::text) OR (type = 'LE'::text)))
  • Rows Removed by Filter: 2923928
16. 913.669 913.669 ↓ 1.3 583,258 1

Seq Scan on eolane_mrp (cost=0.00..70,100.61 rows=457,330 width=38) (actual time=0.021..913.669 rows=583,258 loops=1)

  • Filter: ((effective_at <= (('now'::cstring)::date + '70 days'::interval)) AND ((type = 'SB'::text) OR (type = 'AR'::text) OR (type = 'RP'::text) OR (type = 'WB'::text) OR (type = 'LK'::text) OR (type = 'LB'::text) OR (type = 'QM'::text) OR (type = 'BB'::text) OR (type = 'LA'::text) OR (type = 'LE'::text)))
  • Rows Removed by Filter: 555689
17. 479.592 479.592 ↑ 1.4 165,539 1

Seq Scan on allcircuits_tis_mrp (cost=0.00..47,269.58 rows=224,158 width=45) (actual time=0.020..479.592 rows=165,539 loops=1)

  • Filter: ((effective_at <= (('now'::cstring)::date + '70 days'::interval)) AND ((type = 'SB'::text) OR (type = 'AR'::text) OR (type = 'RP'::text) OR (type = 'WB'::text) OR (type = 'LK'::text) OR (type = 'LB'::text) OR (type = 'QM'::text) OR (type = 'BB'::text) OR (type = 'LA'::text) OR (type = 'LE'::text)))
  • Rows Removed by Filter: 572074
18. 711.832 4,952.617 ↓ 7.5 1,211,121 1

Sort (cost=17,228.67..17,633.04 rows=161,746 width=96) (actual time=4,696.594..4,952.617 rows=1,211,121 loops=1)

  • Sort Key: article_with_needs.article, article_with_needs.division, article_with_needs.buyer
  • Sort Method: quicksort Memory: 10708kB
19. 4,240.785 4,240.785 ↑ 1.7 97,733 1

CTE Scan on article_with_needs (cost=0.00..3,234.92 rows=161,746 width=96) (actual time=4,132.643..4,240.785 rows=97,733 loops=1)

Planning time : 1.103 ms
Execution time : 38,256.689 ms