explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8SaD

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 496.515 ↑ 1.0 10 1

Limit (cost=10,501.20..10,504.19 rows=10 width=528) (actual time=496.478..496.515 rows=10 loops=1)

2.          

CTE suborderdetails

3. 1.894 445.238 ↓ 8.3 5,576 1

Hash Join (cost=674.92..5,082.96 rows=673 width=257) (actual time=67.853..445.238 rows=5,576 loops=1)

  • Hash Cond: (subordertable.suborderstatusvalue = convertortable.suborderstatusvalue)
4. 1.492 443.319 ↓ 52.6 5,576 1

Nested Loop Left Join (cost=636.35..5,020.57 rows=106 width=228) (actual time=67.809..443.319 rows=5,576 loops=1)

5. 4.168 341.459 ↓ 52.6 5,576 1

Nested Loop Left Join (cost=635.91..4,124.87 rows=106 width=220) (actual time=67.774..341.459 rows=5,576 loops=1)

6. 1.229 270.379 ↓ 52.6 5,576 1

Nested Loop (cost=635.48..3,229.17 rows=106 width=189) (actual time=67.741..270.379 rows=5,576 loops=1)

7. 1.134 157.630 ↓ 52.6 5,576 1

Nested Loop (cost=634.91..2,318.89 rows=106 width=152) (actual time=67.375..157.630 rows=5,576 loops=1)

8. 6.255 78.432 ↓ 52.6 5,576 1

Hash Right Join (cost=634.48..1,423.19 rows=106 width=131) (actual time=67.328..78.432 rows=5,576 loops=1)

  • Hash Cond: (reverseshipmenttable.suborderid = subordertable.suborderid)
9. 5.214 5.214 ↑ 1.0 28,017 1

Seq Scan on reverseshipmenttable (cost=0.00..715.17 rows=28,017 width=40) (actual time=0.014..5.214 rows=28,017 loops=1)

10. 2.070 66.963 ↓ 52.6 5,576 1

Hash (cost=633.15..633.15 rows=106 width=99) (actual time=66.963..66.963 rows=5,576 loops=1)

  • Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 906kB
11. 64.893 64.893 ↓ 52.6 5,576 1

Index Scan using idx_storeid_subordertable on subordertable (cost=0.43..633.15 rows=106 width=99) (actual time=0.069..64.893 rows=5,576 loops=1)

  • Index Cond: (storeid = 299293)
  • Filter: ((parentstoreid IS NOT NULL) AND ((subordermodeofpayment <> 'onlinepayment'::text) OR (paymentgatewaystatus = 'success'::text)))
  • Rows Removed by Filter: 84
12. 78.064 78.064 ↑ 1.0 1 5,576

Index Scan using ordertable_pkey on ordertable (cost=0.43..8.45 rows=1 width=29) (actual time=0.014..0.014 rows=1 loops=5,576)

  • Index Cond: (orderid = subordertable.orderid)
13. 111.520 111.520 ↑ 1.0 1 5,576

Index Scan using producttable_pkey on producttable (cost=0.57..8.59 rows=1 width=45) (actual time=0.020..0.020 rows=1 loops=5,576)

  • Index Cond: (productid = subordertable.productid)
14. 66.912 66.912 ↑ 1.0 1 5,576

Index Scan using shipmenttable_pkey on shipmenttable (cost=0.43..8.45 rows=1 width=47) (actual time=0.012..0.012 rows=1 loops=5,576)

  • Index Cond: (subordertable.subordershipmentid = shipmentid)
15. 100.368 100.368 ↑ 1.0 1 5,576

Index Scan using orderstatustimestamps_pkey on orderstatustimestamps (cost=0.43..8.45 rows=1 width=16) (actual time=0.018..0.018 rows=1 loops=5,576)

  • Index Cond: (subordertable.suborderid = suborderid)
16. 0.008 0.025 ↑ 105.8 12 1

Hash (cost=22.70..22.70 rows=1,270 width=36) (actual time=0.024..0.025 rows=12 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 17kB
17. 0.017 0.017 ↑ 105.8 12 1

Seq Scan on convertortable (cost=0.00..22.70 rows=1,270 width=36) (actual time=0.016..0.017 rows=12 loops=1)

18. 0.061 496.511 ↑ 20.0 10 1

GroupAggregate (cost=5,418.24..5,478.03 rows=200 width=528) (actual time=496.477..496.511 rows=10 loops=1)

  • Group Key: suborderdetails.orderid, ((suborderdetails.ordertimestamp + '05:30:00'::interval)), suborderdetails.ordercustomername
19. 5.206 496.450 ↑ 61.2 11 1

Sort (cost=5,418.24..5,419.92 rows=673 width=448) (actual time=496.448..496.450 rows=11 loops=1)

  • Sort Key: suborderdetails.orderid DESC, ((suborderdetails.ordertimestamp + '05:30:00'::interval)), suborderdetails.ordercustomername
  • Sort Method: quicksort Memory: 2443kB
20. 0.932 491.244 ↓ 8.3 5,576 1

Nested Loop Left Join (cost=0.43..5,386.63 rows=673 width=448) (actual time=67.893..491.244 rows=5,576 loops=1)

21. 451.280 451.280 ↓ 8.3 5,576 1

CTE Scan on suborderdetails (cost=0.00..13.46 rows=673 width=432) (actual time=67.857..451.280 rows=5,576 loops=1)

22. 39.032 39.032 ↓ 0.0 0 5,576

Index Scan using productfeedbacktable_pkey on productfeedbacktable (cost=0.43..7.98 rows=1 width=16) (actual time=0.007..0.007 rows=0 loops=5,576)

  • Index Cond: (suborderdetails.suborderid = suborderid)
Planning time : 6.929 ms
Execution time : 497.342 ms