explain.depesz.com

PostgreSQL's explain analyze made readable

Result: bF9G

Settings
# exclusive inclusive rows x rows loops node
1. 1.398 2,214.407 ↑ 1.0 41 1

Limit (cost=84,757.06..84,757.16 rows=41 width=7,032) (actual time=2,213.011..2,214.407 rows=41 loops=1)

2. 0.259 2,213.009 ↑ 1,595.9 41 1

Sort (cost=84,757.06..84,920.63 rows=65,431 width=7,032) (actual time=2,213.006..2,213.009 rows=41 loops=1)

  • Sort Key: t.date DESC, t.id
  • Sort Method: top-N heapsort Memory: 36kB
3. 546.254 2,212.750 ↑ 317.6 206 1

Hash Left Join (cost=7,287.76..82,677.15 rows=65,431 width=7,032) (actual time=66.453..2,212.750 rows=206 loops=1)

  • Hash Cond: (ta.user_id = tu.id)
  • Filter: ((t.by_id = '39551'::bigint) OR (bp.received_by_id = '39551'::bigint) OR ((fa.user_id IS NOT NULL) AND (fa.account_type_id = '4'::bigint) AND ((bp.id IS NULL) OR (bp.authorization_status IS NULL) OR ((bp.authorization_status)::text = 'AUTHORIZED'::text)) AND (alternatives: SubPlan 1 or hashed SubPlan 2)) OR ((ta.user_id IS NOT NULL) AND (ta.account_type_id = '4'::bigint) AND ((bp.id IS NULL) OR (bp.authorization_status IS NULL) OR ((bp.authorization_status)::text = 'AUTHORIZED'::text)) AND (alternatives: SubPlan 3 or hashed SubPlan 4)))
  • Rows Removed by Filter: 959,908
4. 540.950 1,656.380 ↓ 10.7 960,114 1

Hash Left Join (cost=5,412.55..80,491.11 rows=89,436 width=7,110) (actual time=46.494..1,656.380 rows=960,114 loops=1)

  • Hash Cond: (t.to_id = ta.id)
  • Filter: (((t.subclass)::text <> 'SCHEDULED_PAYMENT'::text) OR (ta.user_id <> '39550'::bigint) OR t.show_to_receiver)
5. 503.255 1,107.114 ↓ 10.7 960,114 1

Hash Join (cost=3,993.32..78,837.06 rows=89,436 width=7,094) (actual time=37.961..1,107.114 rows=960,114 loops=1)

  • Hash Cond: (ttfat.currency_id = c.id)
6. 0.000 603.829 ↓ 10.7 960,114 1

