explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Fmlv

Settings
# exclusive inclusive rows x rows loops node
1. 0.008 14,352.489 ↑ 1.0 41 1

Limit (cost=83,581.77..83,581.87 rows=41 width=7,010) (actual time=14,352.480..14,352.489 rows=41 loops=1)

2. 0.344 14,352.481 ↑ 11,293.3 41 1

Sort (cost=83,581.77..84,739.34 rows=463,027 width=7,010) (actual time=14,352.478..14,352.481 rows=41 loops=1)

  • Sort Key: t1.date DESC, t1.id
  • Sort Method: top-N heapsort Memory: 36kB
3. 1,220.771 14,352.137 ↑ 2,236.8 207 1

Hash Left Join (cost=2,841.83..68,863.18 rows=463,027 width=7,010) (actual time=4,096.734..14,352.137 rows=207 loops=1)

  • Hash Cond: (t1.to_id = t8.id)
  • Filter: ((((t1.subclass)::text <> 'SCHEDULED_PAYMENT'::text) OR (t8.user_id <> '39550'::bigint) OR t1.show_to_receiver) AND ((t1.to_id = '39551'::bigint) OR (SubPlan 1)))
  • Rows Removed by Filter: 926,458
4. 590.053 2,003.605 ↓ 1.0 926,665 1

Hash Left Join (cost=1,422.61..64,139.59 rows=926,024 width=7,018) (actual time=24.011..2,003.605 rows=926,665 loops=1)

  • Hash Cond: (t1.from_id = t2.id)
  • Filter: (((t2.user_id = '39550'::bigint) OR ((t1.authorization_type)::text <> 'OPERATOR'::text)) AND (((t1.subclass)::text <> 'TICKET'::text) OR (t2.user_id IS NULL) OR (t2.user_id <> '39550'::bigint)))
5. 409.410 1,403.596 ↓ 1.0 926,665 1

Hash Join (cost=3.38..60,288.96 rows=926,024 width=7,010) (actual time=14.035..1,403.596 rows=926,665 loops=1)

  • Hash Cond: (t0.from_account_type_id = t4.id)
6. 392.669 994.176 ↓ 1.0 926,665 1

Hash Join (cost=1.23..53,110.11 rows=926,024 width=7,018) (actual time=14.022..994.176 rows=926,665 loops=1)

  • Hash Cond: (t1.type_id = t0.id)
7. 601.497 601.497 ↓ 1.0 926,665 1

Seq Scan on transactions t1 (cost=0.00..49,647.88 rows=926,024 width=7,010) (actual time=14.003..601.497 rows=926,665 loops=1)

  • Filter: (((authorization_status)::text = ANY ('{NA,AUTHORIZED}'::text[])) AND ((subclass)::text = ANY ('{PAYMENT,ORDER_PAYMENT,SCHEDULED_PAYMENT,RECURRING_PAYMENT,IMPORT,CHARGEBACK,PAYMENT_REQUEST}'::text[])))
  • Rows Removed by Filter: 33,941
8. 0.003 0.010 ↑ 1.0 10 1

Hash (cost=1.10..1.10 rows=10 width=16) (actual time=0.009..0.010 rows=10 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
9. 0.007 0.007 ↑ 1.0 10 1

Seq Scan on transfer_types t0 (cost=0.00..1.10 rows=10 width=16) (actual time=0.005..0.007 rows=10 loops=1)

10. 0.001 0.010 ↑ 1.0 4 1

Hash (cost=2.10..2.10 rows=4 width=8) (actual time=0.010..0.010 rows=4 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
11. 0.003 0.009 ↑ 1.0 4 1

Nested Loop (cost=0.00..2.10 rows=4 width=8) (actual time=0.008..0.009 rows=4 loops=1)

  • Join Filter: (t4.currency_id = t5.id)
12. 0.004 0.004 ↑ 1.0 1 1

Seq Scan on currencies t5 (cost=0.00..1.01 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=1)

  • Filter: (network_id = '1'::bigint)
13. 0.002 0.002 ↑ 1.0 4 1

Seq Scan on account_types t4 (cost=0.00..1.04 rows=4 width=16) (actual time=0.002..0.002 rows=4 loops=1)

14. 4.060 9.956 ↓ 1.0 32,900 1

Hash (cost=1,007.99..1,007.99 rows=32,899 width=24) (actual time=9.956..9.956 rows=32,900 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 2,312kB
15. 5.896 5.896 ↓ 1.0 32,900 1

Seq Scan on accounts t2 (cost=0.00..1,007.99 rows=32,899 width=24) (actual time=0.003..5.896 rows=32,900 loops=1)

16. 2.992 7.781 ↓ 1.0 32,900 1

Hash (cost=1,007.99..1,007.99 rows=32,899 width=24) (actual time=7.780..7.781 rows=32,900 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 2,312kB
17. 4.789 4.789 ↓ 1.0 32,900 1

Seq Scan on accounts t8 (cost=0.00..1,007.99 rows=32,899 width=24) (actual time=0.011..4.789 rows=32,900 loops=1)

18.          

SubPlan (for Hash Left Join)

19. 0.394 11,119.980 ↓ 0.0 0 926,665

Nested Loop (cost=4.78..31.18 rows=1 width=0) (actual time=0.012..0.012 rows=0 loops=926,665)

20. 0.394 6,486.655 ↓ 2.5 5 926,665

Nested Loop (cost=4.49..30.32 rows=2 width=16) (actual time=0.002..0.007 rows=5 loops=926,665)

21. 1,853.330 1,853.330 ↓ 2.5 5 926,665

Bitmap Heap Scan on brokerings t13 (cost=4.20..13.70 rows=2 width=8) (actual time=0.001..0.002 rows=5 loops=926,665)

  • Recheck Cond: (broker_id = '39550'::bigint)
  • Filter: ((end_date IS NULL) OR (end_date > CURRENT_TIMESTAMP))
  • Heap Blocks: exact=926,665
22. 0.000 0.000 ↑ 1.2 5 926,665

Bitmap Index Scan on ix_brokerings_fk_brokerings_broker_id (cost=0.00..4.20 rows=6 width=0) (actual time=0.000..0.000 rows=5 loops=926,665)

  • Index Cond: (broker_id = '39550'::bigint)
23. 4,632.931 4,632.931 ↑ 1.0 1 4,632,931

Index Scan using users_pkey on users t14 (cost=0.29..8.31 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=4,632,931)

  • Index Cond: (id = t13.user_id)
  • Filter: ((subclass)::text = 'USER'::text)
24. 4,632.931 4,632.931 ↓ 0.0 0 4,632,931

Index Scan using ix_accounts_fk_accounts_user_id on accounts t12 (cost=0.29..0.42 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=4,632,931)

  • Index Cond: (user_id = t14.id)
  • Filter: ((account_type_id = '4'::bigint) AND ((t2.id = id) OR (t8.id = id)))
  • Rows Removed by Filter: 1
Planning time : 15.478 ms
Execution time : 14,352.609 ms