0

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?

Alfabravo
  • 7,493
  • 6
  • 46
  • 82
Pavel Nasevich
  • 400
  • 1
  • 3
  • 13

1 Answers1

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
Oto Shavadze
  • 40,603
  • 55
  • 152
  • 236