Статьи // db

MySql vs Postgres. Оператор LIKE

Alexander Vasilev, Telegram https://axnvas.t.me/

обновлено: 31 мар. 2025 г.  опубликовано: ⏰ 15
article image

Вы, наверное, помните эту известную сцену из фильма “Девушка с татуировкой дракона”? Главная героиня пытается получить полицейский отчет интересующих ее пользователей. Если поставить на паузу этот фрагмент, то можно заметить, что запрос содержит использование функции SUBSTR, где имя и фамилия начинаются на ‘R’ и ‘L’ соответственно. Но так ли это оптимально с точки зрения плана запроса? Как изменится план запроса, если мы попробуем добавить не сложные индексы? Будет ли оптимальнее план, если использовать привычный и понятный оператор LIKE?

В этой статье мы попробуем сравнить планы запросов популярных баз данных: MySQL и PostgreSQL. Важно, что статья не претендует на научную публикацию, это лишь попытка смоделировать подобную ситуацию и вынести на размышление.

Чем обоснован выбор СУБД

В сцене фильма героиня подключается к MySQL, а вот PostgreSQL был выбран исключительно из частого использования на проектах.

Данные

Для тестирования будем использовать уже готовый набор имен dataset

Среда

  • Процессор: Apple M4 Pro
  • Общее количество ядер: 14 (10 performance and 4 efficiency)
  • Память: 48 GB (LPDDR5)
  • Диск: SSD 500,28 GB (AP0512Z)

Подготовка

Сначала установим СУБД. Использовать будем виртуальную среду. Для этого можно использовать docker и docker-compose как привычный инструмент разработчика.

docker-compose.yml

YAML 
services:
  postgres:
    image: postgres:latest
    restart: on-failure
    ports:
      - "5432:5432"
    volumes:
      - postgres_data:/var/lib/postgresql/data
      - ./us_people_names.txt:/opt/test_data/us_people_names.txt
    environment:
      TZ: UTC+4
      POSTGRES_USER: root
      POSTGRES_PASSWORD: root
      POSTGRES_DB: operator-like
    healthcheck:
      test: pg_isready -U $$POSTGRES_USER -d $$POSTGRES_DB
      interval: 10s
      timeout: 5s
      start_period: 10s
      retries: 5
  mysql:
    image: mysql:latest
    restart: on-failure
    ports:
      - "3306:3306"
    volumes:
      - mysql_data:/var/lib/mysql
      - ./us_people_names.txt:/var/lib/mysql-files/us_people_names.txt
    environment:
      TZ: UTC+4
      MYSQL_ROOT_PASSWORD: root
      MYSQL_DATABASE: operator-like
    healthcheck:
      test: mysqladmin ping -u root -p $$MYSQL_ROOT_PASSWORD
      interval: 10s
      timeout: 5s
      start_period: 10s
      retries: 5
volumes:
  postgres_data:
  mysql_data:
  • docker-compose pull – загрузить все необходимые образа.
  • docker-compose up -d – позволяет собрать и запустить сервисы в background режиме.

В данном compose можно увидеть, что будет создана база данных с именем operator-like.

Также можно заметить bind mount для тестовых данных, чтобы можно было выполнить в дальнейшем импорт данных в таблицу.

Маппинг портов не обязателен, вы можете подключаться к серверу СУБД внутри контейнера. Для меня привычнее использовать datagrip в качестве клиента для популярных СУБД.

На момент написания статьи использовались следующие версии СУБД в образе:

  • MySQL=9.2.0-1.el9
  • PostgreSQL=17.4-1.pgdg120+2

После запуска сервисов, можно посмотреть их статус, выполнив команду docker-compose ps

Shell 
***@*** ~/I/r/o/operator-like> docker-compose ps
NAME                       IMAGE             COMMAND                  SERVICE    CREATED         STATUS                            PORTS
operator-like-mysql-1      mysql:latest      "docker-entrypoint.s…"   mysql      7 seconds ago   Up 6 seconds (health: starting)   0.0.0.0:3306->3306/tcp, 33060/tcp
operator-like-postgres-1   postgres:latest   "docker-entrypoint.s…"   postgres   7 seconds ago   Up 6 seconds (healthy)            0.0.0.0:5432->5432/tcp

После подключения можно наблюдать примерно такую структуру:

