explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5ogN

Settings
# exclusive inclusive rows x rows loops node
1. 0.127 2.010 ↑ 1.2 72 1

Hash Join (cost=854.90..890.49 rows=89 width=403) (actual time=1.896..2.010 rows=72 loops=1)

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

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

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

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

4. 0.006 0.012 ↑ 127.0 10 1

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

5. 0.006 0.006 ↑ 127.0 10 1

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

6. 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)

7. 0.093 1.843 ↓ 5.1 72 1

Hash (cost=803.42..803.42 rows=14 width=406) (actual time=1.843..1.843 rows=72 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 43kB
8. 0.073 1.750 ↓ 5.1 72 1

Nested Loop Left Join (cost=332.99..803.42 rows=14 width=406) (actual time=1.062..1.750 rows=72 loops=1)

9. 0.042 1.677 ↓ 5.1 72 1

Hash Left Join (cost=332.71..703.22 rows=14 width=241) (actual time=1.053..1.677 rows=72 loops=1)

  • Hash Cond: (subordertable.couponid = couponmanagementtable.couponid)
10. 0.195 0.900 ↓ 5.1 72 1

Nested Loop Left Join (cost=269.90..640.37 rows=14 width=234) (actual time=0.305..0.900 rows=72 loops=1)

11. 0.071 0.705 ↓ 5.1 72 1

Nested Loop Left Join (cost=269.61..540.16 rows=14 width=230) (actual time=0.302..0.705 rows=72 loops=1)

12. 0.058 0.562 ↓ 5.1 72 1

Nested Loop (cost=269.33..435.93 rows=14 width=222) (actual time=0.295..0.562 rows=72 loops=1)

13. 0.093 0.360 ↓ 5.1 72 1

Bitmap Heap Scan on subordertable (cost=269.04..319.62 rows=14 width=152) (actual time=0.276..0.360 rows=72 loops=1)

  • Recheck Cond: ((storeid = 9) AND (suborderid = ANY ('{6543,7515,7990,7989,10087,10120,6314,6315,6347,6352,6351,6349,6455,6454,6453,6452,6468,6477,6476,6544,101
  • Heap Blocks: exact=45
14. 0.019 0.267 ↓ 0.0 0 1

BitmapAnd (cost=269.04..269.04 rows=14 width=0) (actual time=0.267..0.267 rows=0 loops=1)

15. 0.176 0.176 ↓ 1.0 3,635 1

Bitmap Index Scan on idx_storeid_subordertable (cost=0.00..75.54 rows=3,634 width=0) (actual time=0.176..0.176 rows=3,635 loops=1)

  • Index Cond: (storeid = 9)
16. 0.072 0.072 ↑ 1.0 72 1

Bitmap Index Scan on subordertable_pkey (cost=0.00..193.24 rows=72 width=0) (actual time=0.072..0.072 rows=72 loops=1)

  • Index Cond: (suborderid = ANY ('{6543,7515,7990,7989,10087,10120,6314,6315,6347,6352,6351,6349,6455,6454,6453,6452,6468,6477,6476,6544,10161,6869,6
17. 0.144 0.144 ↑ 1.0 1 72

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

  • Index Cond: (productid = subordertable.productid)
18. 0.072 0.072 ↑ 1.0 1 72

Index Scan using orderstatustimestamps_pkey on orderstatustimestamps (cost=0.29..7.45 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=72)

  • Index Cond: (subordertable.suborderid = suborderid)
19. 0.000 0.000 ↓ 0.0 0 72

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

  • Index Cond: (subordertable.subordershipmentid = shipmentid)
20. 0.341 0.735 ↑ 1.0 1,779 1

Hash (cost=40.14..40.14 rows=1,814 width=15) (actual time=0.735..0.735 rows=1,779 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 100kB
21. 0.394 0.394 ↑ 1.0 1,779 1

Seq Scan on couponmanagementtable (cost=0.00..40.14 rows=1,814 width=15) (actual time=0.005..0.394 rows=1,779 loops=1)

22. 0.000 0.000 ↓ 0.0 0 72

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

  • Index Cond: (subordertable.subordershipmentid = shipmentid)
Planning time : 2.405 ms
Execution time : 2.158 ms