=> CREATE INDEX ON ticket_flights(amount);
CREATE INDEX
=> EXPLAIN ANALYZE SELECT * FROM ticket_flights WHERE amount > 180000;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on ticket_flights (cost=839.45..63797.00 rows=44776 width=32) (actual time=10.525..229.515 rows=55640 loops=1)
Recheck Cond: (amount > '180000'::numeric)
Heap Blocks: exact=1653
-> Bitmap Index Scan on ticket_flights_amount_idx (cost=0.00..828.25 rows=44776 width=0) (actual time=9.218..9.218 rows=55640 loops=1)
Index Cond: (amount > '180000'::numeric)
Planning time: 8.820 ms
Execution time: 248.294 ms
(7 rows)
Запретим сканирование по битовой карте.
=> SET enable_bitmapscan = off;
SET
=> EXPLAIN ANALYZE SELECT * FROM ticket_flights WHERE amount > 180000;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
Gather (cost=1000.00..115263.16 rows=44776 width=32) (actual time=1.258..2722.847 rows=55640 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Seq Scan on ticket_flights (cost=0.00..109785.56 rows=18657 width=32) (actual time=590.010..2563.541 rows=18547 loops=3)
Filter: (amount > '180000'::numeric)
Rows Removed by Filter: 2778737
Planning time: 0.090 ms
Execution time: 2739.809 ms
(8 rows)
Для небольшой выборки сканирование по битовой карте оказывается эффективнее.
=> RESET enable_bitmapscan;
RESET
=> EXPLAIN ANALYZE SELECT * FROM ticket_flights WHERE amount < 44000;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
Seq Scan on ticket_flights (cost=0.00..170976.15 rows=7561892 width=32) (actual time=16.425..3722.229 rows=7583228 loops=1)
Filter: (amount < '44000'::numeric)
Rows Removed by Filter: 808624
Planning time: 0.114 ms
Execution time: 5588.814 ms
(5 rows)
Запретим последовательное сканирование.
=> SET enable_seqscan = off;
SET
=> EXPLAIN ANALYZE SELECT * FROM ticket_flights WHERE amount < 44000;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on ticket_flights (cost=141553.10..302154.75 rows=7561892 width=32) (actual time=2801.096..6475.452 rows=7583228 loops=1)
Recheck Cond: (amount < '44000'::numeric)
Heap Blocks: exact=65494
-> Bitmap Index Scan on ticket_flights_amount_idx (cost=0.00..139662.62 rows=7561892 width=0) (actual time=2782.482..2782.482 rows=7583228 loops=1)
Index Cond: (amount < '44000'::numeric)
Planning time: 0.109 ms
Execution time: 8395.476 ms
(7 rows)
Для большой выборки полное сканирование выгоднее.
Следует также учесть, что повторно запрос обычно выполняется быстрее из-за кэширования.