Skip to content

Instantly share code, notes, and snippets.

@tateisu
Created October 12, 2017 13:57
Show Gist options
  • Save tateisu/70d0144f055d72d3c9c5c800b5feaec3 to your computer and use it in GitHub Desktop.
Save tateisu/70d0144f055d72d3c9c5c800b5feaec3 to your computer and use it in GitHub Desktop.
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment