MySql vs Postgres. LIKE operator

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

updated on:

Mar 31, 2025

published: Mar 23, 2025 ⏰ 12

You probably remember that famous scene in a film called “The Girl with the Dragon Tattoo”. The main character is trying to get a police report about (интересующих) people. If you pause this episode, you can notice that SQL-query contains function SUBSTR for searching people by first and last name starting with ‘R’ and ‘L’ accordingly. But is it really optimal query plan? How query plan will change if we try to create typical database indexes? Would query plan be more optimal if we use popular and clear operator LIKE?

In this article we will try to compare query plans of popular databases MySQL и PostgreSQL. Disclamer: this article is not a science report, it’s just an attemp to model the film scene in order to give you some food for thoughts.

What RDBMS were used

In the film scene our character gets access to MySQL. As for PostgreSQL, it was chosen only because of frequent use on projects.

Test data

We will use ready dataset of names for testing

Environment

Chip: Apple M4 Pro
Total Number of Cores: 14 (10 performance and 4 efficiency)
Memory: 48 GB (LPDDR5)
Disk: SSD 500,28 GB (AP0512Z)

Preparation

Firstly, we shall install RDBMS. We will use a virtual environment. Docker with docker-compose can be used as a standard developing tool.

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 – pull all neccessary images.
docker-compose up -d – build and run all services in background mode.

In the compose above you can see that database with a name operator-like is created.

Also, you can notice bind mount for the dataset, which further will allow us to import data into a table.

Ports mapping is not mandatory, you can connect to RDBMS server inside of docker container. Personally, I prefer to use datagrip as a client for popular databases.

At the time of writing this article, the following RDBMS versions are used in the image:

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

After services run, you can see their status by the command 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

After connecting you can observe the next structure:

Test implementation

For the testing we will use two databases: MySQL and PostgreSQL. We will create several tables in each of them with different types of indexes and also without them. After that, we will execute SQL-query that will select all raws from these tables. Then, we will measure execution time and disk space.

Test without indexes

Let’s create a table PEOPLE_WITHOUT_INDEX.

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

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

Next, we import data from CSV (taking into account a header row)

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

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

After import, we get 5552452 raws in the table.

SQL

SELECT COUNT(*) FROM PEOPLE_WITHOUT_INDEX;

Then, look at size of tables and indexes.

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

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';

RDBMS	TABLE_NAME	Table size (MB)	Indexes size (MB)	Total size (MB)
MySQL	PEOPLE_WITHOUT_INDEX	260	0	260
PostgreSQL	PEOPLE_WITHOUT_INDEX	276	0	276

Now, we try to build a query plan with a simple WHERE condition (without any sub-queries and joins), the way the main character does it, and execute the query itself.

Query with SUBSTR function

SQL

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

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

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

Replace SUBSTR function with LIKE operator with prefix ‘R%’

SQL

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

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

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

Summary: at first glance, it seems like there is not much of a difference between SUBSTR and LIKE on such data size without indexes. But you can notice from execution time that PostgreSQL performed the query faster, because it used Parallel Seq Scan with several workers. Moreover, the developer didn’t have to set anything up.

Test with B-tree index

Let’s try to create B-tree index for the NAME column and compare query plans. We will create a table PEOPLE_WITH_BTREE_INDEX, import the data file (as we have done before).

MySQL (creating index in DDL)

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;

Next, update table information.

MySQL

ANALYZE TABLE PEOPLE_WITH_BTREE_INDEX;

PostgreSQL (creating index)

PostgreSQL

CREATE INDEX NAME_BTREE_IDX ON PEOPLE_WITH_BTREE_INDEX USING BTREE (NAME);

Recalculate reltuples and relpages for greater efficiency.

PostgreSQL

VACUUM ANALYZE;

Query with SUBSTR function

SQL

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

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

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

Replace SUBSTR function with LIKE operator with prefix ‘R%’

SQL

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

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

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

Let’s try to force RDBMS to make it use the index NAME_BTREE_IDX.

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

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

A litte fact, if you only need to count the number of such raws, performance will increase significantly in both cases (full index cover)

SQL

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

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

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

In a real project we ofter select data with pagination, so let’s try to add it. Don’t forget that data needs to be ordered.

Query with pagination

SQL

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

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

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

Summary: adding of a B-tree index not only doesn’t improve the situation, but actually makes it worse. Planner always used Seq Scan instead of Index scan. (it seems like it happens because of statistics, ssd disk, e.g.)

