explain.depesz.com

PostgreSQL's explain analyze made readable

Result: k7Rt

Settings
# exclusive inclusive rows x rows loops node
1. 3.711 12,449.093 ↓ 392.0 392 1

Sort (cost=58,506.39..58,506.40 rows=1 width=1,854) (actual time=12,448.865..12,449.093 rows=392 loops=1)

  • Sort Key: a.ordertime DESC
  • Sort Method: quicksort Memory: 424kB
2. 6.781 12,445.382 ↓ 392.0 392 1

Subquery Scan on a (cost=42,101.15..58,506.38 rows=1 width=1,854) (actual time=305.487..12,445.382 rows=392 loops=1)

  • -> Group (cost=42,101.15..52981.42 rows=1 width=2,140) (actualtime=305.483..10884.712 rows=392 loops=1)
  • Group Key: tbl.orderid, grp.invoiceno, grp.customerid, grp.creditflag, grp.customername, grp.customerphone, grp.ordertime, 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, req.requestid
3. 1.773 277.005 ↓ 403.0 403 1

Sort (cost=42,101.15..42,101.16 rows=1 width=1,814) (actual time=276.393..277.005 rows=403 loops=1)

  • Sort Key: tbl.orderid, grp.invoiceno, grp.creditflag, grp.customername, grp.customerphone, grp.ordertime, 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, req.requestid
  • Sort Method: quicksort Memory: 434kB
4. 1.349 275.232 ↓ 403.0 403 1

Nested Loop Left Join (cost=36,546.94..42,101.14 rows=1 width=1,814) (actual time=175.856..275.232 rows=403 loops=1)

  • Join Filter: (req.orderid = tbl.orderid)
  • Rows Removed by Join Filter: 2,015
5. 47.446 272.674 ↓ 403.0 403 1

Hash Right Join (cost=36,546.94..42,088.22 rows=1 width=1,810) (actual time=175.844..272.674 rows=403 loops=1)

  • Hash Cond: (itm.orderid = tbl.orderid)
6. 52.992 52.992 ↑ 1.3 117,097 1

Seq Scan on ecomm_orderitems itm (cost=0.00..4,983.19 rows=148,819 width=4) (actual time=0.007..52.992 rows=117,097 loops=1)

7. 1.005 172.236 ↓ 392.0 392 1

