explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ocpS

Settings
# exclusive inclusive rows x rows loops node
1. 17.760 718.610 ↓ 2.4 900 1

GroupAggregate (cost=12,239.46..12,261.03 rows=375 width=368) (actual time=698.453..718.610 rows=900 loops=1)

  • Group Key: balance_updates.created, service_updates.external_service_id, balance_updates.cashless, balance_updates.currency, balance_updates.is_bonus, balance_updates.dir, balance_updates.user_id, balance_updates.state, transaction_typ
2. 51.963 700.850 ↓ 54.7 20,511 1

Sort (cost=12,239.46..12,240.40 rows=375 width=284) (actual time=698.392..700.850 rows=20,511 loops=1)

  • Sort Key: balance_updates.created, service_updates.external_service_id, balance_updates.cashless, balance_updates.currency, balance_updates.is_bonus, balance_updates.dir, balance_updates.user_id, balance_updates.state, transactio
  • Sort Method: quicksort Memory: 10,419kB
3. 7.439 648.887 ↓ 54.7 20,511 1

Hash Left Join (cost=1,452.78..12,223.43 rows=375 width=284) (actual time=139.742..648.887 rows=20,511 loops=1)

  • Hash Cond: (balance_updates.transaction_type_id = transaction_types.id)
4. 21.968 641.313 ↓ 54.7 20,511 1

Nested Loop (cost=1,425.22..12,190.72 rows=375 width=190) (actual time=139.596..641.313 rows=20,511 loops=1)

5. 8.562 578.805 ↓ 48.1 20,270 1

Nested Loop (cost=1,424.79..11,270.37 rows=421 width=150) (actual time=139.573..578.805 rows=20,270 loops=1)

6. 0.030 0.030 ↑ 1.0 7 1

Index Scan using accounting_mode_idx on services (cost=0.28..3.01 rows=7 width=113) (actual time=0.013..0.030 rows=7 loops=1)

  • Index Cond: (accounting_mode = 'nbd'::text)
7. 9.198 570.213 ↓ 17.6 2,896 7

Bitmap Heap Scan on service_updates (cost=1,424.52..1,607.97 rows=165 width=49) (actual time=80.265..81.459 rows=2,896 loops=7)

  • Recheck Cond: ((service_id = services.id) AND (user_id = 61,938))
  • Heap Blocks: exact=2,898
8. 43.800 561.015 ↓ 0.0 0 7

BitmapAnd (cost=1,424.52..1,424.52 rows=165 width=0) (actual time=80.145..80.145 rows=0 loops=7)

9. 491.400 491.400 ↓ 29.1 544,191 7

Bitmap Index Scan on service_updates_service_id_index (cost=0.00..646.61 rows=18,716 width=0) (actual time=70.200..70.200 rows=544,191 loops=7)

  • Index Cond: (service_id = services.id)
10. 25.815 25.815 ↑ 1.0 41,557 5

Bitmap Index Scan on user_id_index (cost=0.00..767.20 rows=41,662 width=0) (actual time=5.163..5.163 rows=41,557 loops=5)

  • Index Cond: (user_id = 61,938)
11. 40.540 40.540 ↑ 1.0 1 20,270

Index Scan using service_idx on balance_updates (cost=0.43..2.18 rows=1 width=52) (actual time=0.002..0.002 rows=1 loops=20,270)

  • Index Cond: (service_id = service_updates.id)
  • Filter: ((created >= '2016-12-19 13:40:50'::timestamp without time zone) AND (created < '2020-10-13 15:16:30'::timestamp without time zone))
  • Rows Removed by Filter: 0
12. 0.013 0.135 ↑ 1.0 59 1

Hash (cost=26.79..26.79 rows=61 width=102) (actual time=0.135..0.135 rows=59 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 15kB
13. 0.026 0.122 ↑ 1.0 59 1

Nested Loop Left Join (cost=0.15..26.79 rows=61 width=102) (actual time=0.020..0.122 rows=59 loops=1)

14. 0.037 0.037 ↑ 1.0 59 1

Seq Scan on transaction_types (cost=0.00..2.61 rows=61 width=74) (actual time=0.013..0.037 rows=59 loops=1)

15. 0.059 0.059 ↑ 1.0 1 59

Index Scan using transaction_groups_pkey on transaction_groups (cost=0.15..0.39 rows=1 width=36) (actual time=0.001..0.001 rows=1 loops=59)

  • Index Cond: (transaction_types.transaction_group_id = id)
Planning time : 1.499 ms
Execution time : 719.555 ms