explain.depesz.com

PostgreSQL's explain analyze made readable

Result: RM8s

Settings
# exclusive inclusive rows x rows loops node
1. 2.055 701.060 ↓ 160.6 7,867 1

Limit (cost=10,423.72..72,027.98 rows=49 width=457) (actual time=38.682..701.060 rows=7,867 loops=1)

  • Buffers: shared hit=187424 read=225618
2. 28.934 699.005 ↓ 160.6 7,867 1

Hash Left Join (cost=10,423.72..72,027.98 rows=49 width=457) (actual time=38.680..699.005 rows=7,867 loops=1)

  • Hash Cond: (offers.user_account_id = institutional_badge.user_account_id)
  • Filter: (((offers.offer_type = 2) AND ((user_accounts.marketplace_can_post AND (user_crypto_balances.balance >= 2000000)) OR user_accounts.marketplace_can_post OR user_settings.is_verified OR (user_settings.id_verification_status = ANY ('{1,6}'::integer[]))) AND (((payment_methods.payment_method_group_id = 1) AND (user_crypto_balances.bond >= 10000000)) OR user_settings.is_verified OR user_accounts.marketplace_can_post OR (institutional_badge.user_badge_type_id IS NOT NULL) OR (payment_methods.payment_method_group_id <> 1))) OR ((offers.offer_type = 1) AND ((user_crypto_balances.balance >= 2000000) OR (power_badge.user_badge_type_id IS NOT NULL)) AND (user_settings.is_verified OR (user_settings.id_verification_status = ANY ('{1,6}'::integer[])))))
  • Rows Removed by Filter: 11499
  • Buffers: shared hit=187424 read=225618
3. 15.623 520.521 ↓ 5.7 19,366 1

Hash Left Join (cost=10,410.85..71,431.65 rows=3,421 width=398) (actual time=38.568..520.521 rows=19,366 loops=1)

  • Hash Cond: (offers.user_account_id = power_badge.user_account_id)
  • Buffers: shared hit=170818 read=210118
4. 0.000 504.639 ↓ 5.7 19,366 1

