explain.depesz.com

PostgreSQL's explain analyze made readable

Result: kh4J : Optimization for: plan #pHAr

Settings

Optimization path:

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

GroupAggregate (cost=30,439,428.63..30,451,127.88 rows=73,120 width=56) (actual rows= loops=)

  • Group Key: t.zone, t.month, t.range
2. 0.000 0.000 ↓ 0.0

Sort (cost=30,439,428.63..30,441,256.64 rows=731,203 width=58) (actual rows= loops=)

  • Sort Key: t.zone, t.month, t.range
3. 0.000 0.000 ↓ 0.0

Subquery Scan on t (cost=30,294,944.71..30,313,224.78 rows=731,203 width=58) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

Unique (cost=30,294,944.71..30,305,912.75 rows=731,203 width=62) (actual rows= loops=)

5. 0.000 0.000 ↓ 0.0

Sort (cost=30,294,944.71..30,296,772.71 rows=731,203 width=62) (actual rows= loops=)

  • Sort Key: td.db_date, (concat(lpad(((n.id * 30))::text, 4, '0'::text), '_days')), ul.phone, ((SubPlan 1)), (CASE WHEN (SubPlan 2) THEN 1 ELSE 0 END)
6. 0.000 0.000 ↓ 0.0

Nested Loop (cost=52.37..30,168,740.85 rows=731,203 width=62) (actual rows= loops=)

  • Join Filter: ((td.db_date + ('30 days'::interval * (n.id)::double precision)) < now())
7. 0.000 0.000 ↓ 0.0

Hash Join (cost=52.37..48,769.56 rows=6,587 width=22) (actual rows= loops=)

  • Hash Cond: (ul.monthdt = td.db_date)
8. 0.000 0.000 ↓ 0.0

Seq Scan on user_retention_investor_legacy_upd ul (cost=0.00..43,640.07 rows=1,336,331 width=22) (actual rows= loops=)

  • Filter: ((zone)::text = 'NCR'::text)
9. 0.000 0.000 ↓ 0.0

Hash (cost=51.92..51.92 rows=36 width=4) (actual rows= loops=)

10. 0.000 0.000 ↓ 0.0

Index Scan using idx_db_dt on time_dimension td (cost=0.28..51.92 rows=36 width=4) (actual rows= loops=)

  • Index Cond: ((db_date >= '2016-09-01'::date) AND (db_date <= '2019-09-01'::date))
  • Filter: (day = 1)
11. 0.000 0.000 ↓ 0.0

Materialize (cost=0.00..21.67 rows=333 width=4) (actual rows= loops=)

12. 0.000 0.000 ↓ 0.0

Subquery Scan on n (cost=0.00..20.01 rows=333 width=4) (actual rows= loops=)

  • Filter: ((n.id)::double precision < '36.5'::double precision)
13. 0.000 0.000 ↓ 0.0

Result (cost=0.00..5.01 rows=1,000 width=4) (actual rows= loops=)

14.          

SubPlan (forNested Loop)

15. 0.000 0.000 ↓ 0.0

Limit (cost=0.43..20.55 rows=1 width=4) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

Index Scan using idx_phzone on user_retention_investor_legacy_upd uli (cost=0.43..20.55 rows=1 width=4) (actual rows= loops=)

  • Index Cond: (((ul.phone)::text = (phone)::text) AND ((ul.zone)::text = (zone)::text))
  • Filter: ((boarded_date <= ((date_of_ride)::date + ('30 days'::interval * (n.id)::double precision))) AND (boarded_date >= ((date_of_ride)::date + ('30 days'::interval * ((n.id - 1))::double precision))))
17. 0.000 0.000 ↓ 0.0

Index Scan using idx_phzone on user_retention_investor_legacy_upd uli_1 (cost=0.43..20.55 rows=1 width=0) (actual rows= loops=)

  • Index Cond: (((ul.phone)::text = (phone)::text) AND ((ul.zone)::text = (zone)::text))
  • Filter: ((boarded_date <= ((date_of_ride)::date + ('30 days'::interval * (n.id)::double precision))) AND (boarded_date >= ((date_of_ride)::date + ('30 days'::interval * ((n.id - 1))::double precision))))