explain.depesz.com

PostgreSQL's explain analyze made readable

Result: UyQh

Settings
# exclusive inclusive rows x rows loops node
1. 0.153 25.115 ↓ 54.0 54 1

Nested Loop Left Join (cost=1,384.43..1,445.33 rows=1 width=558) (actual time=23.964..25.115 rows=54 loops=1)

2. 0.056 24.962 ↓ 54.0 54 1

Nested Loop (cost=1,384.15..1,437.00 rows=1 width=511) (actual time=23.938..24.962 rows=54 loops=1)

  • Join Filter: (subordertableforresellers.wholesellerid = storetable.storeid)
3. 0.410 24.852 ↓ 54.0 54 1

Nested Loop (cost=1,383.87..1,432.94 rows=1 width=499) (actual time=23.922..24.852 rows=54 loops=1)

  • Join Filter: (subordertableforresellers.wholesellerid = shop101commissiontableforwholesellers.wholesellerid)
  • Rows Removed by Join Filter: 3294
4. 0.061 24.118 ↓ 54.0 54 1

Nested Loop Left Join (cost=1,383.87..1,429.55 rows=1 width=483) (actual time=23.903..24.118 rows=54 loops=1)

5. 0.034 24.057 ↓ 54.0 54 1

Nested Loop (cost=1,383.58..1,425.03 rows=1 width=318) (actual time=23.888..24.057 rows=54 loops=1)

6. 0.057 23.915 ↓ 54.0 54 1

Hash Join (cost=1,383.29..1,416.72 rows=1 width=248) (actual time=23.867..23.915 rows=54 loops=1)

  • Hash Cond: ((("*SELECT* 1".suborderstatusvalue)::numeric) = CASE WHEN ((shipmenttable_1.pickupdate IS NOT NULL) AND (subordertable.suborderstatusvalue = 2)) THEN 2.5 ELSE
7. 0.020 0.039 ↑ 115.5 11 1

HashAggregate (cost=51.31..64.02 rows=1,271 width=64) (actual time=0.027..0.039 rows=11 loops=1)

  • Group Key: (("*SELECT* 1".suborderstatusvalue)::numeric), "*SELECT* 1".suborderstatus
8. 0.002 0.019 ↑ 115.5 11 1

Append (cost=0.00..44.95 rows=1,271 width=64) (actual time=0.013..0.019 rows=11 loops=1)

9. 0.007 0.016 ↑ 127.0 10 1

Subquery Scan on *SELECT* 1 (cost=0.00..38.58 rows=1,270 width=64) (actual time=0.013..0.016 rows=10 loops=1)

10. 0.009 0.009 ↑ 127.0 10 1

Seq Scan on convertortable (cost=0.00..22.70 rows=1,270 width=36) (actual time=0.007..0.009 rows=10 loops=1)

11. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=64) (actual time=0.001..0.001 rows=1 loops=1)

12. 0.145 23.819 ↓ 54.0 54 1

