Let's say I have a table with 1 000 000 records. Structure of table is:
create table individual ( id serial primary key, surname varchar(128), "name" varchar(128), patronymic varchar(128), birth_dt date )
I create a composite index.INDEX 1
create index on individual using btree (upper(surname) varchar_pattern_ops , upper("name") varchar_pattern_ops, patronymic varchar_pattern_ops, birth_dt);
Docs state that
varchar_pattern_ops should be applied when using
LIKE or pattern match in query. Conclusion: this index will not be used in query below, even it gets only 10 row from 1 000 000.
select * from individual order by upper(surname), upper("name"), upper(patronymic), birth_dt limit 10;
and even more, docs recommends to create an index without
varchar_pattern_ops as well.
create index on individual using btree (upper(surname), upper("name"), upper(patronimyc), birth_dt);
Then a query using
LIMIT will use this index.
I found a cheat to force Postgres to use first index on Postgres users forum. It is operator
select * from individual order by upper(surname) using ~<~ , upper("name") using ~<~ , upper(patronymic) using ~<~ , birth_dt limit 100;
In this case INDEX 1 will be used even if INDEX 2 doesn't exists. I tried to investigate to discover why it happens, but failed.
There are some system tables like
pg_operator, which (I think) link operator
~<~ to some functions that most probably uses
LIKE or regular expressions.
I ran QUERY 1 and QUERY 2 a few times and compared result manually. It looks like operator
~<~ gives correct result, but I didn't risk anything and just create a normal index anyway.
I am still interested how the Postgres planner decides which index to use index where it meets the operator
~<~ in QUERY 1.