explain.depesz.com

PostgreSQL's explain analyze made readable

Result: X7o

Settings
# exclusive inclusive rows x rows loops node
1. 0.097 40,317.074 ↑ 1.0 10 1

Limit (cost=515,726.25..515,726.28 rows=10 width=16,386) (actual time=40,316.972..40,317.074 rows=10 loops=1)

2. 4.490 40,316.977 ↑ 4.6 10 1

Sort (cost=515,726.25..515,726.37 rows=46 width=16,386) (actual time=40,316.969..40,316.977 rows=10 loops=1)

  • Sort Key: grp.ordertime DESC
  • Sort Method: top-N heapsort Memory: 35kB
3. 27.403 40,312.487 ↓ 32.5 1,497 1

HashAggregate (cost=515,724.34..515,724.80 rows=46 width=16,386) (actual time=40,309.159..40,312.487 rows=1,497 loops=1)

  • Group Key: tbl.orderid, tbl.ordergroupid, tbl.vendorid, tbl.vendorname, tbl.vendornpwp, tbl.vendorphoneno, tbl.pono, tbl.awb, tbl.total, tbl.totalshipping, tbl.totaldiscount, tbl.totalmargin, tbl.status, tbl.warehouseid, tbl.warehousephoneno, tbl.warehousecontact, tbl.warehouseaddressline1, tbl.warehouseaddressline2, tbl.warehousecountryname,tbl.warehousestatename, tbl.warehousecityname, tbl.warehousedistrictname, tbl.warehousesubdistrictname, tbl.warehouserw, tbl.warehousert, tbl.warehouseno, tbl.warehousezipcode, tbl.warehousecountryid, tbl.warehousestateid, tbl.warehousecityid, tbl.warehousedistrictid, tbl.warehousesubdistrictid, tbl.vendoraddressline1, tbl.vendoraddressline2, tbl.vendorcountryname, tbl.vendorstatename, tbl.vendorcityname, tbl.vendordistrictname, tbl.vendorsubdistrictname, tbl.vendorrw, tbl.vendorrt, tbl.vendorno, tbl.vendorzipcode, tbl.vendorcountryid, tbl.vendorstateid, tbl.vendorcityid, tbl.vendordistrictid, tbl.vendorsubdistrictid, tbl.shippingpackageid, tbl.shippingpackagecode, tbl.shippingpackagename, tbl.shippingid, tbl.shippingname, tbl.shippingcode, tbl.vendoremail, tbl.warehousename, tbl.totalassurance, tbl.assuranceflag, tbl.subgrandtotal, tbl.vendorcode, tbl.delivertime, tbl.completetime, tbl.voucheramount, tbl.pin, tbl.ppn, tbl.pph, tbl.dpp, tbl.shippingbookingcode, tbl.oldorderid, tbl.difftotal, tbl.difftotalshipping, tbl.pinsms, grp.ordertime, grp.invoiceno, grp.customername, grp.customerphone, grp.paymenttime, grp.paymenttypename, grp.shippingaddressline1, grp.shippingaddressline2, grp.shippingrw, grp.shippingrt, grp.shippingno, grp.shippingzipcode, grp.shippingphoneno, grp.shippingreceivername, grp.shippingcountryname, grp.shippingstatename, grp.shippingcityname, grp.shippingdistrictname, grp.shippingsubdistrictname,grp.installment, grp.paymentperiode, grp.firstpayment, ((SubPlan 4)), req.requestid, ((SubPlan 5)), ((SubPlan 6))
4. 2.265 40,285.084 ↓ 32.5 1,497 1

Append (cost=0.42..515,713.07 rows=46 width=16,386) (actual time=52.503..40,285.084 rows=1,497 loops=1)

5. 25.464 39,296.690 ↓ 1,464.0 1,464 1

Nested Loop Left Join (cost=0.42..21,997.90 rows=1 width=2,064) (actual time=52.501..39,296.690 rows=1,464 loops=1)

  • Join Filter: (req.orderid = tbl.orderid)
  • Rows Removed by Join Filter: 7,320
6. 7.790 91.658 ↓ 0.0 1,464 1

Nested Loop (cost=0.42..11274.47 rows=1width=2036) (cost=0..0 rows=0 width=0) (actual time=17.265..91.658 rows=1,464 loops=1)

7. 53.124 53.124 ↓ 1,464.0 1,464 1

Seq Scan on ecomm_orders tbl (cost=0.00..11,266.02 rows=1 width=1,801) (actual time=17.229..53.124 rows=1,464 loops=1)

  • Filter: (((status)::text = ANY ('{COMPLETED,DELIVERED}'::text[])) AND (vendorid = 451) AND ((shippingcode)::text = 'SPU'::text))
  • Rows Removed by Filter: 114,402
8. 30.744 30.744 ↑ 1.0 1 1,464