Hash (cost=1,331.98..1,331.98 rows=1 width=220) (actual time=23.818..23.819 rows=54 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 20kB
13. 0.017 23.674 ↓ 54.0 54 1

Nested Loop Left Join (cost=1.13..1,331.98 rows=1 width=220) (actual time=0.666..23.674 rows=54 loops=1)

14. 0.041 23.603 ↓ 54.0 54 1

Nested Loop Left Join (cost=0.85..1,323.67 rows=1 width=213) (actual time=0.656..23.603 rows=54 loops=1)

15. 18.650 23.508 ↓ 54.0 54 1

Nested Loop Left Join (cost=0.57..1,319.15 rows=1 width=209) (actual time=0.642..23.508 rows=54 loops=1)

  • Join Filter: (subordertable.suborderid = subordertableforresellers_1.suborderid)
  • -> Seq Scan on subordertableforresellers subordertableforresellers_1 (cost=0.00..521.80 rows=1 width=29) (actual time=0.344..0.344 rows=0 loops=5
16. 0.063 4.858 ↓ 54.0 54 1

Nested Loop Left Join (cost=0.57..797.34 rows=1 width=188) (actual time=0.114..4.858 rows=54 loops=1)

  • Join Filter: (subordertable.suborderid = reversepickuprequesttable.suborderid)
  • Filter: ((wholesellerid = 9) AND (suborderid = ANY ('{13064,6700,6870,10251,8387,8388,8391,8402,8403,8407,8414,8413,6561,8418,8428,13655,1373
  • Rows Removed by Filter: 5668
17. 0.258 1.123 ↓ 54.0 54 1

Nested Loop Left Join (cost=0.57..779.87 rows=1 width=180) (actual time=0.043..1.123 rows=54 loops=1)

  • -> Index Scan using orderstatustimestamps_pkey on orderstatustimestamps (cost=0.29..5.41 rows=1 width=16) (actual time=0.004..0.004 r
18. 0.474 0.865 ↓ 54.0 54 1

Nested Loop (cost=0.29..774.45 rows=1 width=172) (actual time=0.033..0.865 rows=54 loops=1)

  • -> Index Scan using subordertable_pkey on subordertable (cost=0.29..124.08 rows=1 width=152) (actual time=0.004..0.004 rows=1 l
  • Index Cond: (subordertable.suborderid = suborderid)
19. 0.391 0.391 ↓ 27.0 54 1

Seq Scan on subordertableforresellers (cost=0.00..521.80 rows=2 width=28) (actual time=0.007..0.391 rows=54 loops=1)

  • Filter: ((resellerid = 9) AND (suborderid = ANY ('{13064,6700,6870,10251,8387,8388,8391,8402,8403,8407,8414,8413,6561,8418,
  • Rows Removed by Filter: 5614
  • Index Cond: ((suborderid = subordertableforresellers.suborderid) AND (suborderid = ANY ('{13064,6700,6870,10251,8387,8388,8
20. 3.672 3.672 ↓ 0.0 0 54

Seq Scan on reversepickuprequesttable (cost=0.00..16.80 rows=54 width=16) (actual time=0.068..0.068 rows=0 loops=54)

  • Filter: (suborderid = ANY ('{13064,6700,6870,10251,8387,8388,8391,8402,8403,8407,8414,8413,6561,8418,8428,13655,13736,13737,13740,16009
  • Rows Removed by Filter: 181
21. 0.054 0.054 ↓ 0.0 0 54

Index Scan using shipmenttable_pkey on shipmenttable shipmenttable_1 (cost=0.28..4.52 rows=1 width=12) (actual time=0.001..0.001 rows=0 loops=54)

  • Index Cond: (subordertable.subordershipmentid = shipmentid)
22. 0.054 0.054 ↓ 0.0 0 54

Index Scan using couponmanagementtable_pkey on couponmanagementtable (cost=0.28..8.29 rows=1 width=15) (actual time=0.001..0.001 rows=0 loops=54)

  • Index Cond: (couponid = CASE WHEN (subordertableforresellers_1.resellerid IS NULL) THEN subordertable.couponid ELSE '0'::bigint END)
23. 0.108 0.108 ↑ 1.0 1 54

Index Scan using producttable_pkey on producttable (cost=0.29..8.31 rows=1 width=78) (actual time=0.002..0.002 rows=1 loops=54)

  • Index Cond: (productid = CASE WHEN (subordertableforresellers_1.resellerid IS NULL) THEN subordertable.productid ELSE subordertableforresellers_1.productid END)
24. 0.000 0.000 ↓ 0.0 0 54

Index Scan using shipmenttable_pkey on shipmenttable (cost=0.28..4.52 rows=1 width=165) (actual time=0.000..0.000 rows=0 loops=54)

  • Index Cond: (subordertable.subordershipmentid = shipmentid)
25. 0.324 0.324 ↑ 1.0 62 54

Seq Scan on shop101commissiontableforwholesellers (cost=0.00..2.62 rows=62 width=16) (actual time=0.000..0.006 rows=62 loops=54)

26. 0.054 0.054 ↑ 1.0 1 54

Index Scan using storetable_pkey on storetable (cost=0.28..4.04 rows=1 width=28) (actual time=0.001..0.001 rows=1 loops=54)

  • Index Cond: (storeid = shop101commissiontableforwholesellers.wholesellerid)
27. 0.000 0.000 ↓ 0.0 0 54

Index Scan using storetable_pkey on storetable storetable_1 (cost=0.28..8.30 rows=1 width=28) (actual time=0.000..0.000 rows=0 loops=54)

  • Index Cond: (subordertableforresellers_1.resellerid = storeid)
Planning time : 6.394 ms
Execution time : 25.518 ms