Реализация теста

Для тестирования будем использовать две базы данных: MySQL и PostgreSQL. Мы создадим в каждой из них по несколько таблиц, с разными типами индексов и без них. Затем выполним запрос на выборку всех записей из этих таблиц и замерим время выполнения и размеры занимаемых данных.

Тестирование без использования индексов

Создадим таблицу PEOPLE_WITHOUT_INDEX.

MySQL

MySQL 
CREATE TABLE PEOPLE_WITHOUT_INDEX
(
    STATE  VARCHAR(2),
    GENDER VARCHAR(1),
    YEAR   INT,
    NAME   VARCHAR(255),
    NUMBER INT
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_0900_ai_ci;

PostgreSQL

PostgreSQL 
CREATE TABLE PEOPLE_WITHOUT_INDEX
(
    STATE  VARCHAR,
    GENDER VARCHAR,
    YEAR   VARCHAR,
    NAME   VARCHAR(255),
    NUMBER VARCHAR
);

Сделаем импорт данных из CSV (принимаем во внимание header).

MySQL

MySQL 
LOAD DATA INFILE '/var/lib/mysql-files/us_people_names.txt'
    INTO TABLE PEOPLE_WITHOUT_INDEX
    FIELDS TERMINATED BY ','
    LINES TERMINATED BY '\n'
    IGNORE 1 ROWS;

PostgreSQL

PostgreSQL 
COPY PEOPLE_WITHOUT_INDEX FROM '/opt/test_data/us_people_names.txt'
    DELIMITER ',' CSV HEADER;

После импорта получим 5552452 строк в таблице.

SQL 
SELECT COUNT(*) FROM PEOPLE_WITHOUT_INDEX;

Посмотрим размер таблиц и индексов.

MySQL

MySQL 
SELECT TABLE_NAME,
       ROUND(DATA_LENGTH / 1024 / 1024)                  AS 'Table size (MB)',
       ROUND(INDEX_LENGTH / 1024 / 1024)                 AS 'Indexes size (MB)',
       ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) AS 'Total size (MB)'
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'operator-like';

PostgreSQL

PostgreSQL 
select TABLE_NAME,
       ROUND(PG_TABLE_SIZE(QUOTE_IDENT(TABLE_NAME)) / 1024 / 1024)    AS "Table size (MB)",
       ROUND(PG_INDEXES_SIZE(QUOTE_IDENT(TABLE_NAME)) / 1024 / 1024)  AS "Indexes size (MB)",
       ROUND(PG_TOTAL_RELATION_SIZE(QUOTE_IDENT(TABLE_NAME)) / 1024 / 1024) AS "Total size (MB)"
from INFORMATION_SCHEMA.TABLES
where TABLE_SCHEMA = 'public';
RDBMSTABLE_NAMETable size (MB)Indexes size (MB)Total size (MB)
MySQLPEOPLE_WITHOUT_INDEX2600260
PostgreSQLPEOPLE_WITHOUT_INDEX2760276

Теперь попробуем построить план запроса с простым условием (т.е. не содержит подзапросов и объединений) WHERE, как это делал хакер, и сразу выполним его.

Запрос содержит SUBSTR

SQL 
EXPLAIN ANALYZE SELECT *
FROM PEOPLE_WITHOUT_INDEX
WHERE SUBSTR(NAME, 1, 1) = 'R';

MySQL

MySQL 
-> Filter: (substr(PEOPLE_WITHOUT_INDEX.`NAME`,1,1) = 'R')  (cost=569651 rows=5.53e+6) (actual time=0.0886..1277 rows=319986 loops=1)
    -> Table scan on PEOPLE_WITHOUT_INDEX  (cost=569651 rows=5.53e+6) (actual time=0.0676..1072 rows=5.55e+6 loops=1)
Execution Time: 1326 ms

PostgreSQL

PostgreSQL 
Gather  (cost=1000.00..73846.91 rows=27762 width=18) (actual time=0.273..129.770 rows=319986 loops=1)
  Workers Planned: 2
  Workers Launched: 2
  ->  Parallel Seq Scan on people_without_index  (cost=0.00..70070.71 rows=11568 width=18) (actual time=0.045..123.214 rows=106662 loops=3)
