explain.depesz.com

PostgreSQL's explain analyze made readable

Result: MG9c

Settings
# exclusive inclusive rows x rows loops node
1. 0.962 1,864.009 ↑ 1.0 41 1

Limit (cost=63,226.67..63,226.78 rows=41 width=7,032) (actual time=1,863.046..1,864.009 rows=41 loops=1)

2. 0.295 1,863.047 ↑ 1,595.9 41 1

Sort (cost=63,226.67..63,390.25 rows=65,431 width=7,032) (actual time=1,863.044..1,863.047 rows=41 loops=1)

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

Hash Left Join (cost=7,287.33..61,146.77 rows=65,431 width=7,032) (actual time=70.874..1,862.752 rows=206 loops=1)

  • Hash Cond: (ta.user_id = tu.id)
  • Filter: ((t.by_id = '39551'::bigint) OR (t.received_by_id = '39551'::bigint) OR ((fa.user_id IS NOT NULL) AND (fa.account_type_id = '4'::bigint) AND ((t.id IS NULL) OR (t.authorization_status IS NULL) OR ((t.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 ((t.id IS NULL) OR (t.authorization_status IS NULL) OR ((t.authorization_status)::text = 'AUTHORIZED'::text)) AND (alternatives: SubPlan 3 or hashed SubPlan 4)))
  • Rows Removed by Filter: 959,908
4. 557.255 1,277.721 ↓ 10.7 960,114 1

Hash Left Join (cost=5,412.13..58,960.72 rows=89,436 width=7,072) (actual time=50.658..1,277.721 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. 498.863 711.301 ↓ 10.7 960,114 1

Hash Join (cost=3,992.90..57,306.68 rows=89,436 width=7,056) (actual time=41.428..711.301 rows=960,114 loops=1)

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

Gather (cost=3,991.87..56,822.14 rows=89,436 width=7,064) (actual time=41.388..212.420 rows=960,114 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
7. 182.172 933.828 ↓ 8.6 320,038 3 / 3

Parallel Hash Left Join (cost=2,991.87..46,878.54 rows=37,265 width=7,064) (actual time=32.158..933.828 rows=320,038 loops=3)

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

Hash Join (cost=1,421.45..45,210.29 rows=37,265 width=7,056) (actual time=20.085..740.009 rows=320,038 loops=3)

  • Hash Cond: (tt.from_account_type_id = ttfat.id)
9. 220.085 597.997 ↓ 8.6 320,038 3 / 3

Hash Left Join (cost=1,420.37..45,069.93 rows=37,265 width=7,056) (actual time=19.968..597.997 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)))
10. 133.405 366.712 ↓ 8.3 320,038 3 / 3

Hash Join (cost=1.14..43,549.35 rows=38,603 width=7,040) (actual time=8.604..366.712 rows=320,038 loops=3)

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

Parallel Seq Scan on transactions t (cost=0.00..42,105.40 rows=386,035 width=7,032) (actual time=8.569..233.291 rows=320,038 loops=3)

  • Filter: (((subclass)::text = 'PAYMENT'::text) AND ((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[])))
12. 0.004 0.016 ↓ 5.0 5 3 / 3

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

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

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

  • Filter: ((subclass)::text = 'PAYMENT'::text)
  • Rows Removed by Filter: 5
14. 5.383 11.200 ↓ 1.0 32,900 3 / 3

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

  • Buckets: 65,536 Batches: 1 Memory Usage: 2,312kB
15. 5.817 5.817 ↓ 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.006..5.817 rows=32,900 loops=3)

16. 0.008 0.066 ↑ 1.0 4 3 / 3

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

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

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

18. 5.101 11.647 ↑ 1.8 10,967 3 / 3

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

  • Buckets: 65,536 Batches: 1 Memory Usage: 2,112kB
19. 6.546 6.546 ↑ 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.012..6.546 rows=10,967 loops=3)

20. 0.003 0.018 ↑ 1.0 1 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
21. 0.015 0.015 ↑ 1.0 1 1

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

  • Filter: (network_id = '1'::bigint)
22. 3.388 9.165 ↓ 1.0 32,900 1

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

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

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

24. 3.541 10.979 ↓ 1.0 32,900 1

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

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

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

26.          

SubPlan (for Hash Left Join)

27. 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))
28. 0.000 0.000 ↓ 0.0 0

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

29. 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)
30. 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)
31. 0.004 0.009 ↓ 2.5 5 1

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

  • Recheck Cond: (broker_id = '39550'::bigint)
  • Filter: ((end_date IS NULL) OR (end_date > CURRENT_TIMESTAMP))
  • Heap Blocks: exact=1
32. 0.005 0.005 ↑ 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.005..0.005 rows=5 loops=1)

  • Index Cond: (broker_id = '39550'::bigint)
33. 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))
34. 0.000 0.000 ↓ 0.0 0

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

35. 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)
36. 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)
37. 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
38. 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)