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.033..0.042 rows=5 loops=1)

  Recheck Cond: (a = 100)

  Heap Blocks: exact=5

  ->  Bitmap Index Scan on test_a_b_idx  (cost=0.00..12.05 rows=501 width=0) (actual time=0.019..0.020 rows=5 loops=1)

        Index Cond: (a = 100)

Planning Time: 0.933 ms

Execution Time: 0.102 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=0.682..18.101 rows=8 loops=1)

  Filter: (b = 100)

  Rows Removed by Filter: 99992

Planning Time: 0.043 ms

Execution Time: 18.119 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 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.083..0.089 rows=5 loops=1)

  Index Cond: (a = 100)

  Heap Fetches: 5

Planning Time: 0.070 ms

Execution Time: 0.120 ms

EXPLAIN

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

QUERY PLAN

Index Only Scan using test_a_b_idx on test  (cost=0.29..1400.19 rows=501 width=4) (actual time=1.133..4.010 rows=8 loops=1)

  Index Cond: (b = 100)

  Heap Fetches: 8

Planning Time: 0.044 ms

Execution Time: 4.027 ms

EXPLAIN