"        Filter: (substr((name)::text, 1, 1) = 'R'::text)"
        Rows Removed by Filter: 1744155
Planning Time: 0.150 ms
Execution Time: 135.613 ms

Заменим функцию SUBSTR на LIKE с префиксом ‘R%’

SQL 
EXPLAIN ANALYZE SELECT *
FROM PEOPLE_WITHOUT_INDEX
WHERE NAME LIKE 'R%';

MySQL

MySQL 
-> Filter: (PEOPLE_WITHOUT_INDEX.`NAME` like 'R%')  (cost=569651 rows=614409) (actual time=0.0295..1225 rows=319986 loops=1)
    -> Table scan on PEOPLE_WITHOUT_INDEX  (cost=569651 rows=5.53e+6) (actual time=0.0233..1067 rows=5.55e+6 loops=1)
Execution Time: 1261 ms

PostgreSQL

PostgreSQL 
Gather  (cost=1000.00..97870.52 rows=325836 width=18) (actual time=0.200..94.721 rows=319986 loops=1)
  Workers Planned: 2
  Workers Launched: 2
  ->  Parallel Seq Scan on people_without_index  (cost=0.00..64286.92 rows=135765 width=18) (actual time=0.034..88.139 rows=106662 loops=3)
        Filter: ((name)::text ~~ 'R%'::text)
        Rows Removed by Filter: 1744155
Planning Time: 0.063 ms
Execution Time: 101.151 ms

Вывод: На первый взгляд, кажется, что на таком объеме данных без индексов сильной разницы нет между SUBSTR и LIKE. Но можно заметить, что PostgreSQL лучше справился с этой задачей по фактическому времени выполнению. Так как выделил несколько “воркеров” и использовал Parallel Seq Scan. Причем, дополнительно разработчику ничего не пришлось настраивать.

Тестирование с использованием B-tree индекса

Попробуем добавить B-tree индекс для колонки NAME и сравним планы запросов. Создадим таблицу PEOPLE_WITH_BTREE_INDEX, сделаем импорт данных из файла (как мы уже делали ранее) и добавим индекс.

MySQL (индекс при описании таблицы)

