explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ZFBX

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.023 1.039 ↑ 4.0 1 1

GroupAggregate (cost=131.17..131.40 rows=4 width=40) (actual time=1.039..1.039 rows=1 loops=1)

  • Group Key: (date_trunc('year'::text, t1.cal_date))
2. 0.024 1.016 ↑ 1.4 16 1

Sort (cost=131.17..131.23 rows=23 width=12) (actual time=1.014..1.016 rows=16 loops=1)

  • Sort Key: (date_trunc('year'::text, t1.cal_date))
  • Sort Method: quicksort Memory: 25kB
3. 0.818 0.992 ↑ 1.4 16 1

Nested Loop (cost=0.00..130.65 rows=23 width=12) (actual time=0.100..0.992 rows=16 loops=1)

  • Join Filter: (((date_trunc('seconds'::text, t1.cal_date) >= timezone('Asia/Kolkata'::text, timezone('UTC'::text, t2.period_start))) AND (date_trunc('seconds'::text, t1.cal_date) <= timezone('Asia/Kolkata'::text, timezone('UTC'::text, t2.period_end))) AND (date_trunc('year'::text, timezone('Asia/Kolkata'::text, now())) > date_trunc('year'::text, t1.cal_date))) OR ((date_trunc('year'::text, timezone('Asia/Kolkata'::text, now())) = date_trunc('year'::text, t1.cal_date)) AND (timezone('Asia/Kolkata'::text, now()) >= timezone('Asia/Kolkata'::text, timezone('UTC'::text, t2.period_start))) AND (timezone('Asia/Kolkata'::text, now()) <= timezone('Asia/Kolkata'::text, timezone('UTC'::text, t2.period_end)))))
  • Rows Removed by Join Filter: 88
4. 0.070 0.070 ↓ 1.0 52 1

Seq Scan on subscription_histories t2 (cost=0.00..2.05 rows=51 width=20) (actual time=0.025..0.070 rows=52 loops=1)

  • Filter: ((mrr > '0'::numeric) AND (data_source_id = '68000000000'::bigint))
  • Rows Removed by Filter: 18
5. 0.062 0.104 ↑ 2.0 2 52

Materialize (cost=0.00..111.72 rows=4 width=8) (actual time=0.001..0.002 rows=2 loops=52)

6. 0.042 0.042 ↑ 2.0 2 1

Seq Scan on yearly_calendars t1 (cost=0.00..111.70 rows=4 width=8) (actual time=0.031..0.042 rows=2 loops=1)

  • Filter: ((date_trunc('year'::text, cal_date) >= '2018-01-01 00:00:00'::timestamp without time zone) AND (date_trunc('year'::text, cal_date) <= '2019-12-31 23:59:59.999'::timestamp without time zone) AND ((date_trunc('year'::text, timezone('Asia/Kolkata'::text, now())) > date_trunc('year'::text, cal_date)) OR (date_trunc('year'::text, timezone('Asia/Kolkata'::text, now())) = date_trunc('year'::text, cal_date))))
  • Rows Removed by Filter: 14
Planning time : 0.704 ms
Execution time : 1.315 ms