explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 71ku

Settings
# exclusive inclusive rows x rows loops node
1. 2.122 735.812 ↓ 160.6 7,867 1

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

  • Buffers: shared hit=180563 read=232478
2. 31.473 733.690 ↓ 160.6 7,867 1

Hash Left Join (cost=10,423.72..72,027.98 rows=49 width=457) (actual time=49.525..733.690 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=180563 read=232478
3. 15.254 544.763 ↓ 5.7 19,366 1

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

  • Hash Cond: (offers.user_account_id = power_badge.user_account_id)
  • Buffers: shared hit=163961 read=216974
4. 0.000 529.124 ↓ 5.7 19,366 1

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

  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=163959 read=216971
5. 10.449 638.956 ↓ 4.5 6,455 3 / 3

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

  • Buffers: shared hit=163959 read=216971
6. 10.985 525.222 ↓ 4.5 6,455 3 / 3

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

  • Buffers: shared hit=123928 read=179445
7. 10.184 398.041 ↓ 4.5 6,455 3 / 3

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

  • Buffers: shared hit=86344 read=139363
8. 7.424 381.402 ↓ 4.5 6,455 3 / 3

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

  • Hash Cond: (offers.fiat_currency_id = fiat_currencies.id)
  • Buffers: shared hit=84582 read=137540
9. 6.894 373.797 ↓ 4.5 6,455 3 / 3

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

  • Buffers: shared hit=84487 read=137523
10. 6.497 251.411 ↓ 4.5 6,794 3 / 3

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

  • Buffers: shared hit=44417 read=95988
11. 9.758 129.422 ↓ 4.5 6,794 3 / 3

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

  • Hash Cond: (offers.payment_method_id = payment_methods.id)
  • Buffers: shared hit=2247 read=56537
12. 76.634 76.634 ↑ 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.091..76.634 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=1802 read=28955
13. 0.473 43.030 ↑ 1.0 748 3 / 3

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

  • Buckets: 1024 Batches: 1 Memory Usage: 75kB
  • Buffers: shared hit=419 read=27580
14. 42.557 42.557 ↑ 1.0 748 3 / 3

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

  • Filter: approved
  • Rows Removed by Filter: 2725
  • Buffers: shared hit=419 read=27580
15. 115.492 115.492 ↑ 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.016..0.017 rows=1 loops=20,381)

  • Index Cond: (user_account_id = offers.user_account_id)
  • Buffers: shared hit=42170 read=39451
16. 115.492 115.492 ↑ 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.017..0.017 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=40070 read=41535
17. 0.064 0.181 ↑ 1.0 191 3 / 3

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

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

Seq Scan on fiat_currencies (cost=0.00..9.91 rows=191 width=8) (actual time=0.018..0.117 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=1762 read=1823
20. 116.196 116.196 ↑ 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.018..0.018 rows=1 loops=19,366)

  • Index Cond: (user_account_id = user_stats.user_account_id)
  • Buffers: shared hit=37584 read=40082
21. 103.285 103.285 ↑ 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.016..0.016 rows=1 loops=19,366)

  • Index Cond: (user_account_id = user_stats.user_account_id)
  • Buffers: shared hit=40031 read=37526
22. 0.185 0.385 ↓ 1.0 530 1

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

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

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

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

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

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

Seq Scan on user_badges institutional_badge (cost=0.00..12.44 rows=34 width=16) (actual time=0.007..0.100 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 157.340 ↑ 1.0 1 7,867

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

  • Buffers: shared hit=16597 read=15504
28. 133.739 133.739 ↑ 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.017 rows=2 loops=7,867)

  • Index Cond: (offer_id = offers.id)
  • Heap Fetches: 13253
  • Buffers: shared hit=16597 read=15504
Planning time : 67.638 ms
Execution time : 739.583 ms