Rails 4/postgresql 索引 - 我应该使用可以具有无限数量值的日期时间列作为索引过滤器吗? [英] Rails 4/ postgresql index - Should I use as index filter a datetime column which can have infinite number of values?

问题描述

我需要优化一个查询，在特定日期时间和特定时间之前获取特定国家/地区的所有交易.

I need to optimize a query fetching all the deals in a certain country before with access by users before a certain datetime a certain time.

我的计划是实现以下索引

My plan is to implement the following index

add_index(:deals, [:country_id, :last_user_access_datetime])

add_index(:deals, [:country_id, :last_user_access_datetime])

我怀疑此索引的相关性和效率，因为 last_user_access_datetime 列可以具有任何日期值，例如:13/09/2015 3:06pm，并且它会经常更改(每次用户访问它时都会更新).如果我使用这个索引，那会使无限数量的值被索引?

I am doubting the relevance and efficientness of this index as the column last_user_access_datetime can have ANY value of date ex: 13/09/2015 3:06pm and it will change very often (updated each time a user access it). That makes an infinite number of values to be indexed if I use this index?

我应该这样做还是避免使用无限可能的列"，例如索引内的完全免费的日期时间列?

Should I do it or avoid using 'infinite vlaues possible column such as a totally free datetime column inside an index ?

推荐答案

如果您有这样的查询:

select t.
from table t
where t.country_id = :country_id and t.last_user_access_datetime >= :some_datetime;

那么最好的索引就是您提出的索引.

Then the best index is the one you propose.

如果您的机器在访问方面的负载很重(并且认为每秒有很多访问)，那么维护索引可能会成为机器的负担.当然，无论如何您都在更新上次访问日期时间值，因此您已经产生了开销.

If you have a heavy load on the machine in terms of accesses (and think many accesses per second), then maintaining the index can become a burden on the machine. Of course, you are updating the last access date time value anyway, so you are already incurring overhead.

可能值的数量对值没有影响.数据库无法存储无限"数量的值(至少在当前可用的任何硬件上)，因此我不确定您的担忧是什么.

The number of possible values does not have an effect on the value. A database cannot store an "infinite" number of values (at least on any hardware currently available), so I'm not sure what your concern is.

这篇关于Rails 4/postgresql 索引 - 我应该使用可以具有无限数量值的日期时间列作为索引过滤器吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！