explain.depesz.com

PostgreSQL's explain analyze made readable

Result: h8lo : Optimization for: plan #RZHP

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.000 0.215 ↓ 0.0 0 1

Unique (cost=110.34..110.36 rows=4 width=379) (actual time=0.215..0.215 rows=0 loops=1)

2.          

CTE alloffers

3. 0.001 0.059 ↓ 0.0 0 1

Nested Loop (cost=0.00..25.12 rows=1 width=1,045) (actual time=0.059..0.059 rows=0 loops=1)

  • Join Filter: (offers.offergroupid = offergroups.offergroupid)
4. 0.019 0.019 ↑ 1.0 1 1

Seq Scan on offergroups (cost=0.00..3.46 rows=1 width=49) (actual time=0.018..0.019 rows=1 loops=1)

  • Filter: (status = 'running'::text)
  • Rows Removed by Filter: 116
5. 0.039 0.039 ↓ 0.0 0 1

Seq Scan on offers (cost=0.00..21.40 rows=21 width=1,004) (actual time=0.039..0.039 rows=0 loops=1)

  • Filter: (isnewusersegment AND isoldusersegment)
  • Rows Removed by Filter: 140
6.          

CTE newoffervalid

7. 0.001 0.049 ↓ 0.0 0 1

Nested Loop (cost=0.00..25.30 rows=1 width=1,045) (actual time=0.049..0.049 rows=0 loops=1)

  • Join Filter: (offers_1.offergroupid = offergroups_1.offergroupid)
8. 0.014 0.014 ↑ 1.0 1 1

Seq Scan on offergroups offergroups_1 (cost=0.00..3.46 rows=1 width=49) (actual time=0.013..0.014 rows=1 loops=1)

  • Filter: (status = 'running'::text)
  • Rows Removed by Filter: 116
9. 0.034 0.034 ↓ 0.0 0 1

Seq Scan on offers offers_1 (cost=0.00..21.75 rows=7 width=1,004) (actual time=0.034..0.034 rows=0 loops=1)

  • Filter: ((NOT isoldusersegment) AND isnewusersegment AND (maxstoreid < 2558909))
  • Rows Removed by Filter: 140
10.          

CTE olduseroffervalid

11. 0.000 0.038 ↓ 0.0 0 1

Nested Loop (cost=0.00..25.41 rows=1 width=1,045) (actual time=0.038..0.038 rows=0 loops=1)

  • Join Filter: (offers_2.offergroupid = offergroups_2.offergroupid)
12. 0.014 0.014 ↑ 1.0 1 1

Seq Scan on offergroups offergroups_2 (cost=0.00..3.46 rows=1 width=49) (actual time=0.014..0.014 rows=1 loops=1)

  • Filter: (status = 'running'::text)
  • Rows Removed by Filter: 116
13. 0.024 0.024 ↓ 0.0 0 1

Seq Scan on offers offers_2 (cost=0.00..21.75 rows=16 width=1,004) (actual time=0.024..0.024 rows=0 loops=1)

  • Filter: (isoldusersegment AND (NOT isnewusersegment) AND (maxstoreid >= 2558909))
  • Rows Removed by Filter: 140
14.          

CTE storebasedoffers

15. 0.001 0.064 ↓ 0.0 0 1

Nested Loop (cost=4.04..34.01 rows=1 width=1,045) (actual time=0.064..0.064 rows=0 loops=1)

16. 0.026 0.055 ↑ 1.0 1 1

Hash Join (cost=3.48..25.41 rows=1 width=1,045) (actual time=0.054..0.055 rows=1 loops=1)

  • Hash Cond: (offers_3.offergroupid = offergroups_3.offergroupid)
17. 0.014 0.014 ↑ 1.0 140 1

Seq Scan on offers offers_3 (cost=0.00..21.40 rows=140 width=1,004) (actual time=0.000..0.014 rows=140 loops=1)

18. 0.001 0.015 ↑ 1.0 1 1

Hash (cost=3.46..3.46 rows=1 width=49) (actual time=0.015..0.015 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
19. 0.014 0.014 ↑ 1.0 1 1

Seq Scan on offergroups offergroups_3 (cost=0.00..3.46 rows=1 width=49) (actual time=0.014..0.014 rows=1 loops=1)

  • Filter: (status = 'running'::text)
  • Rows Removed by Filter: 116
20. 0.008 0.008 ↓ 0.0 0 1

Index Only Scan using idx_offeridstoreid_usersegments on usersegments (cost=0.57..8.59 rows=1 width=8) (actual time=0.008..0.008 rows=0 loops=1)

  • Index Cond: ((offerid = offers_3.offerid) AND (storeid = 2558909))
  • Heap Fetches: 0
21.          

CTE alloffersvalid

22. 0.001 0.211 ↓ 0.0 0 1

HashAggregate (cost=0.33..0.37 rows=4 width=379) (actual time=0.211..0.211 rows=0 loops=1)

  • Group Key: alloffers.offerid, alloffers.offergroupid, alloffers.offertype, alloffers.name, alloffers.segmentname, alloffers.starttime, alloffers.endtime, alloffers.bannerimage, alloffers.leaderboardimage, alloffers.offerprogressimage, alloffers.bannerurl, alloffers.popupimage, alloffers.detailstext, alloffers.salestarget, alloffers.orderstarget, alloffers.isnewusersegment, alloffers.isoldusersegment, alloffers.showleaderboardorderscount, alloffers.leaderboardrankcount, alloffers.minordervaluetoconsider, alloffers.paymentmethodtoconsider
23. 0.000 0.210 ↓ 0.0 0 1

Append (cost=0.00..0.12 rows=4 width=379) (actual time=0.210..0.210 rows=0 loops=1)

24. 0.059 0.059 ↓ 0.0 0 1

CTE Scan on alloffers (cost=0.00..0.02 rows=1 width=379) (actual time=0.059..0.059 rows=0 loops=1)

25. 0.049 0.049 ↓ 0.0 0 1

CTE Scan on newoffervalid (cost=0.00..0.02 rows=1 width=379) (actual time=0.049..0.049 rows=0 loops=1)

26. 0.038 0.038 ↓ 0.0 0 1

CTE Scan on olduseroffervalid (cost=0.00..0.02 rows=1 width=379) (actual time=0.038..0.038 rows=0 loops=1)

27. 0.064 0.064 ↓ 0.0 0 1

CTE Scan on storebasedoffers (cost=0.00..0.02 rows=1 width=379) (actual time=0.064..0.064 rows=0 loops=1)

28. 0.004 0.215 ↓ 0.0 0 1

Sort (cost=0.12..0.13 rows=4 width=379) (actual time=0.215..0.215 rows=0 loops=1)

  • Sort Key: alloffersvalid.offerid
  • Sort Method: quicksort Memory: 25kB
29. 0.211 0.211 ↓ 0.0 0 1

CTE Scan on alloffersvalid (cost=0.00..0.08 rows=4 width=379) (actual time=0.211..0.211 rows=0 loops=1)

Planning time : 0.643 ms
Execution time : 0.339 ms