explain.depesz.com

PostgreSQL's explain analyze made readable

Result: KlbH

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 109,796.978 ↑ 71.4 7 1

Limit (cost=27,611,073.62..27,611,074.87 rows=500 width=1,694) (actual time=109,796.976..109,796.978 rows=7 loops=1)

2. 0.041 109,796.975 ↑ 225,264.4 7 1

Sort (cost=27,611,073.62..27,615,015.75 rows=1,576,851 width=1,694) (actual time=109,796.975..109,796.975 rows=7 loops=1)

  • Sort Key: twitter_users.followers_count DESC NULLS LAST, twitter_users.verified DESC
  • Sort Method: quicksort Memory: 31kB
3. 0.028 109,796.934 ↑ 225,264.4 7 1

Subquery Scan on twitter_users (cost=27,508,848.07..27,532,500.84 rows=1,576,851 width=1,694) (actual time=109,796.914..109,796.934 rows=7 loops=1)

4. 0.013 109,796.906 ↑ 225,264.4 7 1

Unique (cost=27,508,848.07..27,516,732.33 rows=1,576,851 width=1,680) (actual time=109,796.895..109,796.906 rows=7 loops=1)

5. 0.000 109,796.893 ↑ 175,205.7 9 1

Sort (cost=27,508,848.07..27,512,790.20 rows=1,576,851 width=1,680) (actual time=109,796.891..109,796.893 rows=9 loops=1)

  • Sort Key: twitter_users_1.screen_name
  • Sort Method: quicksort Memory: 33kB
6. 932.650 109,837.804 ↑ 175,205.7 9 1

Gather (cost=1,381.99..26,674,126.28 rows=1,576,851 width=1,680) (actual time=106,475.188..109,837.804 rows=9 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
7. 1.892 108,905.154 ↑ 219,007.0 3 3

Hash Join (cost=381.99..26,515,441.18 rows=657,021 width=1,680) (actual time=61,853.416..108,905.154 rows=3 loops=3)

  • Hash Cond: (persons.organization_id = reports_organizations.organization_id)
8. 108,584.603 108,903.185 ↑ 141,409.1 4,886 3

Nested Loop (cost=335.94..24,701,617.77 rows=690,925,032 width=1,680) (actual time=287.290..108,903.185 rows=4,886 loops=3)

9. 73.344 290.587 ↑ 41.2 3,879 3

Nested Loop (cost=302.41..169,215.12 rows=159,722 width=396) (actual time=194.980..290.587 rows=3,879 loops=3)

10. 144.276 217.227 ↑ 41.2 3,879 3

Hash Join (cost=301.97..13,073.49 rows=159,722 width=51) (actual time=194.925..217.227 rows=3,879 loops=3)

  • Hash Cond: (lower(twitter_followers.primary_screen_name) = (lower(twitter_usernames.name)))
11. 72.694 72.694 ↑ 1.3 271,564 3

Parallel Seq Scan on twitter_followers (cost=0.00..11,805.89 rows=343,489 width=51) (actual time=0.018..72.694 rows=271,564 loops=3)

12. 0.004 0.257 ↑ 46.5 2 3

Hash (cost=300.81..300.81 rows=93 width=32) (actual time=0.257..0.257 rows=2 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
13. 0.002 0.253 ↑ 46.5 2 3

Unique (cost=299.41..299.88 rows=93 width=32) (actual time=0.251..0.253 rows=2 loops=3)

14. 0.041 0.251 ↑ 46.5 2 3

Sort (cost=299.41..299.65 rows=93 width=32) (actual time=0.250..0.251 rows=2 loops=3)

  • Sort Key: (lower(twitter_usernames.name))
  • Sort Method: quicksort Memory: 25kB
  • Worker 0: Sort Method: quicksort Memory: 25kB
  • Worker 1: Sort Method: quicksort Memory: 25kB
15. 0.086 0.210 ↑ 46.5 2 3

Nested Loop (cost=1.26..296.37 rows=93 width=32) (actual time=0.165..0.210 rows=2 loops=3)

16. 0.045 0.093 ↑ 46.5 2 3

Nested Loop (cost=0.84..252.02 rows=93 width=4) (actual time=0.071..0.093 rows=2 loops=3)

17. 0.008 0.008 ↑ 41.0 2 3

Index Only Scan using reports_organizations_unique on reports_organizations reports_organizations_1 (cost=0.42..45.03 rows=82 width=8) (actual time=0.007..0.008 rows=2 loops=3)

  • Index Cond: (report_id = 1101)
  • Heap Fetches: 6
18. 0.040 0.040 ↑ 1.0 1 6

Index Only Scan using twitter_usernames_orgaizations_key2 on twitter_usernames_organizations (cost=0.42..2.51 rows=1 width=8) (actual time=0.039..0.040 rows=1 loops=6)

  • Index Cond: (organization_id = reports_organizations_1.organization_id)
  • Heap Fetches: 6
19. 0.031 0.031 ↑ 1.0 1 6

Index Scan using twitter_usernames_pkey on twitter_usernames (cost=0.42..0.47 rows=1 width=16) (actual time=0.031..0.031 rows=1 loops=6)

  • Index Cond: (id = twitter_usernames_organizations.twitter_username_id)
20. 0.016 0.016 ↑ 1.0 1 11,637

Index Scan using twitter_users_pkey on twitter_users twitter_users_1 (cost=0.43..0.98 rows=1 width=345) (actual time=0.016..0.016 rows=1 loops=11,637)

  • Index Cond: (twitter_user_id = twitter_followers.twitter_user_id)
21. 1.856 27.995 ↑ 4,326.0 1 11,637

Bitmap Heap Scan on persons (cost=33.53..110.33 rows=4,326 width=1,284) (actual time=27.016..27.995 rows=1 loops=11,637)

  • Recheck Cond: (lower(personnm) % lower(twitter_users_1.name))
  • Rows Removed by Index Recheck: 326
  • Heap Blocks: exact=839293
22. 26.139 26.139 ↑ 13.2 328 11,637

Bitmap Index Scan on persons_lower_idx (cost=0.00..32.45 rows=4,326 width=0) (actual time=26.139..26.139 rows=328 loops=11,637)

  • Index Cond: (lower(personnm) % lower(twitter_users_1.name))
23. 0.004 0.077 ↑ 41.0 2 3

Hash (cost=45.03..45.03 rows=82 width=8) (actual time=0.077..0.077 rows=2 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
24. 0.073 0.073 ↑ 41.0 2 3

Index Only Scan using reports_organizations_unique on reports_organizations (cost=0.42..45.03 rows=82 width=8) (actual time=0.071..0.073 rows=2 loops=3)

  • Index Cond: (report_id = 1101)
  • Heap Fetches: 6