explain.depesz.com

PostgreSQL's explain analyze made readable

Result: orTh

Settings
# exclusive inclusive rows x rows loops node
1. 0.052 5.377 ↑ 1.2 32 1

Sort (cost=1,842.09..1,842.19 rows=38 width=482) (actual time=5.375..5.377 rows=32 loops=1)

  • Sort Key: alloffers.sortindex
  • Sort Method: quicksort Memory: 43kB
2.          

CTE alloffers

3. 0.247 2.417 ↓ 1.4 32 1

Hash Join (cost=44.55..582.03 rows=23 width=665) (actual time=0.476..2.417 rows=32 loops=1)

  • Hash Cond: (offers.offergroupid = offergroups.offergroupid)
  • Join Filter: ((offers.isactivatedusersegment AND offers.isnonactivatedusersegment AND (offers.maxstoreid >= 3091735) AND (NOT (alternatives: SubPlan 1 or hashed SubPlan 2))) OR (offers.isnewusersegment AND (offers.maxstoreid < 3091735)) OR (offers.isactivatedusersegment AND (offers.maxstoreid >= 3091735) AND (NOT (alternatives: SubPlan 3 or hashed SubPlan 4))))
4. 1.974 1.974 ↑ 1.1 1,760 1

Seq Scan on offers (cost=0.00..532.49 rows=1,903 width=549) (actual time=0.013..1.974 rows=1,760 loops=1)

  • Filter: ((isactivatedusersegment AND isnonactivatedusersegment AND (maxstoreid >= 3091735)) OR (isnewusersegment AND (maxstoreid < 3091735)) OR (isactivatedusersegment AND (maxstoreid >= 3091735)))
  • Rows Removed by Filter: 1811
5. 0.026 0.111 ↑ 1.0 69 1

Hash (cost=43.66..43.66 rows=71 width=68) (actual time=0.111..0.111 rows=69 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
6. 0.085 0.085 ↑ 1.0 69 1

Index Scan using idx_status_offergroups on offergroups (cost=0.28..43.66 rows=71 width=68) (actual time=0.018..0.085 rows=69 loops=1)

  • Index Cond: (status = 'running'::text)
7.          

SubPlan (for Hash Join)

8. 0.035 0.035 ↓ 0.0 0 7

Index Only Scan using usersegmentsexcluded_offergroupid_offerid_storeid_key on usersegmentsexcluded (cost=0.42..8.44 rows=1 width=0) (actual time=0.005..0.005 rows=0 loops=7)

  • Index Cond: ((offergroupid = offergroups.offergroupid) AND (offerid = offers.offerid) AND (storeid = 3091735))
  • Heap Fetches: 0
9. 0.000 0.000 ↓ 0.0 0

Gather (cost=1,000.00..5,093.30 rows=2 width=16) (never executed)

  • Workers Planned: 1
  • Workers Launched: 0
10. 0.000 0.000 ↓ 0.0 0

Parallel Seq Scan on usersegmentsexcluded usersegmentsexcluded_1 (cost=0.00..4,093.10 rows=1 width=16) (never executed)

  • Filter: (storeid = 3091735)
11. 0.050 0.050 ↓ 0.0 0 25

Index Only Scan using usersegmentsexcluded_offergroupid_offerid_storeid_key on usersegmentsexcluded usersegmentsexcluded_2 (cost=0.42..8.44 rows=1 width=0) (actual time=0.002..0.002 rows=0 loops=25)

  • Index Cond: ((offergroupid = offergroups.offergroupid) AND (offerid = offers.offerid) AND (storeid = 3091735))
  • Heap Fetches: 0
12. 0.000 0.000 ↓ 0.0 0

Gather (cost=1,000.00..5,093.30 rows=2 width=16) (never executed)

  • Workers Planned: 1
  • Workers Launched: 0
13. 0.000 0.000 ↓ 0.0 0

Parallel Seq Scan on usersegmentsexcluded usersegmentsexcluded_3 (cost=0.00..4,093.10 rows=1 width=16) (never executed)

  • Filter: (storeid = 3091735)
14.          

CTE storebasedoffers

15. 0.000 2.708 ↓ 0.0 0 1

Nested Loop (cost=59.32..1,254.69 rows=15 width=665) (actual time=2.708..2.708 rows=0 loops=1)

16. 0.427 2.363 ↑ 1.1 69 1

Hash Join (cost=58.62..573.71 rows=78 width=601) (actual time=0.260..2.363 rows=69 loops=1)

  • Hash Cond: (offers_1.offergroupid = offergroups_1.offergroupid)
17. 1.826 1.826 ↑ 1.0 3,571 1

Seq Scan on offers offers_1 (cost=0.00..505.71 rows=3,571 width=541) (actual time=0.006..1.826 rows=3,571 loops=1)

18. 0.033 0.110 ↑ 1.0 69 1

Hash (cost=57.73..57.73 rows=71 width=68) (actual time=0.110..0.110 rows=69 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
19. 0.077 0.077 ↑ 1.0 69 1

Index Scan using idx_status_offergroups on offergroups offergroups_1 (cost=0.28..57.73 rows=71 width=68) (actual time=0.018..0.077 rows=69 loops=1)

  • Index Cond: (status = 'running'::text)
20. 0.345 0.345 ↓ 0.0 0 69

Index Only Scan using idx_offeridstoreid_usersegments on usersegments (cost=0.70..8.72 rows=1 width=8) (actual time=0.005..0.005 rows=0 loops=69)

  • Index Cond: ((offerid = offers_1.offerid) AND (storeid = 3091735))
  • Heap Fetches: 0
21. 0.123 5.325 ↑ 1.2 32 1

HashAggregate (cost=3.99..4.37 rows=38 width=482) (actual time=5.299..5.325 rows=32 loops=1)

  • Group Key: alloffers.offerid, alloffers.offergroupid, alloffers.offertype, alloffers.name, alloffers.segmentname, alloffers.starttime, alloffers.endtime, alloffers.showtimer, alloffers.timertext, alloffers.bannerimage, alloffers.leaderboardimage, alloffers.offerprogressimage, alloffers.bannerurl, alloffers.popupimage, alloffers.detailstext, alloffers.salestarget, alloffers.orderstarget, alloffers.isnewusersegment, alloffers.isoldusersegment, alloffers.isactivatedusersegment, alloffers.isnonactivatedusersegment, alloffers.showleaderboardorderscount, alloffers.leaderboardrankcount, alloffers.minordervaluetoconsider, alloffers.paymentmethodtoconsider, alloffers.sortindex, alloffers.targetlevels, alloffers.prizes
22. 0.005 5.202 ↑ 1.2 32 1

Append (cost=0.00..1.33 rows=38 width=482) (actual time=0.481..5.202 rows=32 loops=1)

23. 2.488 2.488 ↓ 1.4 32 1

CTE Scan on alloffers (cost=0.00..0.46 rows=23 width=482) (actual time=0.480..2.488 rows=32 loops=1)

24. 2.709 2.709 ↓ 0.0 0 1

CTE Scan on storebasedoffers (cost=0.00..0.30 rows=15 width=482) (actual time=2.709..2.709 rows=0 loops=1)

Planning time : 794.540 ms
Execution time : 5.585 ms