explain.depesz.com

PostgreSQL's explain analyze made readable

Result: n7fC

Settings
# exclusive inclusive rows x rows loops node
1. 0.005 552.173 ↑ 1.0 5 1

Limit (cost=12,687.37..12,687.86 rows=5 width=405) (actual time=552.155..552.173 rows=5 loops=1)

2.          

CTE suborderdetails

3. 9.915 9.915 ↓ 1.6 1,564 1

Index Scan using idx_storeid_subordertable on subordertable (cost=0.43..11,192.88 rows=993 width=486) (actual time=0.064..9.915 rows=1,564 loops=1)

  • Index Cond: (storeid = 654,366)
  • Filter: ((parentstoreid IS NOT NULL) AND (suborderstatusvalue = ANY ('{3,5,6,7,8}'::bigint[])))
  • Rows Removed by Filter: 5,889
4. 0.037 552.168 ↑ 9.0 5 1

GroupAggregate (cost=1,494.49..1,498.88 rows=45 width=405) (actual time=552.154..552.168 rows=5 loops=1)

  • Group Key: ordertable.orderid
5. 1.512 552.131 ↑ 7.5 6 1

Sort (cost=1,494.49..1,494.60 rows=45 width=320) (actual time=552.130..552.131 rows=6 loops=1)

  • Sort Key: ordertable.orderid DESC
  • Sort Method: quicksort Memory: 485kB
6. 0.401 550.619 ↓ 30.5 1,373 1

Nested Loop Left Join (cost=3.06..1,493.25 rows=45 width=320) (actual time=1.372..550.619 rows=1,373 loops=1)

  • Filter: ((suborderdetails.suborderstatusvalue <> 3) OR ((suborderdetails.suborderstatusvalue = 3) AND (shipmenttable.trackingstatus = 'Returning to Shipper'::text)))
  • Rows Removed by Filter: 191
7. 1.258 543.962 ↓ 34.8 1,564 1

Nested Loop Left Join (cost=2.63..1,120.49 rows=45 width=297) (actual time=0.464..543.962 rows=1,564 loops=1)

  • Filter: ((suborderdetails.subordermodeofpayment <> 'onlinepayment'::text) OR (paymenttable.paymentgatewaystatus = 'success'::text))
8. 0.790 542.704 ↓ 34.8 1,564 1

Nested Loop (cost=2.21..784.31 rows=45 width=337) (actual time=0.457..542.704 rows=1,564 loops=1)

9. 0.581 168.118 ↓ 34.8 1,564 1

Nested Loop (cost=1.64..397.87 rows=45 width=301) (actual time=0.115..168.118 rows=1,564 loops=1)

10. 0.996 14.265 ↓ 34.8 1,564 1

Hash Join (cost=1.20..25.24 rows=45 width=280) (actual time=0.097..14.265 rows=1,564 loops=1)

  • Hash Cond: (suborderdetails.suborderstatusvalue = convertortable.suborderstatusvalue)
11. 13.260 13.260 ↓ 1.6 1,564 1

CTE Scan on suborderdetails (cost=0.00..19.86 rows=993 width=248) (actual time=0.067..13.260 rows=1,564 loops=1)

12. 0.004 0.009 ↑ 1.0 9 1

Hash (cost=1.09..1.09 rows=9 width=36) (actual time=0.009..0.009 rows=9 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
13. 0.005 0.005 ↑ 1.0 9 1

Seq Scan on convertortable (cost=0.00..1.09 rows=9 width=36) (actual time=0.004..0.005 rows=9 loops=1)

14. 153.272 153.272 ↑ 1.0 1 1,564

Index Scan using ordertable_pkey on ordertable (cost=0.43..8.28 rows=1 width=29) (actual time=0.098..0.098 rows=1 loops=1,564)

  • Index Cond: (orderid = suborderdetails.orderid)
15. 373.796 373.796 ↑ 1.0 1 1,564

Index Scan using producttable_pkey on producttable (cost=0.57..8.59 rows=1 width=44) (actual time=0.239..0.239 rows=1 loops=1,564)

  • Index Cond: (productid = suborderdetails.productid)
16. 0.000 0.000 ↓ 0.0 0 1,564

Index Scan using paymenttable_pkey on paymenttable (cost=0.42..7.46 rows=1 width=16) (actual time=0.000..0.000 rows=0 loops=1,564)

  • Index Cond: (suborderdetails.suborderpaymentid = paymentid)
17. 6.256 6.256 ↑ 1.0 1 1,564

Index Scan using shipmenttable_pkey on shipmenttable (cost=0.43..8.27 rows=1 width=47) (actual time=0.004..0.004 rows=1 loops=1,564)

  • Index Cond: (suborderdetails.subordershipmentid = shipmentid)
Planning time : 2.520 ms
Execution time : 552.769 ms