explain.depesz.com

PostgreSQL's explain analyze made readable

Result: qhKV

Settings
# exclusive inclusive rows x rows loops node
1. 1.444 2,290.881 ↑ 1.0 41 1

Limit (cost=85,492.70..85,492.80 rows=41 width=7,032) (actual time=2,289.438..2,290.881 rows=41 loops=1)

2. 230.016 2,289.437 ↑ 2,160.3 41 1

Sort (cost=85,492.70..85,714.13 rows=88,573 width=7,032) (actual time=2,289.436..2,289.437 rows=41 loops=1)

  • Sort Key: t.date DESC, t.id
  • Sort Method: top-N heapsort Memory: 35kB
3. 518.162 2,059.421 ↓ 10.5 926,173 1

Hash Left Join (cost=7,287.76..82,677.16 rows=88,573 width=7,032) (actual time=55.676..2,059.421 rows=926,173 loops=1)

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

Hash Left Join (cost=5,412.55..80,491.11 rows=89,436 width=7,110) (actual time=45.568..1,531.329 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. 507.207 1,003.131 ↓ 10.7 960,114 1

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

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

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

  • Workers Planned: 2
  • Workers Launched: 2
7. 178.402 1,494.177 ↓ 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=27.297..1,494.177 rows=320,038 loops=3)

  • Hash Cond: (fa.user_id = fu.id)
8. 262.078 1,306.925 ↓ 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=18.074..1,306.925 rows=320,038 loops=3)

9. 143.747 724.809 ↓ 8.6 320,038 3 / 3

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

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

Hash Left Join (cost=1,420.37..44,070.18 rows=37,265 width=7,056) (actual time=18.015..581.039 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. 132.525 358.963 ↓ 8.3 320,038 3 / 3

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

  • Hash Cond: (t.type_id = tt.id)
12. 226.421 226.421 ↑ 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=7.332..226.421 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.017 ↓ 5.0 5 3 / 3

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
14. 0.013 0.013 ↓ 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.013 rows=5 loops=3)

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

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

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

17. 0.006 0.023 ↑ 1.0 4 3 / 3

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

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

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

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

Index Scan using transactions_pkey on transactions t2 (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.821 8.850 ↑ 1.8 10,967 3 / 3

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

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

22. 0.006 0.039 ↑ 1.0 1 1

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

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

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

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

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

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

26. 3.436 9.930 ↓ 1.0 32,900 1

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

  • Buckets: 65,536 Batches: 1 Memory Usage: 2,055kB
27. 6.494 6.494 ↓ 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..6.494 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.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on brokerings t3_1 (cost=4.20..13.70 rows=2 width=8) (never executed)

  • Recheck Cond: (broker_id = '39550'::bigint)
  • Filter: ((end_date IS NULL) OR (end_date > CURRENT_TIMESTAMP))
34. 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)
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.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on brokerings t4_1 (cost=4.20..13.70 rows=2 width=8) (never executed)

  • Recheck Cond: (broker_id = '39550'::bigint)
  • Filter: ((end_date IS NULL) OR (end_date > CURRENT_TIMESTAMP))
40. 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)
Planning time : 23.294 ms
Execution time : 2,291.342 ms