explain.depesz.com

PostgreSQL's explain analyze made readable

Result: qH29

Settings
# exclusive inclusive rows x rows loops node
1. 1.690 2,389.735 ↑ 1.0 41 1

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

2. 232.741 2,388.045 ↑ 2,160.3 41 1

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

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

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

  • Hash Cond: (t8.user_id = t10.id)
  • Filter: ((t1.by_id = '39551'::bigint) OR (t12.received_by_id = '39551'::bigint) OR (t12.id IS NULL) OR (t12.authorization_status IS NULL) OR ((t12.authorization_status)::text = 'AUTHORIZED'::text) OR ((t2.user_id IS NOT NULL) AND (t2.account_type_id = '4'::bigint) AND ((t12.id IS NULL) OR (t12.authorization_status IS NULL) OR ((t12.authorization_status)::text = 'AUTHORIZED'::text)) AND (alternatives: SubPlan 1 or hashed SubPlan 2)) OR ((t8.user_id IS NOT NULL) AND (t8.account_type_id = '4'::bigint) AND ((t12.id IS NULL) OR (t12.authorization_status IS NULL) OR ((t12.authorization_status)::text = 'AUTHORIZED'::text)) AND (alternatives: SubPlan 3 or hashed SubPlan 4)))
  • Rows Removed by Filter: 33,941
4. 576.938 1,566.152 ↓ 10.7 960,114 1

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

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

  • Hash Cond: (t4.currency_id = t5.id)
6. 0.000 469.224 ↓ 10.7 960,114 1

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

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

  • Hash Cond: (t2.user_id = t6.id)
8. 270.739 1,337.000 ↓ 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.954..1,337.000 rows=320,038 loops=3)

9. 147.507 746.223 ↓ 8.6 320,038 3 / 3

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

  • Hash Cond: (t0.from_account_type_id = t4.id)
10. 221.043 598.687 ↓ 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.888..598.687 rows=320,038 loops=3)

  • Hash Cond: (t1.from_id = t2.id)
  • Filter: (((t2.user_id = '39550'::bigint) OR (t1.authorization_type IS NULL) 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)))
11. 134.334 367.730 ↓ 8.3 320,038 3 / 3

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

  • Hash Cond: (t1.type_id = t0.id)
12. 233.373 233.373 ↑ 1.2 320,038 3 / 3

Parallel Seq Scan on transactions t1 (cost=0.00..41,105.64 rows=386,035 width=7,032) (actual time=7.740..233.373 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.005 0.023 ↓ 5.0 5 3 / 3

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

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

Seq Scan on transfer_types t0 (cost=0.00..1.12 rows=1 width=16) (actual time=0.016..0.018 rows=5 loops=3)

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

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

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

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

17. 0.007 0.029 ↑ 1.0 4 3 / 3

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

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

Seq Scan on account_types t4 (cost=0.00..1.04 rows=4 width=16) (actual time=0.020..0.022 rows=4 loops=3)

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

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

  • Index Cond: (t1.id = id)
20. 4.130 9.251 ↑ 1.8 10,967 3 / 3

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

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

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

22. 0.009 0.062 ↑ 1.0 1 1

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

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

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

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

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

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

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

26. 3.590 10.123 ↓ 1.0 32,900 1

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

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

Seq Scan on users t10 (cost=0.00..1,463.98 rows=32,898 width=16) (actual time=0.007..6.533 rows=32,900 loops=1)

28.          

SubPlan (for Hash Left Join)

29. 0.000 0.000 ↓ 0.0 0

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

  • Recheck Cond: ((user_id = t6.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 = t6.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 t13_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 t14 (cost=8.65..12.67 rows=1 width=0) (never executed)

  • Recheck Cond: ((user_id = t10.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 = t10.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 t14_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.919 ms
Execution time : 2,390.705 ms