If you only need to count the number of such raws, B-tree index will increase performance significantly. In plan you can see full index cover.

Also, if we bring the situation closer to real life, where we get data with pagination, then MySQL more efficient because of B-tree index.

Test with special index

Let’s try to fix the situation, create fulltext index for NAME column and compare query plans.

For MySQL, we will use FULLTEXT index.

For PostgreSQL, we will use GIN index, which requires a vector data type, so we will add an additional column NAME_VECTOR. In order to fill this column with data we can transform NAME to vector by using function TO_TSVECTOR.

Let’s make tables PEOPLE_WITH_TEXT_INDEX, import data from file and add indexes.

Note: import takes a lot of time!

MySQL (create index in DDL)

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 (add column)

PostgreSQL

ALTER TABLE PEOPLE_WITH_TEXT_INDEX
    ADD COLUMN NAME_VECTOR TSVECTOR;

PostgreSQL (fill data)

PostgreSQL

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

PostgreSQL (create index)

PostgreSQL

CREATE INDEX NAME_GIN_IDX ON PEOPLE_WITH_TEXT_INDEX USING GIN (NAME_VECTOR);

Now, compare query plans

Query with SUBSTR function

SQL

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

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

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

Replace SUBSTR function with LIKE operator with prefix ‘R%’

SQL

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

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

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

Let’s use abilities of fulltext search and transform the query to LIKE 'R%'

MySQL (use MATCH and 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 (use to_tsquery function) 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

Summary: only adding fulltext indexes doesn’t improve the situation, even if we try to force the planner (as a rule, Seq scan is used). Such indexes require knowledge of special syntax and the way they work under the hood in different RDBMS. This knowledge will allow you to write efficient queries to improve performance significantly. In examples above, it was demonstrated how to get the same results as if we used LIKE operator.

Additional tests

We need to understand that searching by one symbol (character) is hardly selective. There are discussions about that, for instance: https://stackoverflow.com/a/13452528.

Also, some additional tests show that fulltext index doesn’t make the situation better:

SQL

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

Behind the scenes, we compared ILIKE (case-insensitive like) with LIKE operator on the same queries for PostgreSQL. Long story short, ILIKE is much worse than LIKE.

It’s clear taht you can achieve max performace if you know how to “cook” certain RDBMS. We just tried to figure out, what we can get if we have only high-level knowledge.

Results

Average execution Time

Table	Operation	MySQL, ms	PostgreSQL, ms
PEOPLE_WITHOUT_INDEX	SUBSTR	1326	135.6
PEOPLE_WITHOUT_INDEX	LIKE	1261	101
PEOPLE_WITH_BTREE_INDEX	SUBSTR	1295	138.5
PEOPLE_WITH_BTREE_INDEX	LIKE	1401	209.4
PEOPLE_WITH_BTREE_INDEX	LIKE (force index)	8373	206.1
PEOPLE_WITH_BTREE_INDEX	LIKE (only count)	103	96.3
PEOPLE_WITH_BTREE_INDEX	LIKE (pagination)	236	3512.2
PEOPLE_WITH_TEXT_INDEX	SUBSTR	1493	142.6
PEOPLE_WITH_TEXT_INDEX	LIKE	1433	221.6
PEOPLE_WITH_TEXT_INDEX	LIKE (force index)	-	-
PEOPLE_WITH_TEXT_INDEX	LIKE (only count)	-	-
PEOPLE_WITH_TEXT_INDEX	LIKE (pagination)	-	-
PEOPLE_WITH_TEXT_INDEX	MATCH	708	-
PEOPLE_WITH_TEXT_INDEX	tsquery (vector)	-	112.5

Disk space (table with indexes)

Table	MySQL, table Mb	PostgreSQL, table Mb	MySQL, index Mb	PostgreSQL, index Mb	MySQL, total Mb	PostgreSQL, total Mb
PEOPLE_WITHOUT_INDEX	260	276	0	0	260	276
PEOPLE_WITH_BTREE_INDEX	260	276	186	38	446	314
PEOPLE_WITH_TEXT_INDEX	306	373	105	27	411	401

Appendix

For PostgreSQL planner you can use the service which shows in details what the problem is.

There is also an alternative service.

In this example you can see:

# - order number
exclusive - execution time per operation
inclusive - execution time for all connected operation (Gather node requires execution of Seq Scan)
rows x - misses of planner (number of rows)

Performance Search Mysql Postgresql