explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1XAY : Optimization for: plan #9yLU

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.009 109.736 ↑ 1.0 10 1

Limit (cost=0.88..1,057.14 rows=10 width=83) (actual time=109.723..109.736 rows=10 loops=1)

2. 1.387 109.727 ↑ 3,432.5 10 1

WindowAgg (cost=0.88..3,625,623.94 rows=34,325 width=83) (actual time=109.721..109.727 rows=10 loops=1)

3. 1.576 108.340 ↑ 25.0 1,373 1

Nested Loop Left Join (cost=0.88..3,625,194.88 rows=34,325 width=75) (actual time=0.212..108.340 rows=1,373 loops=1)

4. 0.668 87.542 ↑ 25.0 1,373 1

Nested Loop Left Join (cost=0.58..1,849,587.34 rows=34,325 width=59) (actual time=0.173..87.542 rows=1,373 loops=1)

5. 1.696 63.533 ↑ 25.0 1,373 1

Nested Loop Left Join (cost=0.29..73,979.80 rows=34,325 width=43) (actual time=0.129..63.533 rows=1,373 loops=1)

6. 5.544 5.544 ↑ 1.0 1,373 1

Seq Scan on users (cost=0.00..1,060.76 rows=1,373 width=27) (actual time=0.022..5.544 rows=1,373 loops=1)

  • Filter: ((tenant_id)::text = 'skirtsports'::text)
  • Rows Removed by Filter: 20448
7. 15.103 56.293 ↑ 25.0 1 1,373

GroupAggregate (cost=0.29..52.61 rows=25 width=20) (actual time=0.041..0.041 rows=1 loops=1,373)

  • Group Key: t.user_id
8. 41.190 41.190 ↑ 1.1 23 1,373

Index Scan using transactions_user_id on transactions t (cost=0.29..51.67 rows=25 width=18) (actual time=0.004..0.030 rows=23 loops=1,373)

  • Index Cond: (user_id = users.id)
  • Filter: (value_type = 'points'::transaction_value_type)
  • Rows Removed by Filter: 0
9. 1.373 23.341 ↓ 0.0 0 1,373

GroupAggregate (cost=0.29..51.71 rows=1 width=20) (actual time=0.017..0.017 rows=0 loops=1,373)

  • Group Key: t_1.user_id
10. 21.968 21.968 ↓ 0.0 0 1,373

Index Scan using transactions_user_id on transactions t_1 (cost=0.29..51.67 rows=1 width=18) (actual time=0.016..0.016 rows=0 loops=1,373)

  • Index Cond: (user_id = users.id)
  • Filter: (currency = 'EUR'::text)
  • Rows Removed by Filter: 23
11. 0.000 19.222 ↓ 0.0 0 1,373

GroupAggregate (cost=0.29..51.71 rows=1 width=20) (actual time=0.014..0.014 rows=0 loops=1,373)

  • Group Key: t_2.user_id
12. 19.222 19.222 ↓ 0.0 0 1,373

Index Scan using transactions_user_id on transactions t_2 (cost=0.29..51.67 rows=1 width=18) (actual time=0.014..0.014 rows=0 loops=1,373)

  • Index Cond: (user_id = users.id)
  • Filter: (currency = 'USD'::text)
  • Rows Removed by Filter: 23