MySQL 
CREATE TABLE PEOPLE_WITH_BTREE_INDEX
(
    STATE  VARCHAR(2),
    GENDER VARCHAR(1),
    YEAR   INT,
    NAME   VARCHAR(255),
    NUMBER INT,
    KEY NAME_BTREE_IDX (NAME) USING BTREE
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_0900_ai_ci;

Обновим информацию о таблице

MySQL 
ANALYZE TABLE PEOPLE_WITH_BTREE_INDEX;

PostgreSQL (создание индекса)

PostgreSQL 
CREATE INDEX NAME_BTREE_IDX ON PEOPLE_WITH_BTREE_INDEX USING BTREE (NAME);

Для большей эффективности пересчитаем reltuples и relpages

PostgreSQL 
VACUUM ANALYZE;

Запрос содержит SUBSTR

SQL 
EXPLAIN ANALYZE
SELECT *
FROM PEOPLE_WITH_BTREE_INDEX
WHERE SUBSTR(NAME, 1, 1) = 'R';

MySQL

MySQL 
-> Filter: (substr(PEOPLE_WITH_BTREE_INDEX.`NAME`,1,1) = 'R')  (cost=569816 rows=5.53e+6) (actual time=0.309..1264 rows=319986 loops=1)
    -> Table scan on PEOPLE_WITH_BTREE_INDEX  (cost=569816 rows=5.53e+6) (actual time=0.298..1060 rows=5.55e+6 loops=1)
Execution Time: 1295 ms

PostgreSQL

PostgreSQL 
Gather  (cost=1000.00..73847.12 rows=27762 width=18) (actual time=0.338..132.379 rows=319986 loops=1)
  Workers Planned: 2
  Workers Launched: 2
  ->  Parallel Seq Scan on people_with_btree_index  (cost=0.00..70070.93 rows=11568 width=18) (actual time=0.046..125.406 rows=106662 loops=3)
"        Filter: (substr((name)::text, 1, 1) = 'R'::text)"
        Rows Removed by Filter: 1744155
Planning Time: 0.194 ms
Execution Time: 138.543 ms

Заменим функцию SUBSTR на LIKE с префиксом ‘R%’

SQL 
EXPLAIN ANALYZE
SELECT *
FROM PEOPLE_WITH_BTREE_INDEX
WHERE NAME LIKE 'R%';

MySQL

MySQL 
-> Filter: (PEOPLE_WITH_BTREE_INDEX.`NAME` like 'R%')  (cost=569816 rows=855298) (actual time=0.215..1365 rows=319986 loops=1)
    -> Table scan on PEOPLE_WITH_BTREE_INDEX  (cost=569816 rows=5.53e+6) (actual time=0.207..1208 rows=5.55e+6 loops=1)
Execution Time: 1401 ms

PostgreSQL

PostgreSQL 
Seq Scan on people_with_btree_index  (cost=10000000000.00..10000104773.85 rows=323341 width=18) (actual time=17.707..202.756 rows=319986 loops=1)
  Filter: ((name)::text ~~ 'R%'::text)
  Rows Removed by Filter: 5232466
Planning Time: 0.091 ms
JIT:
  Functions: 2
"  Options: Inlining true, Optimization true, Expressions true, Deforming true"
"  Timing: Generation 0.628 ms (Deform 0.219 ms), Inlining 4.446 ms, Optimization 7.710 ms, Emission 5.484 ms, Total 18.269 ms"
Execution Time: 209.402 ms

Попробуем зафорсить план, где будет принудительно использован индекс NAME_BTREE_IDX

MySQL

MySQL 
EXPLAIN ANALYZE
SELECT *
FROM PEOPLE_WITH_BTREE_INDEX FORCE INDEX (NAME_BTREE_IDX)
WHERE NAME LIKE 'R%';
---
-> Index range scan on PEOPLE_WITH_BTREE_INDEX using NAME_BTREE_IDX over ('R' <= NAME <= 'R??'), with index condition: (PEOPLE_WITH_BTREE_INDEX.`NAME` like 'R%')  (cost=2.21 rows=1) (actual time=0.0753..8306 rows=319986 loops=1)
Execution Time: 8373 ms

PostgreSQL

PostgreSQL 
SET ENABLE_SEQSCAN = false;

EXPLAIN ANALYZE
SELECT *
FROM PEOPLE_WITH_BTREE_INDEX
WHERE NAME LIKE 'R%';
---
Seq Scan on people_with_btree_index  (cost=10000000000.00..10000104773.85 rows=323341 width=18) (actual time=18.625..200.180 rows=319986 loops=1)
  Filter: ((name)::text ~~ 'R%'::text)
  Rows Removed by Filter: 5232466
Planning Time: 0.072 ms
JIT:
  Functions: 2
"  Options: Inlining true, Optimization true, Expressions true, Deforming true"
"  Timing: Generation 0.211 ms (Deform 0.093 ms), Inlining 4.118 ms, Optimization 9.777 ms, Emission 4.708 ms, Total 18.814 ms"
Execution Time: 206.165 ms

Небольшое наблюдение, если нужно только посчитать кол-во таких строк, то производительность в обоих случаях кратно возрастает (наблюдаем полное покрытие индексом)

SQL 
EXPLAIN ANALYZE
SELECT COUNT(*)
FROM PEOPLE_WITH_BTREE_INDEX
WHERE NAME LIKE 'R%';

MySQL

MySQL 
-> Aggregate: count(0)  (cost=464374 rows=1) (actual time=89.1..89.1 rows=1 loops=1)
    -> Filter: (PEOPLE_WITH_BTREE_INDEX.`NAME` like 'R%')  (cost=267301 rows=855298) (actual time=0.0189..80.5 rows=319986 loops=1)
        -> Covering index range scan on PEOPLE_WITH_BTREE_INDEX using NAME_BTREE_IDX over ('R' <= NAME <= 'R??')  (cost=267301 rows=855298) (actual time=0.0168..62.3 rows=319986 loops=1)
Execution Time: 103 ms

PostgreSQL

PostgreSQL 
Finalize Aggregate  (cost=77542.90..77542.91 rows=1 width=8) (actual time=94.663..96.282 rows=1 loops=1)
  ->  Gather  (cost=77542.69..77542.90 rows=2 width=8) (actual time=94.605..96.279 rows=3 loops=1)
        Workers Planned: 2
        Workers Launched: 2
        ->  Partial Aggregate  (cost=76542.69..76542.70 rows=1 width=8) (actual time=84.726..84.727 rows=1 loops=3)
              ->  Parallel Index Only Scan using name_btree_idx on people_with_btree_index  (cost=0.43..76205.88 rows=134725 width=0) (actual time=66.855..82.297 rows=106662 loops=3)
                    Filter: ((name)::text ~~ 'R%'::text)
                    Rows Removed by Filter: 1744155
                    Heap Fetches: 0
Planning Time: 0.091 ms
Execution Time: 96.308 ms

В реальной жизни данные возможно бы запрашивались постранично, попробуем добавить пагинацию и не забываем конечно же, что данные нужно отсортировать.

Запрос получить 100 записей со смещением в 10 000.

SQL 
EXPLAIN ANALYZE
SELECT *
FROM PEOPLE_WITH_BTREE_INDEX
WHERE NAME LIKE 'R%'
ORDER BY NAME
LIMIT 100 OFFSET 10000;

MySQL

MySQL 
-> Limit/Offset: 100/10000 row(s)  (cost=569816 rows=100) (actual time=212..214 rows=100 loops=1)
    -> Index range scan on PEOPLE_WITH_BTREE_INDEX using NAME_BTREE_IDX over ('R' <= NAME <= 'R??'), with index condition: (PEOPLE_WITH_BTREE_INDEX.`NAME` like 'R%')  (cost=569816 rows=855298) (actual time=0.0246..213 rows=10100 loops=1)
Execution Time: 236 ms

PostgreSQL

PostgreSQL 
Limit  (cost=7984.25..8064.09 rows=100 width=18) (actual time=3512.102..3512.184 rows=100 loops=1)
  ->  Index Scan using name_btree_idx on people_with_btree_index  (cost=0.43..258150.05 rows=323341 width=18) (actual time=3503.577..3512.018 rows=10100 loops=1)
        Filter: ((name)::text ~~ 'R%'::text)
        Rows Removed by Filter: 4371454
Planning Time: 0.160 ms
Execution Time: 3512.221 ms

Вывод: добавление B-tree индекса не только не улучшает ситуацию, а ухудшает ее. Планировщик не всегда отдает предпочтение Index scan, видимо по каким-то причинам предпочтение отдается Seq Scan ( статистика, ssd диск и др.).

Но если необходимо, только посчитать кол-во таких строк, то индекс кратно улучшает ситуацию. В плане можно наблюдать полное покрытие индексом.

Также если попробовать приблизить ситуацию к реальной жизни, где мы запрашиваем данные постранично, то MySQL сильно выигрывает в производительности за счет использования B-tree индекса. Дополнительно проводились тесты без индекса - результаты были значительно хуже.

Тестирование с использованием специализированных индексов

Попробуем исправить ситуацию и добавить полнотекстовый индекс для колонки NAME и также сравним планы запросов.

Для MySQL будем использовать FULLTEXT индекс.

Для PostgreSQL будем использовать GIN индекс. GIN индекс требует чтобы тип данных был векторным, для этого добавим дополнительную колонку NAME_VECTOR. Данные получим путем преобразования NAME в вектор, функция TO_TSVECTOR.

Создадим таблицы PEOPLE_WITH_TEXT_INDEX соответственно. Повторим импорт данных из файла, и добавим индексы.

Важно: в этом случае импорт данных может занять значительное время!

MySQL (индекс при описании таблицы)

MySQL 
CREATE TABLE PEOPLE_WITH_TEXT_INDEX
(
    STATE  VARCHAR(2),
    GENDER VARCHAR(1),
    YEAR   INT,
    NAME   VARCHAR(255),
    NUMBER INT,
    FULLTEXT KEY NAME_FULLTEXT_IDX (NAME)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_0900_ai_ci;

PostgreSQL (добавление колонки)

PostgreSQL 
ALTER TABLE PEOPLE_WITH_TEXT_INDEX
    ADD COLUMN NAME_VECTOR TSVECTOR;

PostgreSQL (заполняем данными)

PostgreSQL 
UPDATE PEOPLE_WITH_TEXT_INDEX
SET NAME_VECTOR = TO_TSVECTOR(NAME)
WHERE NAME_VECTOR IS NULL;

PostgreSQL (создаем индекс)

PostgreSQL 
CREATE INDEX NAME_GIN_IDX ON PEOPLE_WITH_TEXT_INDEX USING GIN (NAME_VECTOR);

Сравним планы запросов

Запрос содержит SUBSTR

SQL 
EXPLAIN ANALYZE
SELECT *
FROM PEOPLE_WITH_TEXT_INDEX
WHERE SUBSTR(NAME, 1, 1) = 'R';

MySQL

MySQL 
-> Filter: (substr(PEOPLE_WITH_TEXT_INDEX.`NAME`,1,1) = 'R')  (cost=559153 rows=5.4e+6) (actual time=0.035..1458 rows=319986 loops=1)
    -> Table scan on PEOPLE_WITH_TEXT_INDEX  (cost=559153 rows=5.4e+6) (actual time=0.027..1254 rows=5.55e+6 loops=1)
Execution Time: 1493 ms

PostgreSQL

PostgreSQL 
Gather  (cost=1000.00..86324.73 rows=27762 width=37) (actual time=0.265..136.680 rows=319986 loops=1)
  Workers Planned: 2
  Workers Launched: 2
  ->  Parallel Seq Scan on people_with_text_index  (cost=0.00..82548.53 rows=11568 width=37) (actual time=0.034..123.265 rows=106662 loops=3)
"        Filter: (substr((name)::text, 1, 1) = 'R'::text)"
        Rows Removed by Filter: 1744155
Planning Time: 0.112 ms
Execution Time: 142.652 ms

Заменим функцию SUBSTR на LIKE с префиксом ‘R%’

SQL 
EXPLAIN ANALYZE
SELECT *
FROM PEOPLE_WITH_TEXT_INDEX
WHERE NAME LIKE 'R%';

MySQL

MySQL 
-> Filter: (PEOPLE_WITH_TEXT_INDEX.`NAME` like 'R%')  (cost=559153 rows=599473) (actual time=0.0275..1394 rows=319986 loops=1)
    -> Table scan on PEOPLE_WITH_TEXT_INDEX  (cost=559153 rows=5.4e+6) (actual time=0.0215..1239 rows=5.55e+6 loops=1)
Execution Time: 1433 ms

PostgreSQL

PostgreSQL 
Seq Scan on people_with_text_index  (cost=10000000000.00..10000117251.06 rows=349252 width=37) (actual time=17.419..215.448 rows=319986 loops=1)
  Filter: ((name)::text ~~ 'R%'::text)
  Rows Removed by Filter: 5232466
Planning Time: 0.092 ms
JIT:
  Functions: 2
"  Options: Inlining true, Optimization true, Expressions true, Deforming true"
"  Timing: Generation 0.375 ms (Deform 0.215 ms), Inlining 4.066 ms, Optimization 8.088 ms, Emission 5.183 ms, Total 17.712 ms"
Execution Time: 221.606 ms

Используем возможности полнотекстового поиска, преобразуем запрос LIKE 'R%'

MySQL (используем выражения MATCH и AGAINST) https://dev.mysql.com/doc/refman/8.4/en/fulltext-boolean.html

MySQL 
EXPLAIN ANALYZE
SELECT *
FROM PEOPLE_WITH_TEXT_INDEX
WHERE MATCH(NAME) AGAINST ('R*' IN BOOLEAN MODE);
---
-> Filter: (match PEOPLE_WITH_TEXT_INDEX.`NAME` against ('R*' in boolean mode))  (cost=1.1 rows=1) (actual time=38.3..511 rows=319984 loops=1)
    -> Full-text index search on PEOPLE_WITH_TEXT_INDEX using NAME_FULLTEXT_IDX (NAME = 'R*')  (cost=1.1 rows=1) (actual time=38.3..500 rows=319984 loops=1)
Execution Time: 708 ms

PostgreSQL (используем to_tsquery) https://www.postgresql.org/docs/current/textsearch-controls.html

PostgreSQL 
EXPLAIN ANALYZE
SELECT *
FROM PEOPLE_WITH_TEXT_INDEX
WHERE NAME_VECTOR @@ to_tsquery('R:*');
---
Gather  (cost=3562.52..128540.80 rows=368393 width=37) (actual time=48.037..105.636 rows=319986 loops=1)
  Workers Planned: 2
  Workers Launched: 2
  ->  Parallel Bitmap Heap Scan on people_with_text_index  (cost=2562.52..90701.50 rows=153497 width=37) (actual time=36.083..87.022 rows=106662 loops=3)
        Recheck Cond: (name_vector @@ to_tsquery('R:*'::text))
        Heap Blocks: exact=14652
        ->  Bitmap Index Scan on name_gin_idx  (cost=0.00..2470.42 rows=368393 width=0) (actual time=42.129..42.130 rows=319986 loops=1)
              Index Cond: (name_vector @@ to_tsquery('R:*'::text))
Planning Time: 0.415 ms
JIT:
  Functions: 9
"  Options: Inlining false, Optimization false, Expressions true, Deforming true"
"  Timing: Generation 0.935 ms (Deform 0.408 ms), Inlining 0.000 ms, Optimization 0.272 ms, Emission 3.863 ms, Total 5.070 ms"
Execution Time: 112.505 ms

Вывод: только лишь добавление полнотекстовых индексов никак не улучшит ситуацию, даже если пытаться зафорсить планировщик (как правило предпочтение будет отдано seq scan). Такие индексы требует знаний определенного синтаксиса, а также понимание как они устроены в разных СУБД. Это позволит писать оптимальные запросы, которые дают значительный прирост производительности. В примерах выше показано как можно получить одинаковые результаты также, как если бы мы использовали оператор LIKE.

Дополнительные проверки

Важно понимать, что поиск по одному символу является едва ли селективным. На этот счет есть отдельные дискуссии: https://stackoverflow.com/a/13452528.

И были проведены дополнительные эксперименты, которые показали, что индекс никак не улучшает ситуацию:

SQL 
EXPLAIN ANALYZE
SELECT *
FROM PEOPLE_WITH_TEXT_INDEX
WHERE NAME LIKE 'Loi%';

Для PostgreSQL еще проводили тесты с использованием оператора ILIKE (регистронезависимый like), вкратце, результаты значительно хуже по сравнению с LIKE

Понятно, что можно выжать максимум производительности зная как “готовить” определенную СУБД. Мы пытались понять, что можно получить имея какое-то общее представление.

Сводная таблица

Среднее время (Execution Time)

TableOperationMySQL, msPostgreSQL, ms
PEOPLE_WITHOUT_INDEXSUBSTR1326135.6
PEOPLE_WITHOUT_INDEXLIKE1261101
PEOPLE_WITH_BTREE_INDEXSUBSTR1295138.5
PEOPLE_WITH_BTREE_INDEXLIKE1401209.4
PEOPLE_WITH_BTREE_INDEXLIKE (force index)8373206.1
PEOPLE_WITH_BTREE_INDEXLIKE (only count)10396.3
PEOPLE_WITH_BTREE_INDEXLIKE (pagination)2363512.2
PEOPLE_WITH_TEXT_INDEXSUBSTR1493142.6
PEOPLE_WITH_TEXT_INDEXLIKE1433221.6
PEOPLE_WITH_TEXT_INDEXLIKE (force index)--
PEOPLE_WITH_TEXT_INDEXLIKE (only count)--
PEOPLE_WITH_TEXT_INDEXLIKE (pagination)--
PEOPLE_WITH_TEXT_INDEXMATCH708-
PEOPLE_WITH_TEXT_INDEXtsquery (vector)-112.5

Место на диске (под таблицу и индекс)

TableMySQL, table MbPostgreSQL, table MbMySQL, index MbPostgreSQL, index MbMySQL, total MbPostgreSQL, total Mb
PEOPLE_WITHOUT_INDEX26027600260276
PEOPLE_WITH_BTREE_INDEX26027618638446314
PEOPLE_WITH_TEXT_INDEX30637310527411401

Дополнительно к статье

Для планировщика PostgreSQL можно использовать сервис https://explain.tensor.ru/ наглядно показывает, где имеются проблемы.

Еще есть альтернативный сервис https://explain.depesz.com/

В этом примере можно увидеть:

  • # - порядковый номер
  • exclusive - время на операцию
  • inclusive - время на выполнение всех связанных операций (на примере видно, что для узла Gather нужно выполнить Seq Scan)
  • rows x - промах планировщика (кол-во строк)
Performance Search Mysql Postgresql