Gather (cost=3,992.30..78,352.52 rows=89,436 width=7,102) (actual time=37.923..603.829 rows=960,114 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
7. 175.222 1,483.295 ↓ 8.6 320,038 3 / 3

Parallel Hash Left Join (cost=2,992.30..68,408.92 rows=37,265 width=7,102) (actual time=26.897..1,483.295 rows=320,038 loops=3)

  • Hash Cond: (fa.user_id = fu.id)
8. 258.670 1,299.711 ↓ 8.6 320,038 3 / 3

Nested Loop Left Join (cost=1,421.88..66,740.67 rows=37,265 width=7,094) (actual time=17.900..1,299.711 rows=320,038 loops=3)

9. 142.584 721.003 ↓ 8.6 320,038 3 / 3

Hash Join (cost=1,421.45..44,210.54 rows=37,265 width=7,056) (actual time=17.883..721.003 rows=320,038 loops=3)

  • Hash Cond: (tt.from_account_type_id = ttfat.id)
10. 212.066 578.400 ↓ 8.6 320,038 3 / 3

Hash Left Join (cost=1,420.37..44,070.18 rows=37,265 width=7,056) (actual time=17.851..578.400 rows=320,038 loops=3)

  • Hash Cond: (t.from_id = fa.id)
  • Filter: (((fa.user_id = '39550'::bigint) OR (t.authorization_type IS NULL) OR ((t.authorization_type)::text <> 'OPERATOR'::text)) AND (((t.subclass)::text <> 'TICKET'::text) OR (fa.user_id IS NULL) OR (fa.user_id <> '39550'::bigint)))
11. 134.790 357.028 ↓ 8.3 320,038 3 / 3

Hash Join (cost=1.14..42,549.59 rows=38,603 width=7,040) (actual time=8.367..357.028 rows=320,038 loops=3)

  • Hash Cond: (t.type_id = tt.id)
12. 222.219 222.219 ↑ 1.2 320,038 3 / 3

Parallel Seq Scan on transactions t (cost=0.00..41,105.64 rows=386,035 width=7,032) (actual time=8.333..222.219 rows=320,038 loops=3)

  • Filter: (((authorization_type IS NULL) OR ((authorization_type)::text <> 'OPERATOR'::text) OR (by_id = '39551'::bigint)) AND ((subclass)::text = ANY ('{PAYMENT,ORDER_PAYMENT,SCHEDULED_PAYMENT,RECURRING_PAYMENT,IMPORT,CHARGEBACK,PAYMENT_REQUEST}'::text[])))
13. 0.004 0.019 ↓ 5.0 5 3 / 3

Hash (cost=1.12..1.12 rows=1 width=16) (actual time=0.019..0.019 rows=5 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
14. 0.015 0.015 ↓ 5.0 5 3 / 3

Seq Scan on transfer_types tt (cost=0.00..1.12 rows=1 width=16) (actual time=0.014..0.015 rows=5 loops=3)

  • Filter: ((subclass)::text = 'PAYMENT'::text)
  • Rows Removed by Filter: 5
15. 4.252 9.306 ↓ 1.0 32,900 3 / 3

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

  • Buckets: 65,536 Batches: 1 Memory Usage: 2,312kB
16. 5.054 5.054 ↓ 1.0 32,900 3 / 3

Seq Scan on accounts fa (cost=0.00..1,007.99 rows=32,899 width=24) (actual time=0.007..5.054 rows=32,900 loops=3)

17. 0.005 0.019 ↑ 1.0 4 3 / 3

Hash (cost=1.04..1.04 rows=4 width=16) (actual time=0.019..0.019 rows=4 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
18. 0.014 0.014 ↑ 1.0 4 3 / 3

Seq Scan on account_types ttfat (cost=0.00..1.04 rows=4 width=16) (actual time=0.013..0.014 rows=4 loops=3)

19. 320.038 320.038 ↑ 1.0 1 960,114 / 3

Index Scan using transactions_pkey on transactions bp (cost=0.42..0.60 rows=1 width=38) (actual time=0.001..0.001 rows=1 loops=960,114)

  • Index Cond: (t.id = id)
20. 3.611 8.362 ↑ 1.8 10,967 3 / 3

Parallel Hash (cost=1,328.52..1,328.52 rows=19,352 width=16) (actual time=8.362..8.362 rows=10,967 loops=3)

  • Buckets: 65,536 Batches: 1 Memory Usage: 2,112kB
21. 4.751 4.751 ↑ 1.8 10,967 3 / 3

Parallel Seq Scan on users fu (cost=0.00..1,328.52 rows=19,352 width=16) (actual time=0.027..4.751 rows=10,967 loops=3)

22. 0.005 0.030 ↑ 1.0 1 1

Hash (cost=1.01..1.01 rows=1 width=8) (actual time=0.030..0.030 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
23. 0.025 0.025 ↑ 1.0 1 1

Seq Scan on currencies c (cost=0.00..1.01 rows=1 width=8) (actual time=0.024..0.025 rows=1 loops=1)

  • Filter: (network_id = '1'::bigint)
24. 3.703 8.316 ↓ 1.0 32,900 1

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

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

Seq Scan on accounts ta (cost=0.00..1,007.99 rows=32,899 width=24) (actual time=0.005..4.613 rows=32,900 loops=1)

26. 3.439 10.107 ↓ 1.0 32,900 1

Hash (cost=1,463.98..1,463.98 rows=32,898 width=16) (actual time=10.107..10.107 rows=32,900 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 2,055kB
27. 6.668 6.668 ↓ 1.0 32,900 1

Seq Scan on users tu (cost=0.00..1,463.98 rows=32,898 width=16) (actual time=0.005..6.668 rows=32,900 loops=1)

28.          

SubPlan (for Hash Left Join)

29. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on brokerings t3 (cost=8.65..12.67 rows=1 width=0) (never executed)

  • Recheck Cond: ((user_id = fu.id) AND (broker_id = '39550'::bigint))
  • Filter: ((end_date IS NULL) OR (end_date > CURRENT_TIMESTAMP))
30. 0.000 0.000 ↓ 0.0 0

BitmapAnd (cost=8.65..8.65 rows=1 width=0) (never executed)

31. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on ix_brokerings_fk_brokerings_user_id (cost=0.00..4.20 rows=6 width=0) (never executed)

  • Index Cond: (user_id = fu.id)
32. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on ix_brokerings_fk_brokerings_broker_id (cost=0.00..4.20 rows=6 width=0) (never executed)

  • Index Cond: (broker_id = '39550'::bigint)
33. 0.003 0.007 ↓ 2.5 5 1

Bitmap Heap Scan on brokerings t3_1 (cost=4.20..13.70 rows=2 width=8) (actual time=0.006..0.007 rows=5 loops=1)

  • Recheck Cond: (broker_id = '39550'::bigint)
  • Filter: ((end_date IS NULL) OR (end_date > CURRENT_TIMESTAMP))
  • Heap Blocks: exact=1
34. 0.004 0.004 ↑ 1.2 5 1

Bitmap Index Scan on ix_brokerings_fk_brokerings_broker_id (cost=0.00..4.20 rows=6 width=0) (actual time=0.003..0.004 rows=5 loops=1)

  • Index Cond: (broker_id = '39550'::bigint)
35. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on brokerings t4 (cost=8.65..12.67 rows=1 width=0) (never executed)

  • Recheck Cond: ((user_id = tu.id) AND (broker_id = '39550'::bigint))
  • Filter: ((end_date IS NULL) OR (end_date > CURRENT_TIMESTAMP))
36. 0.000 0.000 ↓ 0.0 0

BitmapAnd (cost=8.65..8.65 rows=1 width=0) (never executed)

37. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on ix_brokerings_fk_brokerings_user_id (cost=0.00..4.20 rows=6 width=0) (never executed)

  • Index Cond: (user_id = tu.id)
38. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on ix_brokerings_fk_brokerings_broker_id (cost=0.00..4.20 rows=6 width=0) (never executed)

  • Index Cond: (broker_id = '39550'::bigint)
39. 0.001 0.002 ↓ 2.5 5 1

Bitmap Heap Scan on brokerings t4_1 (cost=4.20..13.70 rows=2 width=8) (actual time=0.002..0.002 rows=5 loops=1)

  • Recheck Cond: (broker_id = '39550'::bigint)
  • Filter: ((end_date IS NULL) OR (end_date > CURRENT_TIMESTAMP))
  • Heap Blocks: exact=1
40. 0.001 0.001 ↑ 1.2 5 1

Bitmap Index Scan on ix_brokerings_fk_brokerings_broker_id (cost=0.00..4.20 rows=6 width=0) (actual time=0.001..0.001 rows=5 loops=1)

  • Index Cond: (broker_id = '39550'::bigint)
Planning time : 23.952 ms
Execution time : 2,214.935 ms