explain.depesz.com

PostgreSQL's explain analyze made readable

Result: fARU : 139

Settings
# exclusive inclusive rows x rows loops node
1. 0.025 14,864.075 ↑ 2.5 4 1

Sort (cost=416,653.03..416,653.06 rows=10 width=861) (actual time=14,864.072..14,864.075 rows=4 loops=1)

  • Sort Key: rod."Id
  • Sort Method: quicksort Memory: 26kB
2.          

CTE TempRequestOrderDetail

3. 0.009 86.465 ↑ 2.5 4 1

Limit (cost=0.29..2,953.38 rows=10 width=72) (actual time=32.638..86.465 rows=4 loops=1)

4. 0.013 86.456 ↑ 7.0 4 1

Nested Loop (cost=0.29..8,268.96 rows=28 width=72) (actual time=32.635..86.456 rows=4 loops=1)

5. 0.079 0.079 ↑ 1.0 1 1

Index Scan using "RequestOrder_pkey" on "RequestOrder" ro (cost=0.29..8.31 rows=1 width=16) (actual time=0.075..0.079 rows=1 loops=1)

  • Index Cond: ("Id" = 13928)
  • Filter: ((NOT "IsDel") AND ("OrganizationId" = 73) AND ("ReservoirAreaId" = 181))
6. 86.364 86.364 ↑ 7.0 4 1

Seq Scan on "RequestOrderDetail" rod_1 (cost=0.00..8,260.37 rows=28 width=64) (actual time=32.555..86.364 rows=4 loops=1)

  • Filter: ((NOT "IsDel") AND ("RequestId" = 13928) AND ("DetailType" = 3))
  • Rows Removed by Filter: 246637
7.          

CTE Sales

8. 31.665 7,376.020 ↑ 6.0 3,651 1

GroupAggregate (cost=205,367.17..205,971.84 rows=21,988 width=44) (actual time=7,319.501..7,376.020 rows=3,651 loops=1)

  • Group Key: o."WarehouseId", od."ProductId", ((o."SynchTime")::date)
9. 59.606 7,344.355 ↓ 1.3 28,536 1

Sort (cost=205,367.17..205,422.14 rows=21,988 width=17) (actual time=7,319.476..7,344.355 rows=28,536 loops=1)

  • Sort Key: od."ProductId", ((o."SynchTime")::date)
  • Sort Method: quicksort Memory: 2997kB
10. 3,345.013 7,284.749 ↓ 1.3 28,536 1

Hash Right Join (cost=69,303.56..203,781.35 rows=21,988 width=17) (actual time=5,417.601..7,284.749 rows=28,536 loops=1)

  • Hash Cond: (od."OrderNo" = o."No")
11. 3,869.896 3,869.896 ↑ 1.0 3,892,298 1

Seq Scan on "OrderDetail" od (cost=0.00..119,411.86 rows=3,944,286 width=31) (actual time=0.056..3,869.896 rows=3,892,298 loops=1)

12. 13.674 69.840 ↓ 1.4 12,757 1

