The core takeaway here, which is not limited to Elixir or Ecto, is that Postgres tends to use a common query plan for all executions of the same prepared statement, which can cause performance issues if optimal query plans vary with the arguments. Good to know, a non-obvious gotcha!
There isn’t much talk about Elixir here, more about Ecto, and that is still just on one instance. So the problem is related to any app that is using Postgres for data storage.
I run a single person Pleroma instance and have noticed that tag searches seemed to take a long time so it’s good to know there is a way to speed them up. Thanks for the write up of your findings @lain.
I am interested how it would work on normalised data.
This post is hard for me to follow. Tags are not a full text search problem. And unless you are offering a completely open ended tag model, this should be fast for even a Reddit number of stories.
This is an open ended tag model, and you’re right, it’s not full text search. My previous blog post was about full text search. This one is actually more about prepared statements.
Yeah, I think the title buries the lede. I’m not interested in Elixir, but the post was actually a great read about Postgres.
While I can’t speak to the design of the solution or Pleroma’s architecture (I’ve never implemented tag search or anything quite like it), I thought it was an interesting look at Postgres planned vs. unplanned queries 🙂