explain.depesz.com

PostgreSQL's explain analyze made readable

Result: iLt7

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 5,743.824 ↑ 1.0 10 1

Limit (cost=7,266.76..7,269.84 rows=10 width=528) (actual time=5,743.788..5,743.824 rows=10 loops=1)

2.          

CTE suborderdetails

3. 3.639 5,729.385 ↓ 8.0 5,576 1

Hash Left Join (cost=1,107.11..7,218.01 rows=699 width=264) (actual time=26.195..5,729.385 rows=5,576 loops=1)

  • Hash Cond: (subordertable.suborderid = reverseshipmenttable.suborderid)
4. 2.225 5,704.805 ↓ 8.0 5,576 1

Hash Join (cost=41.73..6,149.97 rows=699 width=227) (actual time=5.089..5,704.805 rows=5,576 loops=1)

  • Hash Cond: (subordertable.suborderstatusvalue = convertortable.suborderstatusvalue)
5. 2.563 5,702.549 ↓ 50.7 5,576 1

Nested Loop Left Join (cost=3.15..6,086.68 rows=110 width=203) (actual time=5.032..5,702.549 rows=5,576 loops=1)

6. 3.977 3,469.586 ↓ 50.7 5,576 1

Nested Loop Left Join (cost=2.72..5,157.18 rows=110 width=195) (actual time=3.970..3,469.586 rows=5,576 loops=1)

7. 4.261 2,807.641 ↓ 50.7 5,576 1

Nested Loop Left Join (cost=2.29..4,235.44 rows=110 width=187) (actual time=3.080..2,807.641 rows=5,576 loops=1)

8. 5.812 2,106.380 ↓ 50.7 5,576 1

Nested Loop (cost=1.86..3,305.94 rows=110 width=156) (actual time=2.505..2,106.380 rows=5,576 loops=1)

9. 5.105 990.944 ↓ 50.7 5,576 1

Nested Loop (cost=1.29..2,361.32 rows=110 width=119) (actual time=1.640..990.944 rows=5,576 loops=1)

10. 1.369 121.559 ↓ 50.7 5,576 1

Nested Loop Left Join (cost=0.86..1,431.82 rows=110 width=98) (actual time=0.984..121.559 rows=5,576 loops=1)

  • Filter: ((subordertable.subordermodeofpayment <> 'onlinepayment'::text) OR (paymenttable.paymentgatewaystatus = 'success'::text))
  • Rows Removed by Filter: 84
11. 35.290 35.290 ↓ 46.4 5,660 1

Index Scan using idx_storeid_subordertable on subordertable (cost=0.43..408.00 rows=122 width=112) (actual time=0.981..35.290 rows=5,660 loops=1)

  • Index Cond: (storeid = 299293)
  • Filter: (parentstoreid IS NOT NULL)
12. 84.900 84.900 ↓ 0.0 0 5,660

Index Scan using paymenttable_pkey on paymenttable (cost=0.42..8.38 rows=1 width=16) (actual time=0.015..0.015 rows=0 loops=5,660)

  • Index Cond: (subordertable.suborderpaymentid = paymentid)
13. 864.280 864.280 ↑ 1.0 1 5,576

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

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

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

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

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

  • Index Cond: (subordertable.subordershipmentid = shipmentid)
16. 657.968 657.968 ↓ 0.0 0 5,576

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

  • Index Cond: (subordertable.suborderid = suborderid)
17. 2,230.400 2,230.400 ↑ 1.0 1 5,576

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

  • Index Cond: (subordertable.suborderid = suborderid)
18. 0.008 0.031 ↑ 105.8 12 1

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

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

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

20. 5.452 20.941 ↑ 1.1 25,404 1

Hash (cost=715.17..715.17 rows=28,017 width=40) (actual time=20.941..20.941 rows=25,404 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 2121kB
21. 15.489 15.489 ↑ 1.0 28,017 1

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

22. 0.064 5,743.820 ↑ 20.0 10 1

GroupAggregate (cost=48.75..110.43 rows=200 width=528) (actual time=5,743.786..5,743.820 rows=10 loops=1)

  • Group Key: suborderdetails.orderid, ((suborderdetails.ordertimestamp + '05:30:00'::interval)), suborderdetails.ordercustomername
23. 5.415 5,743.756 ↑ 63.5 11 1

Sort (cost=48.75..50.50 rows=699 width=448) (actual time=5,743.754..5,743.756 rows=11 loops=1)

  • Sort Key: suborderdetails.orderid DESC, ((suborderdetails.ordertimestamp + '05:30:00'::interval)), suborderdetails.ordercustomername
  • Sort Method: quicksort Memory: 2531kB
24. 5,738.341 5,738.341 ↓ 8.0 5,576 1

CTE Scan on suborderdetails (cost=0.00..15.73 rows=699 width=448) (actual time=26.202..5,738.341 rows=5,576 loops=1)

Planning time : 32.903 ms
Execution time : 5,744.728 ms