Postgres and Ruby's Sequel: Empty array in where-clause getting converted to slow query

Question

I'm using the excellent Sequel ORM, and at some point I've generated an array of IDs and I'm trying to retrieve the users that have those IDs. e.g.

user_ids = [1, 2, 3]
User.where(id: user_ids).all
# (0.004223s) SELECT * FROM "users" WHERE ("id" IN (1, 2, 3))

But sometimes, the list of users is empty, and what I effectively get is this:

User.where(id: []).all
# (0.421287s) SELECT * FROM "users" WHERE ("id" != "id")

The result is correct (i.e. no rows returned), but the query is two orders of magnitude slower.

On tables with millions of rows, it can take a couple of seconds to return.

I'm curious why this query is generated in the first place, but I'm also curious why the Postgres query planner doesn't seem to detect this contradiction and return an empty dataset instantly.

Is there a neat solution to this, or will I have to check all arrays for emptiness?

I'm using Ruby 2.0.0, Sequel 4.24.0 & Postgres 9.3.6.

@GaganGami I do if I want to execute the query and see the time it took to return — aidan
– aidan, Commented Nov 30, 2015 at 12:35

spickermann · Accepted Answer · 2015-11-30 07:31:05Z

2

There is a closed issue on github about this behavior.

I agree with jeremyevans that there is no need for a fix, because it is obvious that the reseult will and should always be empty. Furthermore you would agrue that PostgreSQL should be clever enough to optimize queries like that and should not do a whole table scan.

Therefore it makes way more sense IMHO to fix this behavior in the code to avoid to call the database completely:

user_ids.present? ? User.where(id: user_ids) : []

edited Nov 30, 2015 at 7:31

answered Nov 30, 2015 at 7:25

spickermann

108k9 gold badges115 silver badges147 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

aidan Over a year ago

Thanks for the link. It also explains why just false isn't used instead of id != id. (Although I can't think of a scenario when it would occur)

delphaber Over a year ago

with recent versions of rails you can write: user_ids.present? ? User.where(id: user_ids) : User.none in this way you return an active record relation everytime

Jeremy Evans · Accepted Answer · 2015-11-30 16:12:47Z

1

Note that Sequel's default behavior was changed in 4.25.0 so that such queries will use false instead of id != id. While id != id may be more correct in terms of NULL handling, the performance benefits of false make it a better default.

answered Nov 30, 2015 at 16:12

Jeremy Evans

12.2k32 silver badges28 bronze badges

1 Comment

aidan Over a year ago

Thanks Jeremy. Just updated to 4.28.0 - didn't realise I was so far behind. In what scenario does using 1 = 0 fail?

Collectives™ on Stack Overflow

Postgres and Ruby's Sequel: Empty array in where-clause getting converted to slow query

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related