Difference between a composite index and an "equivalent" expression index?

I need help understanding the difference between two kinds of indices:

• one that is applied on two columns

• and one that is applied on the concatenation of these two columns

It's best illustrated with an example of the tables I am dealing with:

create table potatos (
    potato_id integer not null,
    version_id integer not null,
    potato_name varchar not null,
    CONSTRAINT potatos_pkey PRIMARY KEY (potato_id, version_id)
);

Given this table, I should have a unique index because of the composite primary key.

Now consider this index:

create index potatos_composite_index on potatos ((potato_id || '|' || version_id));

With some sample data:

insert into potatos (potato_id, version_id, potato_name) 
SELECT generate_series(1,10000) AS potato_id, (generate_series(1,1000) * random())::int AS version_id, md5(random()::text) AS potato_name;

And these two queries with their results:

explain analyze
SELECT potatos.potato_id, potatos.version_id, potatos.potato_name 
FROM potatos
where
    (potatos.potato_id, potatos.version_id) IN ((1, 10), (2, 20), (3, 30), (4, 40), (5, 50), (6, 60), (7, 70), (8, 80), (9, 90), (10, 100), (11, 110), (12, 120), (13, 130), (14, 140), (15, 150));

Result:

```
QUERY PLAN |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
Bitmap Heap Scan on potatos (cost=64.43..68.51 rows=1 width=41) (actual time=0.020..0.020 rows=0 loops=1)

Here are the sizes for comparison, using \di+ potato*

Schema |          Name           | Type  |  Owner   |  Table  |  Size  | Description 
--------+-------------------------+-------+----------+---------+--------+-------------
 public | potatos_composite_index | index | ecarroll | potatos | 296 kB | 
 public | potatos_pkey            | index | ecarroll | potatos | 240 kB |

potatos_composite_index is

• badly ordered,

• a text type and comparison (rather than ints)

• larger

• badly named too: the pkey is technically composite and the "composite" one is technically not.

What you're calling composite has to scan all this stuff

'10|100', '11|110', '12|120', '13|130', '14|140', '15|150'

To scan '1|10' because it has to check that the second digit in the potato_id is not |.

Also keep in mind that the real composite indexes, like your pkey, are still only one index scan. The reason why it's doing a bitmap scan is so in one full scan of the index it can test all of those conditions. And why not, when the full index is 240kb.

Keep in mind here too that you're talking about a query that runs in 0.060 ms.

StackExchange Database Administrators Q#166849, answer score: 2