explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8lcO

Settings
# exclusive inclusive rows x rows loops node
1. 6.799 27,535.753 ↑ 6.1 313 1

Hash Join (cost=5,033.94..11,569.20 rows=1,896 width=20) (actual time=2,016.159..27,535.753 rows=313 loops=1)

  • Hash Cond: (sanitized_activity.activity_date = curr_d.d)
2. 12,358.697 27,384.274 ↑ 2.4 313 1

Merge Left Join (cost=4,992.79..11,459.30 rows=766 width=16) (actual time=1,871.451..27,384.274 rows=313 loops=1)

  • Merge Cond: (sanitized_activity.timeofday_id = i.timeofday_id)
  • Join Filter: (((row_number() OVER (?)) = (row_number() OVER (?))) AND ((i.day_num)::double precision = ((date_part('dow'::text, (sanitized_activity.activity_date)::timestamp without time zone)) + '1'::double precision)))
  • Rows Removed by Join Filter: 1387334
3. 10.763 19.620 ↑ 2.4 313 1

WindowAgg (cost=52.02..75.00 rows=766 width=76) (actual time=5.805..19.620 rows=313 loops=1)

4. 5.929 8.857 ↑ 2.4 313 1

Sort (cost=52.02..53.93 rows=766 width=20) (actual time=5.771..8.857 rows=313 loops=1)

  • Sort Key: sanitized_activity.timeofday_id, sanitized_activity.activity_date, sanitized_activity.order_num
  • Sort Method: quicksort Memory: 49kB
5. 2.928 2.928 ↑ 2.4 313 1

CTE Scan on sanitized_activity (cost=0.00..15.32 rows=766 width=20) (actual time=0.010..2.928 rows=313 loops=1)

6. 12,000.028 15,005.957 ↓ 28.3 1,392,427 1

Materialize (cost=4,940.78..6,786.08 rows=49,208 width=22) (actual time=1,765.813..15,005.957 rows=1,392,427 loops=1)

7. 851.777 3,005.929 ↑ 1.1 44,872 1

WindowAgg (cost=4,940.78..6,170.98 rows=49,208 width=58) (actual time=1,765.796..3,005.929 rows=44,872 loops=1)

8. 846.165 2,154.152 ↑ 1.1 44,875 1

Sort (cost=4,940.78..5,063.80 rows=49,208 width=14) (actual time=1,764.364..2,154.152 rows=44,875 loops=1)

  • Sort Key: i.timeofday_id, i.day_num, i.order_num
  • Sort Method: external merge Disk: 1360kB
9. 864.127 1,307.987 ↑ 1.0 49,208 1

Hash Join (cost=35.11..1,105.85 rows=49,208 width=14) (actual time=19.959..1,307.987 rows=49,208 loops=1)

  • Hash Cond: (i.template_id = t.template_id)
10. 423.944 423.944 ↑ 1.0 49,208 1

Seq Scan on template_item i (cost=0.00..941.08 rows=49,208 width=18) (actual time=0.018..423.944 rows=49,208 loops=1)

11. 9.921 19.916 ↑ 1.0 1,116 1

Hash (cost=21.16..21.16 rows=1,116 width=4) (actual time=19.908..19.916 rows=1,116 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 56kB
12. 9.995 9.995 ↑ 1.0 1,116 1

Seq Scan on template t (cost=0.00..21.16 rows=1,116 width=4) (actual time=0.012..9.995 rows=1,116 loops=1)

13. 0.366 144.680 ↑ 13.4 37 1

Hash (cost=34.96..34.96 rows=495 width=8) (actual time=144.672..144.680 rows=37 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
14. 10.639 144.314 ↑ 13.4 37 1

Hash Join (cost=6.50..34.96 rows=495 width=8) (actual time=52.912..144.314 rows=37 loops=1)

  • Hash Cond: (avail_d.d_order = curr_d.d_order)
15. 112.497 112.497 ↓ 2.2 1,107 1

CTE Scan on available_date avail_d (cost=0.00..9.90 rows=495 width=12) (actual time=31.697..112.497 rows=1,107 loops=1)

16. 0.387 21.178 ↑ 5.4 37 1

Hash (cost=4.00..4.00 rows=200 width=12) (actual time=21.170..21.178 rows=37 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
17. 20.791 20.791 ↑ 5.4 37 1

CTE Scan on curr_date curr_d (cost=0.00..4.00 rows=200 width=12) (actual time=19.006..20.791 rows=37 loops=1)

Planning time : 1.802 ms