explain.depesz.com

PostgreSQL's explain analyze made readable

Result: XN2W

Settings
# exclusive inclusive rows x rows loops node
1. 0.018 1.512 ↑ 1.0 1 1

Nested Loop Left Join (cost=392.38..474.03 rows=1 width=558) (actual time=1.501..1.512 rows=1 loops=1)

2. 0.005 1.493 ↑ 1.0 1 1

Nested Loop Left Join (cost=392.10..465.68 rows=1 width=511) (actual time=1.482..1.493 rows=1 loops=1)

3. 0.004 1.486 ↑ 1.0 1 1

Nested Loop (cost=391.82..457.38 rows=1 width=346) (actual time=1.475..1.486 rows=1 loops=1)

4. 0.006 1.476 ↑ 1.0 1 1

Nested Loop (cost=391.53..449.07 rows=1 width=276) (actual time=1.465..1.476 rows=1 loops=1)

  • Join Filter: (subordertableforresellers.wholesellerid = storetable.storeid)
5. 0.012 1.464 ↑ 6.0 1 1

Hash Join (cost=391.25..424.76 rows=6 width=264) (actual time=1.454..1.464 rows=1 loops=1)

  • Hash Cond: (subordertableforresellers.wholesellerid = shop101commissiontableforwholesellers.wholesellerid)
6. 0.013 1.406 ↑ 6.0 1 1

Hash Join (cost=387.86..421.28 rows=6 width=248) (actual time=1.396..1.406 rows=1 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.024 0.038 ↑ 115.5 11 1

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

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

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

9. 0.006 0.011 ↑ 127.0 10 1

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

10. 0.005 0.005 ↑ 127.0 10 1

Seq Scan on convertortable (cost=0.00..22.70 rows=1,270 width=36) (actual time=0.004..0.005 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.006 1.355 ↑ 1.0 1 1

Hash (cost=336.54..336.54 rows=1 width=220) (actual time=1.355..1.355 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
13. 0.005 1.349 ↑ 1.0 1 1

Nested Loop Left Join (cost=1.13..336.54 rows=1 width=220) (actual time=1.241..1.349 rows=1 loops=1)

14. 0.004 1.343 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.85..328.23 rows=1 width=213) (actual time=1.235..1.343 rows=1 loops=1)

15. 0.632 1.337 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.57..319.93 rows=1 width=209) (actual time=1.230..1.337 rows=1 loops=1)

  • Join Filter: (subordertable.suborderid = subordertableforresellers_1.suborderid)
  • -> Seq Scan on subordertableforresellers subordertableforresellers_1 (cost=0.00..149.02 rows=1 width=29) (actual time=0.627..0.627 rows=0 loops=1
16. 0.002 0.705 ↑ 1.0 1 1

Nested Loop (cost=0.57..170.89 rows=1 width=188) (actual time=0.598..0.705 rows=1 loops=1)

  • Filter: ((suborderid = 16233) AND (wholesellerid = 18))
  • Rows Removed by Filter: 5668
17. 0.002 0.062 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.57..21.86 rows=1 width=168) (actual time=0.049..0.062 rows=1 loops=1)

  • Join Filter: (subordertable.suborderid = reversepickuprequesttable.suborderid)
18. 0.034 0.034 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.57..16.62 rows=1 width=160) (actual time=0.021..0.034 rows=1 loops=1)

  • Join Filter: (subordertable.suborderid = orderstatustimestamps.suborderid)
  • -> Index Scan using subordertable_pkey on subordertable (cost=0.29..8.30 rows=1 width=152) (actual time=0.012..0.025 rows=1 loo
  • Index Cond: (suborderid = 16233)
  • -> Index Scan using orderstatustimestamps_pkey on orderstatustimestamps (cost=0.29..8.30 rows=1 width=16) (actual time=0.006..0
  • Index Cond: (suborderid = 16233)
19. 0.026 0.026 ↓ 0.0 0 1

Seq Scan on reversepickuprequesttable (cost=0.00..5.22 rows=1 width=16) (actual time=0.026..0.026 rows=0 loops=1)

  • Filter: (suborderid = 16233)
  • Rows Removed by Filter: 181
20. 0.641 0.641 ↑ 1.0 1 1

Seq Scan on subordertableforresellers (cost=0.00..149.02 rows=1 width=28) (actual time=0.547..0.641 rows=1 loops=1)

  • Filter: ((suborderid = 16233) AND (resellerid = 18))
  • Rows Removed by Filter: 5667
21. 0.002 0.002 ↓ 0.0 0 1

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

  • Index Cond: (subordertable.subordershipmentid = shipmentid)
22. 0.001 0.001 ↓ 0.0 0 1

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=1)

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

Hash (cost=2.62..2.62 rows=62 width=16) (actual time=0.046..0.046 rows=62 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
24. 0.029 0.029 ↑ 1.0 62 1

Seq Scan on shop101commissiontableforwholesellers (cost=0.00..2.62 rows=62 width=16) (actual time=0.011..0.029 rows=62 loops=1)

25. 0.006 0.006 ↑ 1.0 1 1

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

  • Index Cond: (storeid = shop101commissiontableforwholesellers.wholesellerid)
26. 0.006 0.006 ↑ 1.0 1 1

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

  • Index Cond: (productid = CASE WHEN (subordertableforresellers_1.resellerid IS NULL) THEN subordertable.productid ELSE subordertableforresellers_1.productid END)
27. 0.002 0.002 ↓ 0.0 0 1

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

  • Index Cond: (subordertable.subordershipmentid = shipmentid)
28. 0.001 0.001 ↓ 0.0 0 1

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

  • Index Cond: (subordertableforresellers_1.resellerid = storeid)
Planning time : 8.128 ms
Execution time : 1.781 ms