gistfile1.txt

SELECT  "statuses"."id", "statuses"."updated_at" FROM "statuses" 
LEFT OUTER JOIN mentions ON statuses.id = mentions.status_id AND mentions.account_id = 1867 
WHERE "statuses"."account_id" = 1 AND ("statuses"."visibility" IN (0,1,2) OR "mentions"."id" IS NOT NULL) 
ORDER BY "statuses"."id" DESC, "statuses"."visibility" DESC LIMIT 40;

Analyze
Limit  (cost=1932.69..1932.79 rows=40 width=20) (actual time=3057.493..3057.584 rows=40 loops=1)
  ->  Sort  (cost=1932.69..1933.78 rows=434 width=20) (actual time=3057.489..3057.534 rows=40 loops=1)
        Sort Key: statuses.id DESC, statuses.visibility DESC
        Sort Method: top-N heapsort  Memory: 28kB
        ->  Hash Right Join  (cost=1697.32..1918.97 rows=434 width=20) (actual time=3033.272..3054.277 rows=4530 loops=1)
              Hash Cond: (mentions.status_id = statuses.id)
              Filter: ((statuses.visibility = ANY ('{0,1,2}'::integer[])) OR (mentions.id IS NOT NULL))
              Rows Removed by Filter: 166
              ->  Bitmap Heap Scan on mentions  (cost=4.89..226.30 rows=60 width=16) (actual time=4.319..37.274 rows=23 loops=1)
                    Recheck Cond: (account_id = 1867)
                    Heap Blocks: exact=20
                    ->  Bitmap Index Scan on index_mentions_on_account_id_and_status_id  (cost=0.00..4.88 rows=60 width=0) (actual time=4.250..4.250 rows=23 loops=1)
                          Index Cond: (account_id = 1867)
              ->  Hash  (cost=1687.01..1687.01 rows=434 width=20) (actual time=3013.252..3013.252 rows=4696 loops=1)
                    Buckets: 8192 (originally 1024)  Batches: 1 (originally 1)  Memory Usage: 303kB
                    ->  Bitmap Heap Scan on statuses  (cost=11.79..1687.01 rows=434 width=20) (actual time=2.450..3000.897 rows=4696 loops=1)
                          Recheck Cond: (account_id = 1)
                          Heap Blocks: exact=4332
                          ->  Bitmap Index Scan on index_statuses_on_account_id_id  (cost=0.00..11.68 rows=434 width=0) (actual time=1.668..1.668 rows=4701 loops=1)
                                Index Cond: (account_id = 1)
Planning time: 0.772 ms
Execution time: 3057.880 ms