NoBrainer::Error::CannotUseIndex on compound index with a range

[ajselvig]

Maybe this isn't possible with rethink, but I'm unable to use a compound index when one of the query parameters is using a range modifier (.gt, .lt, etc):

class Foo
  include NoBrainer::Document
  field :name, type: String
  field :time, type: Time
  index [:time, :name]
end

NoBrainer.sync_indexes

# works fine
Foo.where(name: 'foo', time: Time.now).with_index(:time_name).count

# raises NoBrainer::Error::CannotUseIndex
Foo.where(name: 'foo', :time.lt => Time.now).with_index(:time_name).count

[nviennot]

Hum... I'm not sure what would be the RethinkDB query that should be used in this case. Any idea?

[ajselvig]

It seems like it should be possible based on the docs, but I'm having trouble getting it to work:

%w(foo bar).each do |name|
  t = Time.now - 1.day
  while t < Time.now+1.day
    Foo.create! name: name, time: t
    t += 1.hour
  end
end

puts Foo.where(name: 'foo', :time.lt => Time.now).count
# => 25 (correct)

c = NoBrainer.run do |r|
  r.table('foos').between(
      [r.minval, 'foo'], [Time.now, 'foo'], index: 'time_name'
  ).count
end
puts c
# => 50

It seems to be ignoring the name condition for some reason. I'll keep digging.

[ajselvig]

It looks like it works if the index order is flipped around:

class Foo
  include NoBrainer::Document
  field :name, type: String
  field :time, type: Time
  index [:name, :time]
end

The result is then correct:

c = NoBrainer.run do |r|
  r.table('foos').between(
      ['foo', r.minval], ['foo', Time.now], index: 'name_time'
  ).count
end
puts c
# => 25

The explanation is here: https://rethinkdb.com/api/ruby/between/ in the "lexicographical order" part, although it's not entirely clear why it behaves this way.

This functionality would provide a pretty big performance boost since we have tables containing lots of records that are almost always queried by both a time range and a single foreign key value. Does it seem like something reasonable to support?

[nviennot]

That's reasonable. It might take me a bit of time to implement this as I have a lot of my plate these days.

[ajselvig]

Yeah, no problem. Thanks.