Fuzzy date search/Optimizing LIKE with date type

Question

Well, I will be pretty straightforward - I encountered with huge performance drop when implementing birthday search in my app - I'm using LIKE and this is not left-anchored expression (as date column format looks like YYYY-MM-DD), so i had to do (%-this month-this day), and this query can't use default index.

I found pg-trgm thing, which is actually pretty awesome, but gist/gin_trgm_ops operator doesn't support "date" type. You may say it makes no sense to have date as a column type if I use LIKE - i have to ::varchar it anyway, but I wish to set things strict in my database, so I'm asking for advice - is there any way to have LIKE work faster with date, or, if there is another options, way to finish my task (have a birthday search) with not that monstrous query time?

Answer 1

Try index like this:

create index idx_name on your_table(extract(month from birth_day), extract(day from birth_day));

and then query for example:

select * from your_table
where
extract(month from birth_day) = 2 and extract(day from birth_day) = 20