explain.depesz.com

PostgreSQL's explain analyze made readable

Result: kJqg

Settings
# exclusive inclusive rows x rows loops node
1. 55.056 345.288 ↑ 14.3 14 1

GroupAggregate (cost=132,205.52..132,225.82 rows=200 width=100) (actual time=283.216..345.288 rows=14 loops=1)

  • Group Key: t.year, t.month, t.room_type
2.          

CTE t

3. 45.494 218.549 ↓ 77.9 49,239 1

GroupAggregate (cost=132,122.40..132,163.48 rows=632 width=48) (actual time=158.946..218.549 rows=49,239 loops=1)

  • Group Key: cm.year, cm.month, cm.room_type, cm.bedrooms, cm.airbnb_property_id
4. 93.028 173.055 ↓ 77.9 49,239 1

Sort (cost=132,122.40..132,123.98 rows=632 width=20) (actual time=158.921..173.055 rows=49,239 loops=1)

  • Sort Key: cm.year, cm.month, cm.bedrooms, cm.airbnb_property_id
  • Sort Method: quicksort Memory: 5383kB
5. 80.027 80.027 ↓ 77.9 49,239 1

Index Scan using cm_cityid_date on calendar_metric_properties cm (cost=0.57..132,093.00 rows=632 width=20) (actual time=36.341..80.027 rows=49,239 loops=1)

  • Index Cond: (city_id = 59380)
  • Filter: ((days_r > 0) AND (room_type = 'E'::bpchar) AND (make_date((year)::integer, (month)::integer, 1) >= '2018-05-01 00:00:00'::timestamp without time zone) AND (make_date((year)::integer, (month)::integer, 1) <= '2019-06-01'::date))
  • Rows Removed by Filter: 158691
6. 31.186 290.232 ↓ 77.9 49,239 1

Sort (cost=42.04..43.62 rows=632 width=48) (actual time=279.049..290.232 rows=49,239 loops=1)

  • Sort Key: t.year, t.month, t.room_type
  • Sort Method: quicksort Memory: 5383kB
7. 259.046 259.046 ↓ 77.9 49,239 1

CTE Scan on t (cost=0.00..12.64 rows=632 width=48) (actual time=158.951..259.046 rows=49,239 loops=1)

Planning time : 0.199 ms
Execution time : 346.762 ms