Hash (cost=36,546.93..36,546.93 rows=1 width=1,810) (actual time=172.235..172.236 rows=392 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 317kB
8. 45.291 171.231 ↓ 392.0 392 1

Hash Join (cost=25,326.62..36,546.93 rows=1 width=1,810) (actual time=49.993..171.231 rows=392 loops=1)

  • Hash Cond: (tbl.ordergroupid = grp.ordergroupid)
9. 80.576 80.576 ↑ 1.1 113,159 1

Seq Scan on ecomm_orders tbl (cost=0.00..10,772.51 rows=119,409 width=1,570) (actual time=0.009..80.576 rows=113,159 loops=1)

  • Filter: ((status)::text = ANY ('{COMPLETED,CANCELED,REJECTED}'::text[]))
  • Rows Removed by Filter: 2,707
10. 0.481 45.364 ↓ 422.0 422 1

Hash (cost=25,326.61..25,326.61 rows=1 width=244) (actual time=45.363..45.364 rows=422 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 119kB
11. 44.883 44.883 ↓ 422.0 422 1

Seq Scan on ecomm_ordergroups grp (cost=0.00..25,326.61 rows=1 width=244) (actual time=0.746..44.883 rows=422 loops=1)

  • Filter: (customerid = 97,581)
  • Rows Removed by Filter: 115,616
12. 1.209 1.209 ↑ 26.0 5 403

Seq Scan on ecomm_cancelrequest req (cost=0.00..11.30 rows=130 width=8) (actual time=0.001..0.003 rows=5 loops=403)

13.          

SubPlan (for Subquery Scan)

14. 3.528 5,359.816 ↑ 1.0 1 392

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

15. 5,356.288 5,356.288 ↑ 1.0 1 392

Seq Scan on ecomm_orderitems (cost=0.00..5,355.24 rows=1 width=4) (actual time=8.449..13.664 rows=1 loops=392)

  • Filter: (orderid = tbl.orderid)
  • Rows Removed by Filter: 117,096
16. 1.960 5,243.000 ↑ 1.0 1 392

Limit (cost=5,524.94..5,524.95 rows=1 width=644) (actual time=13.371..13.375 rows=1 loops=392)

17. 1.568 5,241.040 ↑ 1.0 1 392

Group (cost=5,524.94..5,524.95 rows=1 width=644) (actual time=13.369..13.370 rows=1 loops=392)

  • Group Key: item_1.orderitemid, img_1.skuimageid
18. 3.920 5,239.472 ↑ 1.0 1 392

Sort (cost=5,524.94..5,524.95 rows=1 width=326) (actual time=13.366..13.366 rows=1 loops=392)

  • Sort Key: item_1.orderitemid, img_1.skuimageid
  • Sort Method: quicksort Memory: 25kB
19. 4.169 5,235.552 ↑ 1.0 1 392

Nested Loop (cost=4.68..5,524.93 rows=1 width=326) (actual time=9.653..13.356 rows=1 loops=392)

20. 5,226.144 5,226.144 ↑ 1.0 1 392

Seq Scan on ecomm_orderitems item_1 (cost=0.00..5,355.24 rows=1 width=8) (actual time=8.188..13.332 rows=1 loops=392)

  • Filter: (tbl.orderid = orderid)
  • Rows Removed by Filter: 117,096
21. 5.239 5.239 ↑ 26.0 1 403

Bitmap Heap Scan on ecomm_sku_images img_1 (cost=4.68..169.43 rows=26 width=326) (actual time=0.013..0.013 rows=1 loops=403)

  • Recheck Cond: (skuid = item_1.skuid)
  • Filter: isthumbnail
  • Rows Removed by Filter: 0
  • Heap Blocks: exact=415
  • -> Bitmap Index Scan on ecomm_sku_images_skuid_idx01 (cost=0.00..4.67 rows=52 width=0) (actualtime=0.008..0.008 rows=1 loops=403)
  • Index Cond: (skuid= item_1.skuid)
22. 0.708 1,558.780 ↑ 1.0 1 118

Limit (cost=5,524.94..5,524.95 rows=1 width=644) (actual time=13.205..13.210 rows=1 loops=118)

23. 0.590 1,558.072 ↑ 1.0 1 118

Group (cost=5,524.94..5,524.95 rows=1 width=644) (actual time=13.204..13.204 rows=1 loops=118)

  • Group Key: item.orderitemid, img.skuimageid
24. 1.062 1,557.482 ↑ 1.0 1 118

Sort (cost=5,524.94..5,524.95 rows=1 width=326) (actual time=13.199..13.199 rows=1 loops=118)

  • Sort Key: item.orderitemid, img.skuimageid
  • Sort Method: quicksort Memory: 25kB
25. 1,555.340 1,556.420 ↑ 1.0 1 118

Nested Loop (cost=4.68..5,524.93 rows=1 width=326) (actual time=8.611..13.190 rows=1 loops=118)

  • -> Seq Scan on ecomm_orderitems item (cost=0.00..5355.24 rows=1 width=8) (actual time=8.352..13.171 rows=1loops=118)
  • Filter: (a.orderid = orderid)
  • Rows Removed by Filter: 117,096
26. 0.480 1.080 ↑ 26.0 1 120

Bitmap Heap Scan on ecomm_sku_images img (cost=4.68..169.43 rows=26 width=326) (actual time=0.009..0.009 rows=1 loops=120)

  • Recheck Cond: (skuid = item.skuid)
  • Filter: (NOT isthumbnail)
  • Rows Removed by Filter: 0
  • Heap Blocks: exact=116
27. 0.600 0.600 ↑ 52.0 1 120

Bitmap Index Scan on ecomm_sku_images_skuid_idx01 (cost=0.00..4.67 rows=52 width=0) (actual time=0.005..0.005 rows=1 loops=120)

  • Index Cond: (skuid = item.skuid)
Planning time : 1.214 ms
Execution time : 12,449.511 ms