explain.depesz.com

PostgreSQL's explain analyze made readable

Result: D4La

Settings
# exclusive inclusive rows x rows loops node
1. 3.671 22,543.248 ↑ 13.2 168 1

HashAggregate (cost=14,862,369.54..14,862,408.41 rows=2,221 width=44) (actual time=22,543.218..22,543.248 rows=168 loops=1)

  • Group Key: t.date, CASE WHEN (t.sum_amount = '1'::numeric) THEN '1'::text WHEN ((t.sum_amount >= '2'::numeric) AND (t.sum_amount <= '5'::numeric)) THEN '2-5'::text ELSE '>5'::text END
2.          

CTE main

3. 17.391 217.520 ↑ 1.7 13,152 1

HashAggregate (cost=68,128.71..68,406.26 rows=22,204 width=16) (actual time=214.379..217.520 rows=13,152 loops=1)

  • Group Key: (answers.created_at)::date, answers.account_id
4. 166.843 200.129 ↓ 1.0 22,844 1

Bitmap Heap Scan on answers (cost=15,150.36..67,962.18 rows=22,204 width=16) (actual time=37.483..200.129 rows=22,844 loops=1)

  • Recheck Cond: (created_at > '2018-11-01 00:00:00'::timestamp without time zone)
  • Filter: (NOT deleted)
  • Rows Removed by Filter: 2067
  • Heap Blocks: exact=20455
5. 33.286 33.286 ↑ 1.0 24,911 1

Bitmap Index Scan on answers_question_id_created_at_ix (cost=0.00..15,144.81 rows=25,403 width=0) (actual time=33.286..33.286 rows=24,911 loops=1)

  • Index Cond: (created_at > '2018-11-01 00:00:00'::timestamp without time zone)
6. 4.676 22,539.577 ↑ 1.7 13,152 1

Subquery Scan on t (cost=14,793,352.67..14,793,796.75 rows=22,204 width=44) (actual time=22,533.591..22,539.577 rows=13,152 loops=1)

7. 17.287 22,534.901 ↑ 1.7 13,152 1

Sort (cost=14,793,352.67..14,793,408.18 rows=22,204 width=44) (actual time=22,533.578..22,534.901 rows=13,152 loops=1)

  • Sort Key: m1.date, m1.account_id
  • Sort Method: quicksort Memory: 1412kB
8. 21.297 22,517.614 ↑ 1.7 13,152 1

Nested Loop (cost=666.13..14,791,749.70 rows=22,204 width=44) (actual time=221.865..22,517.614 rows=13,152 loops=1)

9. 216.829 216.829 ↑ 1.7 13,152 1

CTE Scan on main m1 (cost=0.00..444.08 rows=22,204 width=12) (actual time=214.383..216.829 rows=13,152 loops=1)

10. 26.304 22,279.488 ↑ 1.0 1 13,152

Aggregate (cost=666.13..666.14 rows=1 width=8) (actual time=1.694..1.694 rows=1 loops=13,152)

11. 22,253.184 22,253.184 ↓ 2.0 2 13,152

CTE Scan on main m2 (cost=0.00..666.12 rows=1 width=8) (actual time=0.649..1.692 rows=2 loops=13,152)

  • Filter: ((date <= m1.date) AND (m1.account_id = account_id) AND ((m1.date - '7 days'::interval) < date))
  • Rows Removed by Filter: 13150