explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.184 0.184 ↑ 2.0 1 1

CTE Scan on alloffersvalid (cost=59.93..59.97 rows=2 width=379) (actual time=0.184..0.184 rows=1 loops=1)

2.          

CTE alloffers

3. 0.006 0.108 ↑ 1.0 1 1

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

  • Hash Cond: (offers.offergroupid = offergroups.offergroupid)
4. 0.083 0.083 ↑ 41.0 1 1

Seq Scan on offers (cost=0.00..22.10 rows=41 width=1,004) (actual time=0.083..0.083 rows=1 loops=1)

  • Filter: ((isnewusersegment AND isoldusersegment) OR ((NOT isoldusersegment) AND isnewusersegment AND (maxstoreid < 2558909)) OR (isoldusersegment AND (NOT isnewusersegment) AND (maxstoreid >= 2558909)))
  • Rows Removed by Filter: 139
5. 0.001 0.019 ↑ 1.0 1 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
6. 0.018 0.018 ↑ 1.0 1 1

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

  • Filter: (status = 'running'::text)
  • Rows Removed by Filter: 116
7.          

CTE storebasedoffers

8. 0.001 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)

9. 0.010 0.045 ↑ 1.0 1 1

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

  • Hash Cond: (offers_1.offergroupid = offergroups_1.offergroupid)
10. 0.021 0.021 ↑ 1.0 140 1

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

11. 0.001 0.014 ↑ 1.0 1 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
12. 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
13. 0.010 0.010 ↓ 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.010..0.010 rows=0 loops=1)

  • Index Cond: ((offerid = offers_1.offerid) AND (storeid = 2558909))
  • Heap Fetches: 0
14.          

CTE alloffersvalid

15. 0.001 0.183 ↑ 2.0 1 1

Unique (cost=0.07..0.18 rows=2 width=379) (actual time=0.183..0.183 rows=1 loops=1)

16. 0.014 0.182 ↑ 2.0 1 1

Sort (cost=0.07..0.08 rows=2 width=379) (actual time=0.182..0.182 rows=1 loops=1)

  • Sort 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
  • Sort Method: quicksort Memory: 25kB
17. 0.000 0.168 ↑ 2.0 1 1

Append (cost=0.00..0.06 rows=2 width=379) (actual time=0.110..0.168 rows=1 loops=1)

18. 0.110 0.110 ↑ 1.0 1 1

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

19. 0.058 0.058 ↓ 0.0 0 1

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

Planning time : 0.468 ms
Execution time : 0.261 ms