Index Scan using ecomm_ordergroups_pkey on ecomm_ordergroups grp (cost=0.42..8.44 rows=1 width=239) (actual time=0.018..0.021 rows=1 loops=1,464)

  • Index Cond: (ordergroupid = tbl.ordergroupid)
  • Filter: ((customername)::text ~~* '%%'::text)
9. 5.856 5.856 ↑ 26.0 5 1,464

Seq Scan on ecomm_cancelrequest req (cost=0.00..11.30 rows=130 width=8) (actual time=0.002..0.004 rows=5 loops=1,464)

10.          

SubPlan (for Nested Loop Left Join)

11. 10.248 19,642.488 ↑ 1.0 1 1,464

Aggregate (cost=5,355.24..5,355.25 rows=1 width=8) (actual time=13.416..13.417 rows=1 loops=1,464)

12. 19,632.240 19,632.240 ↑ 1.0 1 1,464

Seq Scan on ecomm_orderitems ecomm_orderitems_2 (cost=0.00..5,355.24 rows=1 width=4) (actual time=12.181..13.410 rows=1 loops=1,464)

  • Filter: (orderid = tbl.orderid)
  • Rows Removed by Filter: 117,096
13. 11.712 19,528.296 ↑ 1.0 1 1,464

Aggregate (cost=5,355.24..5,355.25 rows=1 width=8) (actual time=13.338..13.339 rows=1 loops=1,464)

14. 19,516.584 19,516.584 ↑ 1.0 1 1,464

Seq Scan on ecomm_orderitems ecomm_orderitems_3 (cost=0.00..5,355.24 rows=1 width=4) (actual time=12.108..13.331 rows=1 loops=1,464)

  • Filter: (orderid = tbl.orderid)
  • Rows Removed by Filter: 117,096
15. 2.928 2.928 ↑ 1.0 1 1,464

Result (cost=0.00..0.01 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=1,464)

16. 0.565 986.129 ↑ 1.4 33 1

Nested Loop Left Join (cost=0.42..493,714.71 rows=45 width=2,064) (actual time=29.753..986.129 rows=33 loops=1)

  • Join Filter: (req_1.orderid = tbl_1.orderid)
  • Rows Removed by Join Filter: 165
17. 0.200 71.464 ↑ 1.4 33 1

Nested Loop (cost=0.42..11,642.27 rows=45 width=2,036) (actual time=3.762..71.464 rows=33 loops=1)

18. 70.538 70.538 ↑ 1.4 33 1

Seq Scan on ecomm_orders tbl_1 (cost=0.00..11,266.02 rows=45 width=1,801) (actual time=3.732..70.538 rows=33 loops=1)

  • Filter: (((shippingcode)::text <> 'SPU'::text) AND (vendorid = 451) AND ((status)::text = 'COMPLETED'::text))
  • Rows Removed by Filter: 115,833
19. 0.726 0.726 ↑ 1.0 1 33

Index Scan using ecomm_ordergroups_pkey on ecomm_ordergroups grp_1 (cost=0.42..8.35 rows=1 width=239) (actual time=0.020..0.022 rows=1 loops=33)

  • Index Cond: (ordergroupid = tbl_1.ordergroupid)
  • Filter: ((customername)::text ~~* '%%'::text)
20. 0.093 0.099 ↑ 26.0 5 33

Materialize (cost=0.00..11.95 rows=130 width=8) (actual time=0.001..0.003 rows=5 loops=33)

21. 0.006 0.006 ↑ 26.0 5 1

Seq Scan on ecomm_cancelrequest req_1 (cost=0.00..11.30 rows=130 width=8) (actual time=0.003..0.006 rows=5 loops=1)

22.          

SubPlan (for Nested Loop Left Join)

23. 0.231 461.967 ↑ 1.0 1 33

Aggregate (cost=5,355.24..5,355.25 rows=1 width=8) (actual time=13.999..13.999 rows=1 loops=33)

24. 461.736 461.736 ↑ 1.0 1 33

Seq Scan on ecomm_orderitems (cost=0.00..5,355.24 rows=1 width=4) (actual time=3.062..13.992 rows=1 loops=33)

  • Filter: (orderid = tbl_1.orderid)
  • Rows Removed by Filter: 117,096
25. 0.297 451.935 ↑ 1.0 1 33

Aggregate (cost=5,355.24..5,355.25 rows=1 width=8) (actual time=13.694..13.695 rows=1 loops=33)

26. 451.638 451.638 ↑ 1.0 1 33

Seq Scan on ecomm_orderitems ecomm_orderitems_1 (cost=0.00..5,355.24 rows=1 width=4) (actual time=2.793..13.686 rows=1 loops=33)

  • Filter: (orderid = tbl_1.orderid)
  • Rows Removed by Filter: 117,096
27. 0.099 0.099 ↑ 1.0 1 33

Result (cost=0.00..0.01 rows=1 width=8) (actual time=0.002..0.003 rows=1 loops=33)

Planning time : 2.176 ms
Execution time : 40,317.937 ms