Why is Postgres BETWEEN so slow?
We recently started using PGanalyze, which is amazing. Today I spent some time trying to speed up some of the slowest queries in Tanda. The very slowest had an average run time of 266ms, and was being called hundreds of thousands of times per day.
It turns out, this query was running incredibly slowly because an index wasn’t being hit. The query looked like this:
AND (“shifts”.”date” BETWEEN ‘2017–12–26’ AND ‘2017–12–27’)
Earlier on the day, I had, on a whim, changed another slow query from this:
AND (“shifts”.”date” BETWEEN ‘2017–12–26’ AND ‘2017–12–26’)
To this:
AND (“shifts”.”date” = ‘2017–12–26’)
I was a bit taken aback to find that removing the BETWEEN operator caused Postgres to use an index on the date column. Previously, it had been hitting another (less specific) index and then doing a table scan. As a result this query ended up being about 20x faster.
So with this knowledge in mind, I tried changing to the original query to this:
AND (“shifts”.”date” IN (‘2017–12–27’, ‘2017–12–26’))
Just as before, this resulted in a different index being used, and an 80x speedup. Not too shabby.
So why was I using the really slow BETWEEN query in the first place? We use ActiveRecord, and it turns out ActiveRecord loves BETWEENs. In this case, our code looked like this:
It seems that when AR sees a range, it uses BETWEEN. Fair enough, that usually makes sense. In this case, because I am using two concurrent dates, and I know they will always be the only two items in the “list” of values, I just used an array instead:
Much faster. See if you can guess where I deployed the change:
Lesson learned: if you have a slow query, check if it is (and if needs to be) using BETWEEN.
ps. sorry for not answering the question in the title of the post. If anyone actually knows why BETWEEN isn’t smart enough to act like IN, I’d love to hear it.
Update: some theories as to my question from this Facebook thread.
