explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mlGi

Settings
# exclusive inclusive rows x rows loops node
1. 0.031 19.435 ↓ 15.0 15 1

WindowAgg (cost=574.02..574.04 rows=1 width=297) (actual time=19.415..19.435 rows=15 loops=1)

2.          

CTE foo

3. 1.224 19.349 ↓ 15.0 15 1

GroupAggregate (cost=5.97..573.99 rows=1 width=289) (actual time=0.762..19.349 rows=15 loops=1)

  • Group Key: collegeteammemberinfotable.storeid
4. 0.111 18.125 ↓ 296.0 296 1

Nested Loop Left Join (cost=5.97..573.64 rows=1 width=88) (actual time=0.151..18.125 rows=296 loops=1)

  • Filter: ((subordertable.subordermodeofpayment = 'cod'::text) OR (paymenttable.paymentgatewaystatus = 'success'::text))
  • Rows Removed by Filter: 65
5. 0.145 16.931 ↓ 361.0 361 1

Nested Loop (cost=5.54..568.84 rows=1 width=88) (actual time=0.138..16.931 rows=361 loops=1)

6. 0.137 15.342 ↓ 361.0 361 1

Nested Loop Left Join (cost=5.11..563.99 rows=1 width=96) (actual time=0.126..15.342 rows=361 loops=1)

7. 0.100 14.122 ↓ 361.0 361 1

Nested Loop (cost=4.67..559.13 rows=1 width=92) (actual time=0.116..14.122 rows=361 loops=1)

  • Join Filter: (collegeteammemberinfotable.storeid = subordertable.storeid)
8. 0.018 0.198 ↓ 16.0 16 1

Nested Loop (cost=4.24..81.33 rows=1 width=54) (actual time=0.051..0.198 rows=16 loops=1)

9. 0.032 0.116 ↓ 16.0 16 1

Nested Loop (cost=3.81..73.05 rows=1 width=34) (actual time=0.038..0.116 rows=16 loops=1)

  • Join Filter: (collegeteammemberinfotable.teamnumber = collegeteaminfotable.teamnumber)
  • Rows Removed by Join Filter: 120
10. 0.012 0.052 ↓ 16.0 16 1

Merge Anti Join (cost=3.81..71.69 rows=1 width=21) (actual time=0.031..0.052 rows=16 loops=1)

  • Merge Cond: (collegeteammemberinfotable.storeid = bannedstorestable.storeid)
11. 0.016 0.016 ↑ 103.8 16 1

Index Scan using collegeteammemberinfotable_pkey on collegeteammemberinfotable (cost=0.15..46.05 rows=1,660 width=21) (actual time=0.010..0.016 rows=16 loops=1)

12. 0.024 0.024 ↑ 1,065.2 10 1

Index Only Scan using idx_storeid_bannedstorestable on bannedstorestable (cost=0.29..11,932.33 rows=10,652 width=8) (actual time=0.016..0.024 rows=10 loops=1)

  • Heap Fetches: 10
13. 0.032 0.032 ↑ 2.0 8 16

Seq Scan on collegeteaminfotable (cost=0.00..1.16 rows=16 width=21) (actual time=0.001..0.002 rows=8 loops=16)

14. 0.064 0.064 ↑ 1.0 1 16

Index Scan using storetable_pkey on storetable (cost=0.43..8.29 rows=1 width=20) (actual time=0.004..0.004 rows=1 loops=16)

  • Index Cond: (storeid = collegeteammemberinfotable.storeid)
15. 13.824 13.824 ↑ 10.2 23 16

Index Scan using idx_storeid_subordertable on subordertable (cost=0.43..474.87 rows=234 width=54) (actual time=0.487..0.864 rows=23 loops=16)

  • Index Cond: (storeid = storetable.storeid)
  • Filter: (parentstoreid IS NOT NULL)
  • Rows Removed by Filter: 781
16. 1.083 1.083 ↑ 1.0 1 361

Index Scan using shipmenttable_pkey on shipmenttable (cost=0.43..4.86 rows=1 width=20) (actual time=0.003..0.003 rows=1 loops=361)

  • Index Cond: (subordertable.subordershipmentid = shipmentid)
17. 1.444 1.444 ↑ 1.0 1 361

Index Only Scan using idx_suborderid_subordertableforresellers on subordertableforresellers (cost=0.43..4.85 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=361)

  • Index Cond: (suborderid = subordertable.suborderid)
  • Heap Fetches: 361
18. 1.083 1.083 ↑ 1.0 1 361

Index Scan using paymenttable_pkey on paymenttable (cost=0.43..4.78 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=361)

  • Index Cond: (subordertable.suborderpaymentid = paymentid)
19. 0.020 19.404 ↓ 15.0 15 1

Sort (cost=0.03..0.04 rows=1 width=289) (actual time=19.402..19.404 rows=15 loops=1)

  • Sort Key: foo.totalpoints DESC, foo.mintimestamp
  • Sort Method: quicksort Memory: 28kB
20. 19.384 19.384 ↓ 15.0 15 1

CTE Scan on foo (cost=0.00..0.02 rows=1 width=289) (actual time=0.766..19.384 rows=15 loops=1)

Planning time : 5.868 ms
Execution time : 19.770 ms