我正在构建一个应用程序，服务器需要根据某些条件/过滤器选择行。其中之一是用户的位置以及用户希望查看帖子和其他过滤器(例如日期范围(并筛选另一列的值的半径。这将适用于临时事件发现应用程序。
我读过PostGIS的文章，我知道有一种point数据类型。基于这个答案，我明白最好从相等到范围列排序，即使我觉得地理点列应该是第一个。但主要问题是，如何创建这样的索引？ 我想过 GiST 索引，但不确定是否有帮助。
假设以下简化事件表(忽略有效性位置数据(：
id event_title event_position event_type is_public start_date
(varchar) (point lat/lon) (smallint) (boolean) (timestamptz)
-- --------------------------- --------------- --------- --------- ----
1 "John's Party" (122,35) 0 0 2020-07-05
2 "Revolution then Starbucks" (123,30) 1 1 2020-07-06
3 "Study for math exam" (120,36) 2 1 2020-07-07
4 "Party after exam" (120,36) 1 1 2020-07-08
5 "Hiking next to the city" (95,40) 3 1 2020-07-09
6 "Football match" (-42,31) 4 1 2020-07-10
因此，在此表中，用户将能够查询接近 (122,34( 100 公里的公共事件(假设前三行属于此区域(以及介于日期 2020-07-05 和 2020-07-07 之间的事件类型 0、1 或 2。用户将获得 ID 为 2 和 3 的行。
这是我想要使用适当的索引进行优化的查询。谢谢！
借助btree_gist扩展，您可以将event_type和start_date列与event_position一起包含在 GiST 索引中。 但是，只要限制子句类似于event_type in (0, 1, 2)，event_type在索引中就不会很有用。 (但是，如果列表只包含一个元素，它将被重写为相等，在这种情况下，它可以有效地在索引中使用该列。 因此，使用其他两列将是我的起点。 我会把通常更具选择性的放在第一位。 如果您打算将过去的事件留在表中而不是清除它们，那么日期最终可能会更具选择性。
如果位置条件根本不是选择性的(例如，您的大多数活动都在纽约，您的大多数用户都在纽约市，几乎所有东西都在几乎所有其他东西的 100 公里范围内(，那么您可能需要一种不同的方法。 只需在(event_type, start_date)上创建一个默认的 BTREE 索引即可。 与 GiST 不同，这样的 BTREE 可以有效地使用event_type in (0, 1, 2)等条件以及AND start_date between x and y。
我不认为 GiST 不能有效地使用列表内而 BTREE 可以的根本原因。 也许这将在未来的某个版本中为 GiST 修复。