explain.depesz.com

PostgreSQL's explain analyze made readable

Result: D62k

Settings
# exclusive inclusive rows x rows loops node
1. 0.005 49,081.393 ↑ 1.0 10 1

Limit (cost=855,398.44..855,415.56 rows=10 width=19) (actual time=49,079.367..49,081.393 rows=10 loops=1)

2.          

CTE article_with_needs

3. 373.615 13,734.601 ↑ 1.4 97,733 1

Unique (cost=376,186.07..390,165.61 rows=139,796 width=24) (actual time=10,377.358..13,734.601 rows=97,733 loops=1)

4. 11,049.932 13,360.986 ↑ 1.3 1,087,872 1

Sort (cost=376,186.07..379,680.95 rows=1,397,954 width=24) (actual time=10,377.354..13,360.986 rows=1,087,872 loops=1)

  • Sort Key: lacroix_mrp_1.buyer, lacroix_mrp_1.division, lacroix_mrp_1.article
  • Sort Method: external merge Disk: 36872kB
5. 482.186 2,311.054 ↑ 1.3 1,087,872 1

Append (cost=41,175.38..204,818.71 rows=1,397,954 width=24) (actual time=108.284..2,311.054 rows=1,087,872 loops=1)

6. 1,074.718 1,176.912 ↑ 1.2 691,898 1

Bitmap Heap Scan on lacroix_mrp lacroix_mrp_1 (cost=41,175.38..135,113.49 rows=840,227 width=24) (actual time=108.282..1,176.912 rows=691,898 loops=1)

  • Recheck Cond: ((effective_at <= (('now'::cstring)::date + '70 days'::interval)) AND (quantity < '0'::double precision))
  • Rows Removed by Index Recheck: 1038064
  • Heap Blocks: exact=33024 lossy=26535
7. 102.194 102.194 ↑ 1.2 691,898 1

Bitmap Index Scan on raw_import_mat_views_lacroix_mrp_buyer_division (cost=0.00..40,965.32 rows=840,227 width=0) (actual time=102.194..102.194 rows=691,898 loops=1)

  • Index Cond: ((effective_at <= (('now'::cstring)::date + '70 days'::interval)) AND (quantity < '0'::double precision))
8. 570.952 570.952 ↑ 1.5 260,573 1

Seq Scan on eolane_mrp eolane_mrp_1 (cost=0.00..44,479.31 rows=398,695 width=22) (actual time=0.041..570.952 rows=260,573 loops=1)

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

Bitmap Heap Scan on allcircuits_tis_mrp allcircuits_tis_mrp_1 (cost=7,577.70..25,225.92 rows=159,032 width=29) (actual time=18.593..81.004 rows=135,401 loops=1)

  • Recheck Cond: ((effective_at <= (('now'::cstring)::date + '70 days'::interval)) AND (quantity < '0'::double precision))
  • Heap Blocks: exact=9199
10. 17.107 17.107 ↑ 1.2 135,401 1

Bitmap Index Scan on raw_import_mat_views_allcircuits_tis_mrp_buyer_division (cost=0.00..7,537.94 rows=159,032 width=0) (actual time=17.107..17.107 rows=135,401 loops=1)

  • Index Cond: ((effective_at <= (('now'::cstring)::date + '70 days'::interval)) AND (quantity < '0'::double precision))
11. 0.046 49,081.388 ↑ 6.7 10 1

Subquery Scan on a (cost=465,232.83..465,347.55 rows=67 width=19) (actual time=49,079.365..49,081.388 rows=10 loops=1)

  • Filter: (a.qty < '0'::double precision)
  • Rows Removed by Filter: 69
12. 0.387 49,081.342 ↑ 2.5 79 1

Unique (cost=465,232.83..465,345.05 rows=200 width=32) (actual time=49,079.313..49,081.342 rows=79 loops=1)

13. 7,558.973 49,080.955 ↑ 16.0 1,404 1

Sort (cost=465,232.83..465,288.94 rows=22,443 width=32) (actual time=49,079.311..49,080.955 rows=1,404 loops=1)

  • Sort Key: lacroix_mrp.article
  • Sort Method: external merge Disk: 54640kB
14. 1,222.866 41,521.982 ↓ 53.0 1,190,546 1

WindowAgg (cost=463,105.91..463,610.88 rows=22,443 width=32) (actual time=38,873.818..41,521.982 rows=1,190,546 loops=1)

15. 10,539.240 40,299.116 ↓ 53.0 1,190,546 1

