1

I have an Ecto query that finds rows with a column in a set of values. Something like this (using a fictitious blog domain):

Post
|> where(state in ^["published", "draft"])

This generates a clause like this:

p0.state = ANY($1)

With ["published", "draft"] as parameters.

The database is Postgres 9.6. Running explain shows the cost of this query is much higher (by many orders of magnitude) than what is supposed to be the equivalent clause:

p0.state in ($1)

I do have an index on state.

I don't know if this due to a performance problem in Postgres itself, but I've found some hints of that.

I'd like to either be able to work around the performance issue or influence Ecto to generate p0.state in ($1) instead.

Any advice?

objectuser
  • 671
  • 6
  • 15
  • In the question you linked to, the first answer says "In fact, its second form is rewritten internally: IN is rewritten with = ANY". Where did you read that `= ANY` is "many orders of magnitude" slower than `IN`? – Dogbert Jun 22 '18 at 02:01
  • I didn't read it, that's the result of running `explain` on both versions on my DB. – objectuser Jun 22 '18 at 02:05

0 Answers0