EXPLAIN (ANALYZE, BUFFERS) SELECT user_id, rating FROM accounts WHERE game_id = 123 ORDER BY rating DESC LIMIT 3000;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=2475021.96..2475029.46 rows=3000 width=12) (actual time=204038.913..204039.763 rows=3000 loops=1)
Buffers: shared hit=79631 read=1385187 written=4601
-> Sort (cost=2475021.96..2480821.74 rows=2319911 width=12) (actual time=204038.911..204039.447 rows=3000 loops=1)
Sort Key: rating DESC
Sort Method: top-N heapsort Memory: 333kB
Buffers: shared hit=79631 read=1385187 written=4601
-> Bitmap Heap Scan on accounts (cost=549639.99..2329438.88 rows=2319911 width=12) (actual time=17485.007..202936.757 rows=2330230 loops=1)
Recheck Cond: (game_id = 123)
Rows Removed by Index Recheck: 1509171
Heap Blocks: exact=363810 lossy=645363
Buffers: shared hit=79631 read=1385187 written=4601
-> Bitmap Index Scan on uk_account_user_game (cost=0.00..549060.02 rows=2319911 width=0) (actual time=17338.478..17338.478 rows=2685830 loops=1)
Index Cond: (game_id = 123)
Buffers: shared hit=51458 read=404187 written=4601
Planning time: 0.170 ms
Execution time: 204044.322 msEXPLAIN (ANALYZE, BUFFERS) SELECT user_id, rating FROM accounts WHERE game_id = 123 AND last_played > '2019-01-01 00:00:00' ORDER BY rating DESC LIMIT 3000;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.43..11604.33 rows=3000 width=12) (actual time=1.270..2399.434 rows=3000 loops=1)
Buffers: shared hit=79253 read=8879
-> Index Scan Backward using account_last_played_idx on accounts (cost=0.43..1797664.90 rows=464757 width=12) (actual time=1.269..2397.924 rows=3000 loops=1)
Filter: ((last_played > '2019-01-01 00:00:00'::timestamp without time zone) AND (game_id = 123))
Rows Removed by Filter: 11100
Buffers: shared hit=79253 read=8879
Planning time: 0.205 ms
Execution time: 2400.207 ms
(8 строк)