[Python] SQLAlchemy composite index with LOWER

I need to create composite indexes on the last_name and first_name columns of a large table called people

I need to search with a single field provided, called name, that can match either the first_name or the last_name.

In the end I will have to use SQLite, so the ILIKE operator I cannot use.

The match must be case insensitive, so the query is

SELECT *
FROM people AS p
WHERE
LOWER(p.last_name || p.first_name) LIKE :name
OR
LOWER(p.first_name || p.last_name) LIKE :name


with, for example, the name parameter = `"fra%"

My model is

import sqlalchemy as sa
from sqlalchemy.orm import Mapped, mapped_column

class People(DeclarativeBase):
__tablename__ = "people"

id: Mapped[str] = mapped_column(primary_key=True)
first_name: Mapped[str] = mapped_column(nullable=False, unique=False)
last_name: Mapped[str] = mapped_column(nullable=False, unique=False)

__table_args__ = (
sa.Index(
"ix_people_last_first_name",
sa.text("LOWER(last_name || first_name)"),
),
sa.Index(
"ix_people_first_last_name",
sa.text("LOWER(first_name || last_name)"),
),
)


When I generate the migration with Alembic, I get

INFO [alembic.autogenerate.compare] Detected added table 'people'
INFO [alembic.autogenerate.compare] Detected added index ''ix_people_first_last_name'' on '()'
INFO [alembic.autogenerate.compare] Detected added index ''ix_people_last_first_name'' on '()'


As you can see, the indexes are not applied to a column(s). They do get created though:

op.create_index('ix_people_first_last_name', 'people', [sa.literal_column('LOWER(first_name || last_name)')], unique=False)
op.create_index('ix_people_last_first_name', 'people', [sa.literal_column('LOWER(last_name || first_name)')], unique=False)


EXPLAIN gives the following query plan:

"Seq Scan on people p (cost=0.00..4.50 rows=3 width=128)"
" Filter: ((lower(((last_name)::text || (first_name)::text)) ~~ 'fra%'::text) OR (lower(((first_name)::text || (last_name)::text)) ~~ 'fra%'::text))"


which shows the indexes are not used.

SQLAlchemy says here it is possible to use lower on the index, but it shows a simple case, not a composite one.

It also says:

the text() construct may be used to specify Index expressions, provided the Index is explicitly associated with the Table.

How can I achieve it?

Any suggestions or other options are welcome.

Note: I have tried using a fulltext extension, i.e. fts5, but it doesn't work and I have no idea why (the virtual table gets created and the query executes without errors, but I get no results).

Many thanks

Continue reading...