explain.depesz.com

PostgreSQL's explain analyze made readable

Result: udVN

Settings
# exclusive inclusive rows x rows loops node
1. 0.012 308.620 ↑ 1.0 1 1

Aggregate (cost=62,592.84..62,592.85 rows=1 width=8) (actual time=308.620..308.620 rows=1 loops=1)

  • Buffers: shared hit=273795
2.          

CTE users_with_transaction

3. 1.856 5.175 ↑ 1.3 2,337 1

HashAggregate (cost=6,785.08..6,814.56 rows=2,948 width=8) (actual time=4.434..5.175 rows=2,337 loops=1)

  • Group Key: transactions.user_id
  • Buffers: shared hit=267
4. 3.319 3.319 ↓ 1.1 3,568 1

Index Scan using transactions_immutable_to_char_idx1 on transactions (cost=0.43..6,776.92 rows=3,265 width=8) (actual time=0.021..3.319 rows=3,568 loops=1)

  • Index Cond: (immutable_to_char(created_at, 'DD.MM.YYYY'::text) = '16.09.2019'::text)
  • Filter: (((status)::text = 'approved'::text) AND ((((transaction_type)::text = 'income'::text) AND ((operation_type)::text <> 'bonus'::text)) OR (((transaction_type)::text = 'expense'::text) AND ((method)::text <> 'wallet'::text))))
  • Rows Removed by Filter: 3558
  • Buffers: shared hit=267
5.          

CTE first_active_transaction

6. 32.199 304.243 ↑ 6.7 2,337 1

HashAggregate (cost=51,383.93..51,539.42 rows=15,549 width=16) (actual time=303.815..304.243 rows=2,337 loops=1)

  • Group Key: tr.user_id
  • Buffers: shared hit=273795
7. 16.100 272.044 ↓ 1.5 190,606 1

Nested Loop (cost=0.43..50,747.93 rows=127,200 width=16) (actual time=4.467..272.044 rows=190,606 loops=1)

  • Buffers: shared hit=273795
8. 5.885 5.885 ↑ 1.3 2,337 1

CTE Scan on users_with_transaction uwt (cost=0.00..58.96 rows=2,948 width=8) (actual time=4.437..5.885 rows=2,337 loops=1)

  • Buffers: shared hit=267
9. 250.059 250.059 ↓ 1.9 82 2,337

Index Scan using transactions_user_id_idx on transactions tr (cost=0.43..16.76 rows=43 width=16) (actual time=0.006..0.107 rows=82 loops=2,337)

  • Index Cond: (user_id = uwt.user_id)
  • Filter: (((status)::text = 'approved'::text) AND ((((transaction_type)::text = 'income'::text) AND ((operation_type)::text <> 'bonus'::text)) OR (((transaction_type)::text = 'expense'::text) AND ((method)::text <> 'wallet'::text))))
  • Rows Removed by Filter: 56
  • Buffers: shared hit=273528
10.          

CTE actives

11. 308.593 308.593 ↓ 1.4 112 1

CTE Scan on first_active_transaction fat (cost=0.00..4,237.10 rows=78 width=16) (actual time=303.934..308.593 rows=112 loops=1)

  • Filter: (immutable_to_char(created_at, 'DD.MM.YYYY'::text) = '16.09.2019'::text)
  • Rows Removed by Filter: 2225
  • Buffers: shared hit=273795
12. 308.608 308.608 ↓ 1.4 112 1

CTE Scan on actives (cost=0.00..1.56 rows=78 width=0) (actual time=303.935..308.608 rows=112 loops=1)

  • Buffers: shared hit=273795
Planning time : 0.295 ms
Execution time : 308.989 ms