explain.depesz.com

PostgreSQL's explain analyze made readable

Result: dm4 : Optimization for: Optimization for: plan #RZHP; plan #h8lo

Settings

Optimization path:

Optimization(s) for this plan:

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

CTE Scan on alloffersvalid (cost=110.22..110.30 rows=4 width=379) (actual time=0.205..0.205 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.017 0.017 ↑ 1.0 1 1

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

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

Seq Scan on offers (cost=0.00..21.40 rows=21 width=1,004) (actual time=0.041..0.041 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.013 0.013 ↑ 1.0 1 1

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

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

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

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

CTE olduseroffervalid

11. 0.000 0.037 ↓ 0.0 0 1

Nested Loop (cost=0.00..25.41 rows=1 width=1,045) (actual time=0.037..0.037 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.013..0.014 rows=1 loops=1)

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

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

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

CTE storebasedoffers

15. 0.000 0.056 ↓ 0.0 0 1

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

16. 0.019 0.048 ↑ 1.0 1 1

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

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

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

18. 0.001 0.016 ↑ 1.0 1 1

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

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

Seq Scan on offergroups offergroups_3 (cost=0.00..3.46 rows=1 width=49) (actual time=0.014..0.015 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.000 0.205 ↓ 0.0 0 1

HashAggregate (cost=0.33..0.37 rows=4 width=379) (actual time=0.205..0.205 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.003 0.205 ↓ 0.0 0 1

Append (cost=0.00..0.12 rows=4 width=379) (actual time=0.205..0.205 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.056 0.056 ↓ 0.0 0 1

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

Planning time : 0.637 ms
Execution time : 0.315 ms