Hash (cost=69,191.76..69,191.76 rows=8,944 width=34) (actual time=69.840..69.840 rows=12,757 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 1018kB
13. 50.085 56.166 ↓ 1.4 12,757 1

Bitmap Heap Scan on "Order" o (cost=1,101.04..69,191.76 rows=8,944 width=34) (actual time=6.554..56.166 rows=12,757 loops=1)

  • Recheck Cond: (("SynchTime" >= '2019-04-02 00:00:00'::timestamp without time zone) AND ("SynchTime" <= '2019-04-08 23:59:59'::timestamp without time zone))
  • Filter: ((NOT "IsDel") AND ("Status" > 0) AND ("WarehouseId" = 181))
  • Rows Removed by Filter: 45927
  • Heap Blocks: exact=2892
14. 6.081 6.081 ↓ 1.1 58,684 1

Bitmap Index Scan on "Index_Order_SynchTime" (cost=0.00..1,098.81 rows=52,238 width=0) (actual time=6.081..6.081 rows=58,684 loops=1)

  • Index Cond: (("SynchTime" >= '2019-04-02 00:00:00'::timestamp without time zone) AND ("SynchTime" <= '2019-04-08 23:59:59'::timestamp without time zone))
15.          

CTE SalesAll

16. 27.013 7,392.353 ↑ 22.1 993 1

GroupAggregate (cost=205,312.20..205,806.93 rows=21,988 width=40) (actual time=7,342.105..7,392.353 rows=993 loops=1)

  • Group Key: o_1."WarehouseId", od_1."ProductId
17. 50.992 7,365.340 ↓ 1.3 28,536 1

Sort (cost=205,312.20..205,367.17 rows=21,988 width=13) (actual time=7,342.047..7,365.340 rows=28,536 loops=1)

  • Sort Key: od_1."ProductId
  • Sort Method: quicksort Memory: 2106kB
18. 3,325.731 7,314.348 ↓ 1.3 28,536 1

Hash Right Join (cost=69,303.56..203,726.38 rows=21,988 width=13) (actual time=5,580.530..7,314.348 rows=28,536 loops=1)

  • Hash Cond: (od_1."OrderNo" = o_1."No")
19. 3,931.891 3,931.891 ↑ 1.0 3,892,298 1

Seq Scan on "OrderDetail" od_1 (cost=0.00..119,411.86 rows=3,944,286 width=31) (actual time=0.078..3,931.891 rows=3,892,298 loops=1)

20. 11.952 56.726 ↓ 1.4 12,757 1

Hash (cost=69,191.76..69,191.76 rows=8,944 width=26) (actual time=56.726..56.726 rows=12,757 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 868kB
21. 40.355 44.774 ↓ 1.4 12,757 1

Bitmap Heap Scan on "Order" o_1 (cost=1,101.04..69,191.76 rows=8,944 width=26) (actual time=4.876..44.774 rows=12,757 loops=1)

  • Recheck Cond: (("SynchTime" >= '2019-04-02 00:00:00'::timestamp without time zone) AND ("SynchTime" <= '2019-04-08 23:59:59'::timestamp without time zone))
  • Filter: ((NOT "IsDel") AND ("Status" > 0) AND ("WarehouseId" = 181))
  • Rows Removed by Filter: 45927
  • Heap Blocks: exact=2892
22. 4.419 4.419 ↓ 1.1 58,684 1

Bitmap Index Scan on "Index_Order_SynchTime" (cost=0.00..1,098.81 rows=52,238 width=0) (actual time=4.419..4.419 rows=58,684 loops=1)

  • Index Cond: (("SynchTime" >= '2019-04-02 00:00:00'::timestamp without time zone) AND ("SynchTime" <= '2019-04-08 23:59:59'::timestamp without time zone))
23. 0.963 14,864.050 ↑ 2.5 4 1

Hash Right Join (cost=1,315.95..1,920.72 rows=10 width=861) (actual time=14,817.634..14,864.050 rows=4 loops=1)

  • Hash Cond: ((sevendayavgsale."WarehouseId" = rod."ReservoirAreaId") AND (sevendayavgsale."ProductId" = rod."ArticleId"))
24. 7,393.992 7,393.992 ↑ 22.1 993 1

CTE Scan on "SalesAll" sevendayavgsale (cost=0.00..439.76 rows=21,988 width=40) (actual time=7,342.110..7,393.992 rows=993 loops=1)

25. 0.017 7,469.095 ↑ 2.5 4 1

Hash (cost=1,315.80..1,315.80 rows=10 width=741) (actual time=7,469.095..7,469.095 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
26. 0.022 7,469.078 ↑ 2.5 4 1

Nested Loop Left Join (cost=500.64..1,315.80 rows=10 width=741) (actual time=7,467.735..7,469.078 rows=4 loops=1)

27. 0.026 7,468.988 ↑ 2.5 4 1

Hash Left Join (cost=500.35..1,232.60 rows=10 width=742) (actual time=7,467.707..7,468.988 rows=4 loops=1)

  • Hash Cond: (rod."ReservoirAreaId" = ra."Id")
28. 0.019 7,468.886 ↑ 2.5 4 1

Nested Loop Left Join (cost=496.92..1,229.03 rows=10 width=738) (actual time=7,467.614..7,468.886 rows=4 loops=1)

29. 0.027 7,468.759 ↑ 2.5 4 1

Nested Loop Left Join (cost=496.63..1,145.83 rows=10 width=731) (actual time=7,467.568..7,468.759 rows=4 loops=1)

30. 0.021 7,468.676 ↑ 2.5 4 1

Nested Loop Left Join (cost=496.35..1,066.73 rows=10 width=703) (actual time=7,467.554..7,468.676 rows=4 loops=1)

31. 0.430 7,468.563 ↑ 2.5 4 1

Hash Right Join (cost=496.06..991.63 rows=10 width=698) (actual time=7,467.470..7,468.563 rows=4 loops=1)

  • Hash Cond: ((senvendaysale."WarehouseId" = rod."ReservoirAreaId") AND (senvendaysale."ProductId" = rod."ArticleId"))
32. 0.872 0.872 ↓ 4.8 525 1

CTE Scan on "Sales" senvendaysale (cost=0.00..494.73 rows=110 width=40) (actual time=0.006..0.872 rows=525 loops=1)

  • Filter: ("SynchTime" = '2019-04-02'::date)
  • Rows Removed by Filter: 3126
33. 0.020 7,467.261 ↑ 2.5 4 1

Hash (cost=495.91..495.91 rows=10 width=666) (actual time=7,467.261..7,467.261 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
34. 0.493 7,467.241 ↑ 2.5 4 1

Hash Right Join (cost=0.35..495.91 rows=10 width=666) (actual time=7,413.085..7,467.241 rows=4 loops=1)

  • Hash Cond: ((yesterdaysale."WarehouseId" = rod."ReservoirAreaId") AND (yesterdaysale."ProductId" = rod."ArticleId"))
35. 7,380.245 7,380.245 ↓ 4.9 544 1

CTE Scan on "Sales" yesterdaysale (cost=0.00..494.73 rows=110 width=40) (actual time=7,319.562..7,380.245 rows=544 loops=1)

  • Filter: ("SynchTime" = '2019-04-08'::date)
  • Rows Removed by Filter: 3107
36. 0.015 86.503 ↑ 2.5 4 1

Hash (cost=0.20..0.20 rows=10 width=634) (actual time=86.503..86.503 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
37. 86.488 86.488 ↑ 2.5 4 1

CTE Scan on "TempRequestOrderDetail" rod (cost=0.00..0.20 rows=10 width=634) (actual time=32.650..86.488 rows=4 loops=1)

38. 0.092 0.092 ↑ 1.0 1 4

Index Scan using "PurchaseOrder_pkey" on "PurchaseOrder" po (cost=0.28..7.50 rows=1 width=17) (actual time=0.022..0.023 rows=1 loops=4)

  • Index Cond: ("Id" = rod."PurchaseOrderId")
39. 0.056 0.056 ↑ 1.0 1 4

Index Scan using "Product_pkey" on "Product" p (cost=0.28..7.90 rows=1 width=32) (actual time=0.013..0.014 rows=1 loops=4)

  • Index Cond: ("Id" = rod."ArticleId")
40. 0.108 0.108 ↑ 1.0 1 4

Index Scan using "ProductStock_ProductId_ReservoirAreaId_OrganizationId_key" on "ProductStock" ps (cost=0.29..8.31 rows=1 width=15) (actual time=0.026..0.027 rows=1 loops=4)

  • Index Cond: (("ProductId" = rod."ArticleId") AND ("ReservoirAreaId" = rod."ReservoirAreaId"))
41. 0.023 0.076 ↑ 1.0 19 1

Hash (cost=3.19..3.19 rows=19 width=8) (actual time=0.076..0.076 rows=19 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
42. 0.053 0.053 ↑ 1.0 19 1

Seq Scan on "ReservoirArea" ra (cost=0.00..3.19 rows=19 width=8) (actual time=0.015..0.053 rows=19 loops=1)

43. 0.068 0.068 ↑ 1.0 1 4

Index Scan using "ProductStock_ProductId_ReservoirAreaId_OrganizationId_key" on "ProductStock" pps (cost=0.29..8.31 rows=1 width=11) (actual time=0.016..0.017 rows=1 loops=4)

  • Index Cond: (("ProductId" = rod."ArticleId") AND ("ReservoirAreaId" = ra."ParentId"))