V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
liuguangxuan
V2EX  ›  PostgreSQL

请教各位老哥, PostgreSQL、PostGIS 基于地理空间的查询如何优化速度?

  •  1
     
  •   liuguangxuan · 10 天前 · 1177 次点击

    场景:

    可以理解为:记录飞机飞行的轨迹,把经、纬度点,高度、速度、航向存入PostgreSQL数据库,并在经纬度列建立 gist 索引。

    数据量大概在 1 亿条左右。

    想实现查询指定区域范围(圆形、矩形、多边形)内的轨迹。

    测试:

    随机在经度(-180°,180°),纬度(-90°,90°)的范围内生成 1 亿个坐标点,保留小数点后 5 位小数,并存入数据库,测试在指定的范围内的查询速度。

    查询矩形区域32°*32°的范围,查询出来的记录数约为 150 万条,用时 15 分钟左右。

    问题:

    1. 想请问各位老哥,如何把查询的时间给优化下去,现在耗时 15 分钟有点儿太长了。
    2. 如果不关注实时的点,只关注整体的轨迹线,如何把轨迹线抽取出来?做压缩?这样查询速度会不会快一些。
    3. 如果抽取轨迹线的话,如何保留速度、航向、高度等特征值。想以后做分析用,比如突然转向、突然减速、突然高度骤降等。
    第 1 条附言  ·  10 天前
    1. 经纬度列的类型为geometry(Point),在该列上建立的gist索引。
    2. 建立索引语句为create index idx_gistable_jwd on gistable using gist(jwd);,而且用\d gistable查看表的描述的时候,能看到该索引。
    3. 查询语句为select count(*) from gistable as t where st_contains('POLYGON((0 0, 32 0, 32 32, 0 32, 0 0))', t.jwd);
    4. 检索速度慢一方面和我使用的测试环境有关系,我是用的是腾讯云vps,2核8G内存,PostgreSQL的配置保持安装时的设置。
    14 条回复    2022-05-17 15:39:17 +08:00
    nuistzhou
        1
    nuistzhou  
       10 天前 via iPhone
    你的 Gist 是不是有问题呀?一亿条数据也不应该这么久啊。另外,单看返回条数呢?是不是大部分时间花在返回数据本身上了?
    beginor
        2
    beginor  
       10 天前 via Android
    经纬度保存成空间数据类型 Geometry 然后再加索引试试,这样可以用上空间索引
    liuguangxuan
        3
    liuguangxuan  
    OP
       10 天前
    @nuistzhou #1
    @beginor #2
    老哥,经纬度索引列,应该是没问题,类型为 geometry(Point),我用\d tablename ,能查到索引。只是建立索引的时候没有添加编码 4326 ,请问这个影响大吗?

    另外可能和我测试环境的配置有关系,我用的是腾讯云 2 核 8G 内存,PostgreSQL 的配置文件保持默认的设置。

    提高服务器硬件资源配置是一方面,老哥可否指点一下其它提高查询性能的方法。
    liuguangxuan
        4
    liuguangxuan  
    OP
       10 天前
    @nuistzhou #1 如果单看返回条数的话,使用 select count(*),时间也差不太多。
    iseki
        5
    iseki  
       10 天前   ❤️ 1
    explain (analyze on, timing on)看看慢在哪呗
    beginor
        6
    beginor  
       10 天前 via Android   ❤️ 1
    @liuguangxuan 坐标字段声明坐标系,空间数据类型建议使用 SP-GiST 索引类型, 查询时的空间参数也使用相同的坐标系, 空间函数 st_contains 可以改为 st_intersect 或者 && 算符
    a90120411
        7
    a90120411  
       10 天前
    别查 Point ,查 Line 。
    用点来生成线,在线对象数据中同时保存与点集合的业务数据关联。
    wd
        8
    wd  
       10 天前 via iPhone
    st_contains 走索引吗?好像不走?
    v2eb
        9
    v2eb  
       10 天前 via Android
    分析突然减速的这种场景,好像和坐标没有必然的联系吧,还是没有速度这个数据条目?
    nuistzhou
        10
    nuistzhou  
       10 天前 via iPhone
    1. explain 看看吧,前面的老哥提到了,看看是不是 hit 太多了
    2. 试试 st_geohash 吧,可以把点聚集起来,然后建个空间索引,hit 应该会降低不少
    3. 试试 @ 这个 operator
    liuguangxuan
        11
    liuguangxuan  
    OP
       8 天前
    @beginor #6 老哥,我大概按你的方法测试了一下,分别测试了 st_contains 、st_intersects 、&&在不同索引( gist,sp-gist )下的查询情况,并且换了一台服务器。每次测试均重启了服务器。

    总体而言,gist 索引性能好于 sp-gist 索引,首次查询 st_intersects 性能比较好,第二次查询&&性能比较好。

    测试结果和老哥说的有点儿出入,能不能帮忙解答一下原因,还是我测试的方式不太对?

    beginor
        12
    beginor  
       7 天前 via Android
    SP-GiST 是带分区的 GiST ,至于谁比谁更好,要看具体场景和数据类型,实际上也差不了多少。
    dzdh
        13
    dzdh  
       7 天前
    完整 sql 方便贴一下吗

    explain(timing, analyze, buffers) 或者是 explain 结果
    liuguangxuan
        14
    liuguangxuan  
    OP
       7 天前
    换了一台性能比较好的服务器,再加上二次查询的原因,可能内存中有缓存,所以现在比较快,大约 64°*64°的区域,在 6~7 秒左右,老哥还有没有优化的方法?

    关于   ·   帮助文档   ·   API   ·   FAQ   ·   我们的愿景   ·   广告投放   ·   感谢   ·   实用小工具   ·   1161 人在线   最高记录 5497   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 24ms · UTC 19:12 · PVG 03:12 · LAX 12:12 · JFK 15:12
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.