explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1UG2

Settings
# exclusive inclusive rows x rows loops node
1. 0.008 0.796 ↑ 1.8 4 1

Sort (cost=236.45..236.47 rows=7 width=482) (actual time=0.796..0.796 rows=4 loops=1)

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

CTE alloffers

3. 0.052 0.449 ↑ 1.2 4 1

Hash Join (cost=33.04..76.17 rows=5 width=473) (actual time=0.429..0.449 rows=4 loops=1)

  • Hash Cond: (offers.offergroupid = offergroups.offergroupid)
  • Join Filter: ((offers.isactivatedusersegment AND offers.isnonactivatedusersegment AND (offers.maxstoreid >= 3000) AND (NOT (alternatives: SubPlan 1 or hashed SubPlan 2))) OR
  • -> Index Only Scan using usersegmentsexcluded_offergroupid_offerid_storeid_key on usersegmentsexcluded usersegmentsexcluded_2 (cost=0.15..8.17 rows=1 width=0) (never exec
  • Index Cond: ((offergroupid = offergroups.offergroupid) AND (offerid = offers.offerid) AND (storeid = 3000))
  • Heap Fetches: 0
  • -> Index Only Scan using usersegmentsexcluded_offergroupid_offerid_storeid_key on usersegmentsexcluded usersegmentsexcluded_4 (cost=0.15..8.17 rows=1 width=0) (never exec
  • Index Cond: ((offergroupid = offergroups.offergroupid) AND (offerid = offers.offerid) AND (storeid = 3000))
  • Heap Fetches: 0
4. 0.269 0.269 ↑ 1.4 338 1

Seq Scan on offers (cost=0.00..41.88 rows=478 width=369) (actual time=0.036..0.269 rows=338 loops=1)

  • Filter: ((isactivatedusersegment AND isnonactivatedusersegment AND (maxstoreid >= 3000)) OR (isnewusersegment AND (maxstoreid < 3000)) OR (isactivatedusersegment AND (m
  • Rows Removed by Filter: 190
5. 0.005 0.096 ↑ 1.7 6 1

Hash (cost=32.91..32.91 rows=10 width=64) (actual time=0.095..0.096 rows=6 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
6. 0.091 0.091 ↑ 1.7 6 1

Seq Scan on offergroups (cost=0.00..32.91 rows=10 width=64) (actual time=0.045..0.091 rows=6 loops=1)

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

SubPlan (for Hash Join)

8. 0.000 0.000 ↓ 0.0 0

Index Only Scan using usersegmentsexcluded_offergroupid_offerid_storeid_key on usersegmentsexcluded (cost=0.15..8.17 rows=1 width=0) (never executed)

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

Seq Scan on usersegmentsexcluded usersegmentsexcluded_1 (cost=0.00..29.62 rows=8 width=16) (never executed)

  • Filter: (storeid = 3000)
10. 0.028 0.028 ↑ 1.0 1 4

Result (cost=8.46..8.47 rows=1 width=1) (actual time=0.007..0.007 rows=1 loops=4)

  • -> Index Only Scan using idx_creationdate_storeid_cohortactivatedusers on cohortactivatedusers (cost=0.43..8.46 rows=1 width=0) (actual time=0.006..0.006 rows=1 l
  • Index Cond: ((creationdate = (offergroups.createdon)::date) AND (storeid = 3000))
  • Heap Fetches: 4
11. 0.004 0.004 ↓ 0.0 0 1

Seq Scan on usersegmentsexcluded usersegmentsexcluded_3 (cost=0.00..29.62 rows=8 width=16) (actual time=0.004..0.004 rows=0 loops=1)

  • Filter: (storeid = 3000)
  • Rows Removed by Filter: 6
12. 0.000 0.000 ↓ 0.0 0

Result (cost=8.46..8.47 rows=1 width=1) (never executed)

  • -> Index Only Scan using idx_creationdate_storeid_cohortactivatedusers on cohortactivatedusers cohortactivatedusers_1 (cost=0.43..8.46 rows=1 width=0) (never exec
  • Index Cond: ((creationdate = (offergroups.createdon)::date) AND (storeid = 3000))
  • Heap Fetches: 0
13. 0.000 0.000 ↓ 0.0 0

Seq Scan on usersegmentsexcluded usersegmentsexcluded_5 (cost=0.00..29.62 rows=8 width=16) (never executed)

  • Filter: (storeid = 3000)
14.          

CTE storebasedoffers

15. 0.001 0.310 ↓ 0.0 0 1

Nested Loop (cost=33.46..159.38 rows=2 width=473) (actual time=0.310..0.310 rows=0 loops=1)

16. 0.056 0.285 ↑ 2.0 6 1

Hash Join (cost=33.04..70.92 rows=12 width=409) (actual time=0.281..0.285 rows=6 loops=1)

  • Hash Cond: (offers_1.offergroupid = offergroups_1.offergroupid)
17. 0.154 0.154 ↑ 1.0 528 1

Seq Scan on offers offers_1 (cost=0.00..36.44 rows=544 width=361) (actual time=0.003..0.154 rows=528 loops=1)

18. 0.003 0.075 ↑ 1.7 6 1

Hash (cost=32.91..32.91 rows=10 width=56) (actual time=0.074..0.075 rows=6 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
19. 0.072 0.072 ↑ 1.7 6 1

Seq Scan on offergroups offergroups_1 (cost=0.00..32.91 rows=10 width=56) (actual time=0.040..0.072 rows=6 loops=1)

  • Filter: (status = 'running'::text)
  • Rows Removed by Filter: 470
20. 0.024 0.024 ↓ 0.0 0 6

Index Only Scan using idx_offeridstoreid_usersegments on usersegments (cost=0.42..7.36 rows=1 width=8) (actual time=0.004..0.004 rows=0 loops=6)

  • Index Cond: ((offerid = offers_1.offerid) AND (storeid = 3000))
  • Heap Fetches: 0
21. 0.019 0.788 ↑ 1.8 4 1

HashAggregate (cost=0.74..0.81 rows=7 width=482) (actual time=0.786..0.788 rows=4 loops=1)

  • Group Key: alloffers.offerid, alloffers.offergroupid, alloffers.offertype, alloffers.name, alloffers.segmentname, alloffers.starttime, alloffers.endtime, alloffers.showtimer, a
22. 0.001 0.769 ↑ 1.8 4 1

Append (cost=0.00..0.25 rows=7 width=482) (actual time=0.433..0.769 rows=4 loops=1)

23. 0.457 0.457 ↑ 1.2 4 1

CTE Scan on alloffers (cost=0.00..0.10 rows=5 width=482) (actual time=0.433..0.457 rows=4 loops=1)

24. 0.311 0.311 ↓ 0.0 0 1

CTE Scan on storebasedoffers (cost=0.00..0.04 rows=2 width=482) (actual time=0.310..0.311 rows=0 loops=1)

Planning time : 0.899 ms
Execution time : 0.997 ms