Gather (cost=10,391.82..71,395.01 rows=3,421 width=390) (actual time=38.296..504.639 rows=19,366 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=170816 read=210115
5. 10.037 610.740 ↓ 4.5 6,455 3 / 3

Nested Loop (cost=9,391.82..70,052.91 rows=1,425 width=390) (actual time=48.365..610.740 rows=6,455 loops=3)

  • Buffers: shared hit=170816 read=210115
6. 8.864 503.873 ↓ 4.5 6,455 3 / 3

Nested Loop (cost=9,391.39..67,227.05 rows=1,424 width=402) (actual time=48.318..503.873 rows=6,455 loops=3)

  • Buffers: shared hit=130750 read=172624
7. 7.945 385.268 ↓ 4.5 6,455 3 / 3

Nested Loop Left Join (cost=9,390.95..64,331.64 rows=1,423 width=391) (actual time=48.274..385.268 rows=6,455 loops=3)

  • Buffers: shared hit=93113 read=132595
8. 6.923 370.868 ↓ 4.5 6,455 3 / 3

Hash Join (cost=9,390.66..61,573.56 rows=1,423 width=357) (actual time=48.266..370.868 rows=6,455 loops=3)

  • Hash Cond: (offers.fiat_currency_id = fiat_currencies.id)
  • Buffers: shared hit=91370 read=130753
9. 8.958 363.808 ↓ 4.5 6,455 3 / 3

Nested Loop (cost=9,378.36..61,557.45 rows=1,423 width=353) (actual time=47.973..363.808 rows=6,455 loops=3)

  • Buffers: shared hit=91264 read=130747
10. 6.198 246.151 ↓ 4.5 6,794 3 / 3

Nested Loop (cost=9,377.93..58,342.58 rows=1,512 width=331) (actual time=47.931..246.151 rows=6,794 loops=3)

  • Buffers: shared hit=51138 read=89268
11. 9.642 131.254 ↓ 4.5 6,794 3 / 3

Hash Join (cost=9,377.50..49,703.66 rows=1,512 width=315) (actual time=47.876..131.254 rows=6,794 loops=3)

  • Hash Cond: (offers.payment_method_id = payment_methods.id)
  • Buffers: shared hit=8942 read=49843
12. 73.817 73.817 ↑ 1.0 6,801 3 / 3

Parallel Index Scan using offers_active_index on offers (cost=0.42..40,308.14 rows=7,018 width=203) (actual time=0.042..73.817 rows=6,801 loops=3)

  • Index Cond: (active = true)
  • Filter: (active AND authorized AND (deleted_at IS NULL) AND ((offer_type = 2) OR (offer_type = 1)))
  • Rows Removed by Filter: 13507
  • Buffers: shared hit=1806 read=28952
13. 0.413 47.795 ↑ 1.0 748 3 / 3

Hash (cost=9,367.73..9,367.73 rows=748 width=120) (actual time=47.795..47.795 rows=748 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 75kB
  • Buffers: shared hit=7108 read=20891
14. 47.382 47.382 ↑ 1.0 748 3 / 3

Seq Scan on payment_methods (cost=0.00..9,367.73 rows=748 width=120) (actual time=4.122..47.382 rows=748 loops=3)

  • Filter: approved
  • Rows Removed by Filter: 2725
  • Buffers: shared hit=7108 read=20891
15. 108.699 108.699 ↑ 1.0 1 20,381 / 3

Index Scan using user_stats_user_account_id_index on user_stats (cost=0.43..5.70 rows=1 width=16) (actual time=0.015..0.016 rows=1 loops=20,381)

  • Index Cond: (user_account_id = offers.user_account_id)
  • Buffers: shared hit=42196 read=39425
16. 108.699 108.699 ↑ 1.0 1 20,381 / 3

Index Scan using user_accounts_pkey on user_accounts (cost=0.43..2.13 rows=1 width=22) (actual time=0.016..0.016 rows=1 loops=20,381)

  • Index Cond: (id = user_stats.user_account_id)
  • Filter: ((NOT suspend) AND (NOT banned) AND (NOT frozen))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=40126 read=41479
17. 0.047 0.137 ↑ 1.0 191 3 / 3

Hash (cost=9.91..9.91 rows=191 width=8) (actual time=0.137..0.137 rows=191 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
  • Buffers: shared hit=18 read=6
18. 0.090 0.090 ↑ 1.0 191 3 / 3

Seq Scan on fiat_currencies (cost=0.00..9.91 rows=191 width=8) (actual time=0.018..0.090 rows=191 loops=3)

  • Buffers: shared hit=18 read=6
19. 6.455 6.455 ↓ 0.0 0 19,366 / 3

Index Scan using locations_pk on locations (cost=0.29..1.94 rows=1 width=38) (actual time=0.001..0.001 rows=0 loops=19,366)

  • Index Cond: (id = offers.location_id)
  • Buffers: shared hit=1743 read=1842
20. 109.741 109.741 ↑ 1.0 1 19,366 / 3

Index Scan using user_settings_user_account_id_is_verified_index on user_settings (cost=0.43..2.02 rows=1 width=11) (actual time=0.017..0.017 rows=1 loops=19,366)

  • Index Cond: (user_account_id = user_stats.user_account_id)
  • Buffers: shared hit=37637 read=40029
21. 96.830 96.830 ↑ 1.0 1 19,366 / 3

Index Scan using user_crypto_balances_user_account_id_index on user_crypto_balances (cost=0.43..1.97 rows=1 width=20) (actual time=0.015..0.015 rows=1 loops=19,366)

  • Index Cond: (user_account_id = user_stats.user_account_id)
  • Buffers: shared hit=40066 read=37491
22. 0.105 0.259 ↓ 1.0 530 1

Hash (cost=12.44..12.44 rows=528 width=16) (actual time=0.258..0.259 rows=530 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 35kB
  • Buffers: shared hit=2 read=3
23. 0.154 0.154 ↓ 1.0 530 1

Seq Scan on user_badges power_badge (cost=0.00..12.44 rows=528 width=16) (actual time=0.008..0.154 rows=530 loops=1)

  • Filter: (user_badge_type_id = 1)
  • Rows Removed by Filter: 67
  • Buffers: shared hit=2 read=3
24. 0.008 0.077 ↑ 1.0 34 1

Hash (cost=12.44..12.44 rows=34 width=16) (actual time=0.076..0.077 rows=34 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
  • Buffers: shared hit=5
25. 0.069 0.069 ↑ 1.0 34 1

Seq Scan on user_badges institutional_badge (cost=0.00..12.44 rows=34 width=16) (actual time=0.004..0.069 rows=34 loops=1)

  • Filter: (user_badge_type_id = 2)
  • Rows Removed by Filter: 563
  • Buffers: shared hit=5
26.          

SubPlan (for Hash Left Join)

27. 23.601 149.473 ↑ 1.0 1 7,867

Aggregate (cost=11.54..11.55 rows=1 width=32) (actual time=0.019..0.019 rows=1 loops=7,867)

  • Buffers: shared hit=16601 read=15500
28. 125.872 125.872 ↑ 1.0 2 7,867

Index Only Scan using offer_tags_pkey on offer_tags (cost=0.42..11.52 rows=2 width=4) (actual time=0.015..0.016 rows=2 loops=7,867)

  • Index Cond: (offer_id = offers.id)
  • Heap Fetches: 13253
  • Buffers: shared hit=16601 read=15500
Planning time : 57.146 ms
Execution time : 705.114 ms