#	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

                                                                                                                                 QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=27611073.62..27611074.87 rows=500 width=1694) (actual time=109796.976..109796.978 rows=7 loops=1)
   ->  Sort  (cost=27611073.62..27615015.75 rows=1576851 width=1694) (actual time=109796.975..109796.975 rows=7 loops=1)
         Sort Key: twitter_users.followers_count DESC NULLS LAST, twitter_users.verified DESC
         Sort Method: quicksort  Memory: 31kB
         ->  Subquery Scan on twitter_users  (cost=27508848.07..27532500.84 rows=1576851 width=1694) (actual time=109796.914..109796.934 rows=7 loops=1)
               ->  Unique  (cost=27508848.07..27516732.33 rows=1576851 width=1680) (actual time=109796.895..109796.906 rows=7 loops=1)
                     ->  Sort  (cost=27508848.07..27512790.20 rows=1576851 width=1680) (actual time=109796.891..109796.893 rows=9 loops=1)
                           Sort Key: twitter_users_1.screen_name
                           Sort Method: quicksort  Memory: 33kB
                           ->  Gather  (cost=1381.99..26674126.28 rows=1576851 width=1680) (actual time=106475.188..109837.804 rows=9 loops=1)
                                 Workers Planned: 2
                                 Workers Launched: 2
                                 ->  Hash Join  (cost=381.99..26515441.18 rows=657021 width=1680) (actual time=61853.416..108905.154 rows=3 loops=3)
                                       Hash Cond: (persons.organization_id = reports_organizations.organization_id)
                                       ->  Nested Loop  (cost=335.94..24701617.77 rows=690925032 width=1680) (actual time=287.290..108903.185 rows=4886 loops=3)
                                             ->  Nested Loop  (cost=302.41..169215.12 rows=159722 width=396) (actual time=194.980..290.587 rows=3879 loops=3)
                                                   ->  Hash Join  (cost=301.97..13073.49 rows=159722 width=51) (actual time=194.925..217.227 rows=3879 loops=3)
                                                         Hash Cond: (lower(twitter_followers.primary_screen_name) = (lower(twitter_usernames.name)))
                                                         ->  Parallel Seq Scan on twitter_followers  (cost=0.00..11805.89 rows=343489 width=51) (actual time=0.018..72.694 rows=271564 loops=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
                                                               ->  Unique  (cost=299.41..299.88 rows=93 width=32) (actual time=0.251..0.253 rows=2 loops=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
                                                                           ->  Nested Loop  (cost=1.26..296.37 rows=93 width=32) (actual time=0.165..0.210 rows=2 loops=3)
                                                                                 ->  Nested Loop  (cost=0.84..252.02 rows=93 width=4) (actual time=0.071..0.093 rows=2 loops=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
                                                                                       ->  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
                                                                                 ->  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)
                                                   ->  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=11637)
                                                         Index Cond: (twitter_user_id = twitter_followers.twitter_user_id)
                                             ->  Bitmap Heap Scan on persons  (cost=33.53..110.33 rows=4326 width=1284) (actual time=27.016..27.995 rows=1 loops=11637)
                                                   Recheck Cond: (lower(personnm) % lower(twitter_users_1.name))
                                                   Rows Removed by Index Recheck: 326
                                                   Heap Blocks: exact=839293
                                                   ->  Bitmap Index Scan on persons_lower_idx  (cost=0.00..32.45 rows=4326 width=0) (actual time=26.139..26.139 rows=328 loops=11637)
                                                         Index Cond: (lower(personnm) % lower(twitter_users_1.name))
                                       ->  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
                                             ->  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
 Planning Time: 1.990 ms
 Execution Time: 109838.295 ms

Per node type stats

node type	count	sum of times	% of query
Bitmap Heap Scan	1	1.856 ms	0.0 %
Bitmap Index Scan	1	26.139 ms	0.0 %
Gather	1	932.650 ms	0.8 %
Hash	2	0.008 ms	0.0 %
Hash Join	2	146.168 ms	0.1 %
Index Only Scan	3	0.121 ms	0.0 %
Index Scan	2	0.047 ms	0.0 %
Limit	1	0.003 ms	0.0 %
Nested Loop	4	108,658.078 ms	99.0 %
Parallel Seq Scan	1	72.694 ms	0.1 %
Sort	3	0.082 ms	0.0 %
Subquery Scan	1	0.028 ms	0.0 %
Unique	2	0.015 ms	0.0 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
persons	1	1.856 ms	0.0 %
Bitmap Heap Scan	1	1.856 ms	100.0 %
reports_organizations	2	0.081 ms	0.0 %
Index Only Scan	2	0.081 ms	100.0 %
twitter_followers	1	72.694 ms	0.1 %
Parallel Seq Scan	1	72.694 ms	100.0 %
twitter_usernames	1	0.031 ms	0.0 %
Index Scan	1	0.031 ms	100.0 %
twitter_usernames_organizations	1	0.040 ms	0.0 %
Index Only Scan	1	0.040 ms	100.0 %
twitter_users	1	0.016 ms	0.0 %
Index Scan	1	0.016 ms	100.0 %

explain.depesz.com

PostgreSQL's explain analyze made readable

Result: KlbH

Color mode:

Visible columns:

Per node type stats

Per table stats