explain.depesz.com

PostgreSQL's explain analyze made readable

Result: RZHP

Settings

Optimization(s) for this plan:

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

Unique (cost=110.05..110.06 rows=4 width=379) (actual time=1.140..1.140 rows=0 loops=1)

2.          

CTE alloffers

3. 0.002 0.066 ↓ 0.0 0 1

Nested Loop (cost=0.00..25.12 rows=1 width=1,045) (actual time=0.066..0.066 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.047 0.047 ↓ 0.0 0 1

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

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

CTE newoffervalid

7. 0.000 0.052 ↓ 0.0 0 1

Nested Loop (cost=0.00..25.30 rows=1 width=1,045) (actual time=0.052..0.052 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.038 0.038 ↓ 0.0 0 1

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

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

CTE olduseroffervalid

11. 0.001 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.013 0.013 ↑ 1.0 1 1

Seq Scan on offergroups offergroups_2 (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.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.004 0.976 ↓ 0.0 0 1

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

16. 0.025 0.060 ↑ 1.0 1 1

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

  • Hash Cond: (offers_3.offergroupid = offergroups_3.offergroupid)
17. 0.017 0.017 ↑ 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.017 rows=140 loops=1)

18. 0.003 0.018 ↑ 1.0 1 1

Hash (cost=3.46..3.46 rows=1 width=49) (actual time=0.018..0.018 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.912 0.912 ↓ 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.912..0.912 rows=0 loops=1)

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

CTE alloffersvalid

22. 0.000 1.133 ↓ 0.0 0 1

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

23. 0.066 0.066 ↓ 0.0 0 1

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

24. 0.053 0.053 ↓ 0.0 0 1

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

25. 0.037 0.037 ↓ 0.0 0 1

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

26. 0.977 0.977 ↓ 0.0 0 1

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

27. 0.006 1.140 ↓ 0.0 0 1

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

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

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