Sort (cost=463,105.91..463,162.02 rows=22,443 width=32) (actual time=38,873.806..40,299.116 rows=1,190,546 loops=1)

  • Sort Key: lacroix_mrp.division, lacroix_mrp.article, lacroix_mrp.normalized_effective_at
  • Sort Method: external merge Disk: 54456kB
16. 1,719.201 29,759.876 ↓ 53.0 1,190,546 1

Merge Join (cost=446,669.06..461,483.96 rows=22,443 width=32) (actual time=24,622.589..29,759.876 rows=1,190,546 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))
17. 10,848.884 13,374.293 ↑ 1.0 1,275,295 1

Sort (cost=424,757.50..427,968.25 rows=1,284,302 width=40) (actual time=10,303.711..13,374.293 rows=1,275,295 loops=1)

  • Sort Key: lacroix_mrp.article, lacroix_mrp.division, lacroix_mrp.buyer
  • Sort Method: external merge Disk: 68816kB
18. 599.759 2,525.409 ↑ 1.0 1,275,295 1

Append (cost=38,742.74..259,329.67 rows=1,284,302 width=40) (actual time=114.078..2,525.409 rows=1,275,295 loops=1)

19. 1,289.259 1,396.629 ↑ 1.0 752,511 1

Bitmap Heap Scan on lacroix_mrp (cost=38,742.74..159,702.35 rows=778,410 width=40) (actual time=114.076..1,396.629 rows=752,511 loops=1)

  • Recheck Cond: (effective_at <= (('now'::cstring)::date + '70 days'::interval))
  • Rows Removed by Index Recheck: 938790
  • Filter: ((quantity <> '0'::double precision) 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: 208981
  • Heap Blocks: exact=35526 lossy=26553
20. 107.370 107.370 ↑ 1.0 961,492 1

Bitmap Index Scan on raw_import_mat_views_lacroix_mrp_buyer_division (cost=0.00..38,548.13 rows=966,876 width=0) (actual time=107.370..107.370 rows=961,492 loops=1)

  • Index Cond: (effective_at <= (('now'::cstring)::date + '70 days'::interval))
21. 336.640 396.327 ↓ 1.1 369,096 1

Bitmap Heap Scan on eolane_mrp (cost=22,464.42..70,125.46 rows=349,400 width=38) (actual time=62.933..396.327 rows=369,096 loops=1)

  • Recheck Cond: (effective_at <= (('now'::cstring)::date + '70 days'::interval))
  • Filter: ((quantity <> '0'::double precision) 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: 230449
  • Heap Blocks: exact=18756
22. 59.687 59.687 ↑ 1.0 599,545 1

Bitmap Index Scan on raw_import_mat_views_eolane_mrp_buyer_division (cost=0.00..22,377.07 rows=606,485 width=0) (actual time=59.687..59.687 rows=599,545 loops=1)

  • Index Cond: (effective_at <= (('now'::cstring)::date + '70 days'::interval))
23. 115.632 132.694 ↑ 1.0 153,688 1

Bitmap Heap Scan on allcircuits_tis_mrp (cost=7,140.69..29,501.86 rows=156,492 width=45) (actual time=18.897..132.694 rows=153,688 loops=1)

  • Recheck Cond: (effective_at <= (('now'::cstring)::date + '70 days'::interval))
  • Filter: ((quantity <> '0'::double precision) 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: 17503
  • Heap Blocks: exact=11430
24. 17.062 17.062 ↑ 1.0 171,191 1

Bitmap Index Scan on raw_import_mat_views_allcircuits_tis_mrp_buyer_division (cost=0.00..7,101.56 rows=174,551 width=0) (actual time=17.062..17.062 rows=171,191 loops=1)

  • Index Cond: (effective_at <= (('now'::cstring)::date + '70 days'::interval))
25. 291.572 14,666.382 ↓ 8.5 1,191,997 1

Materialize (cost=21,911.56..22,610.54 rows=139,796 width=96) (actual time=14,318.871..14,666.382 rows=1,191,997 loops=1)

26. 575.813 14,374.810 ↑ 1.4 97,733 1

Sort (cost=21,911.56..22,261.05 rows=139,796 width=96) (actual time=14,318.865..14,374.810 rows=97,733 loops=1)

  • Sort Key: article_with_needs.article, article_with_needs.division, article_with_needs.buyer
  • Sort Method: external merge Disk: 3232kB
27. 13,798.997 13,798.997 ↑ 1.4 97,733 1

CTE Scan on article_with_needs (cost=0.00..2,795.92 rows=139,796 width=96) (actual time=10,377.362..13,798.997 rows=97,733 loops=1)