postgresql - Postgres 9.4 哪种类型的索引最适合浮点列

Question

我以前在 mysql，现在加入了 postgres，我有一个表，每天有多达 300,000 条新记录，但也有很多读取。我有 2 个我认为最适合索引的列:纬度和经度，我知道 postgres 有不同类型的索引，我的问题是哪种类型的索引最适合有很多写入和读取的表？这是读取的查询

SELECT p.fullname,s.post,to_char(s.created_on, 'MON DD,YYYY'),last_reply,s.id,
r.my_id,s.comments,s.city,s.state,p.reputation,s.profile_id 
FROM profiles as p INNER JOIN streams as s ON (s.profile_id=p.id) Left JOIN 
reputation as r ON (r.stream_id=s.id and r.my_id=?) where s.latitudes >=? 
AND ?>= s.latitudes AND s.longitudes>=? AND ?>=s.longitudes order by 
s.last_reply desc limit ?"

如您所见，where 子句中的两列是纬度和经度

Answer 1

PostgreSQL 有 point data type有很多operators gist index 的良好支持.因此，如果可能的话，请更改您的表定义以使用 point 而不是 2 个 float。

插入点数据非常简单，只需对列使用point(longitudes, latitudes)，而不是将两个值放在不同的列中。与获取数据相同:lnglat[0] 是经度，lnglat[1] 是纬度。

索引应该是这样的:

CREATE INDEX idx_mytable_lnglat ON streams USING gist (lnglat pointops);

还有 box data type ，这对于对所有参数进行分组并在 gist 索引中高度优化的框中查找点非常有用。

在表中有一个点和一个要搜索的框，您的查询将简化为:

SELECT p.fullname, s.post, to_char(s.created_on, 'MON DD,YYYY'), last_reply, s.id,
       r.my_id, s.comments, s.city, s.state, p.reputation, s.profile_id 
FROM profiles AS p
JOIN streams AS s ON (s.profile_id = p.id)
LEFT JOIN reputation AS r ON r.stream_id = s.id AND r.my_id = ?
WHERE s.lnglat && box(?, ?, ?, ?)
ORDER BY s.last_reply DESC
LIMIT ?;

短语 s.lnglat && box(?, ?, ?, ?) 表示“lnglat 列的值与框重叠(意思是:在框内)”。