Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE test (
    a INT,
    b INT
);

CREATE TABLE

-- Составной индекс
CREATE INDEX on test(a, b);

CREATE INDEX

WITH numbers AS (
  SELECT *
  FROM generate_series(1, 100000)
)

INSERT INTO test(a, b)
SELECT 
    (round(random() * (10000 - 1)) + 1) AS a, 
    (round(random() * (10000 - 1)) + 1) AS b
FROM numbers;

INSERT 0 100000

-- Обычный запрос по первой колонке, использует индекс
EXPLAIN ANALYZE
SELECT a FROM test WHERE a = 100;

QUERY PLAN

Bitmap Heap Scan on test  (cost=12.18..482.70 rows=501 width=4) (actual time=0.028..0.048 rows=12 loops=1)

  Recheck Cond: (a = 100)

  Heap Blocks: exact=12

  ->  Bitmap Index Scan on test_a_b_idx  (cost=0.00..12.05 rows=501 width=0) (actual time=0.017..0.018 rows=12 loops=1)

        Index Cond: (a = 100)

Planning Time: 0.291 ms

Execution Time: 0.093 ms

EXPLAIN

-- Обычный запрос по второй колонке, не использует индекс
EXPLAIN ANALYZE
SELECT b FROM test WHERE b = 100;

QUERY PLAN

Seq Scan on test  (cost=0.00..1694.48 rows=501 width=4) (actual time=2.138..17.018 rows=6 loops=1)

  Filter: (b = 100)

  Rows Removed by Filter: 99994

Planning Time: 0.045 ms

Execution Time: 17.036 ms

EXPLAIN

-- Обычный запрос по второй колонке, не использует индекс
EXPLAIN ANALYZE
SELECT b FROM test WHERE b = 100;

QUERY PLAN

Seq Scan on test  (cost=0.00..1694.48 rows=501 width=4) (actual time=1.785..13.282 rows=6 loops=1)

  Filter: (b = 100)

  Rows Removed by Filter: 99994

Planning Time: 0.063 ms

Execution Time: 13.301 ms

EXPLAIN

-- В Postgres есть два параметра, определяющие стоимость запроса
-- Если у нас HDD, то последовательное чтение быстрее и стоимость у него меньше
-- Но если у нас SSD, то рандомное чтение выигрывает
-- Подробнее тут https://bit.ly/2tfsnmd
SHOW seq_page_cost;
SHOW random_page_cost;

seq_page_cost

1

SHOW

random_page_cost

4

SHOW

-- Поскольку Sec Scan для SSD более медленный вариант, то меняем приоритеты местами
SET seq_page_cost TO 4;
SET random_page_cost TO 1;

SET

SET

-- По второму ключу получаем поиск по индексу и почти пятикратный прирост по скорости
-- Который будет только расти с размером таблицы
EXPLAIN ANALYZE
SELECT b FROM test WHERE b = 100;

QUERY PLAN

Index Only Scan using test_a_b_idx on test  (cost=0.29..1411.19 rows=501 width=4) (actual time=0.191..3.591 rows=6 loops=1)

  Index Cond: (b = 100)

  Heap Fetches: 6

Planning Time: 0.132 ms

Execution Time: 3.614 ms

EXPLAIN

-- По первому ключу получаем почти двукратный прирост по скорости
EXPLAIN ANALYZE
SELECT a FROM test WHERE a = 100;

QUERY PLAN

Index Only Scan using test_a_b_idx on test  (cost=0.29..332.06 rows=501 width=4) (actual time=0.013..0.023 rows=12 loops=1)

  Index Cond: (a = 100)

  Heap Fetches: 12

Planning Time: 0.053 ms

Execution Time: 0.039 ms

EXPLAIN