postgresql空间索引不走索引原因

- 日理万妓 2022-10-12 01:22 436阅读 0赞

一般建立GIST索引即可  
不走索引原因  
1，没有更新统计信息  
2，使用select子查询或者使用ST\_GeographyFromText t.geom :: geography  
3，数据类型和sql中类型不一致   （:: geography 类型转化作用）

强制索引方法：set enable\_seqscan = off;

create index i2 on t(lower(a::text));  创建函数索引---t.geom :: geography  
     
   以下基于数据库:geometry 类型，反之用geography类型  
     
set enable\_seqscan = off;  以下在这种才有效  
explain analyze   
SELECT  
    \*   
FROM  
    i t   
WHERE  
    ST\_DWithin ( ST\_GeomFromText ( 'SRID=4326;POINT(123.098 23.987)' ) , t.geom, '5000' )  
      
      
    use\_spheroid=false

explain analyze   
SELECT  
    \*   
FROM  
    i t   
WHERE  
    ST\_DWithin ( ST\_GeomFromText ( 'SRID=4326;POINT(123.098 23.987)' ) , t.geom, '5000' )  
      
      
      
    这种不走索引  
    explain analyze   
SELECT  
    \*   
FROM  
    i t   
WHERE  
    ST\_DWithin ( ST\_GeographyFromText ( 'SRID=4326;POINT(123.098 23.987)' ) , t.geom :: geography, '5000' )  
      
      
      
    加select也不走索引  
      
        explain analyze  SELECT  
    \*   
FROM  
    i t   
WHERE  
    ST\_DWithin ( (SELECT ST\_GeomFromText ( 'SRID=4326;POINT(123.098 23.987)' )) , t.geom, '1000')  
      
      
      
      
      
    下面这种需要走索引需要数据库类型是geography（这个类型索引快于geometry），不是geometry

用了索引会快5分之一

set enable\_seqscan = off;  
explain analyze  SELECT  
    \*   
FROM  
    i t   
WHERE  
    ST\_DWithin ( ST\_GeographyFromText ( 'SRID=4326;POINT(123.098 23.987)' ) , t.geom::geography, 1000)  
      
      
    ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3k2NjY2NjZ5_size_16_color_FFFFFF_t_70][]

减少字段的返回会快5分之一

set enable\_seqscan = off;  
explain analyze  SELECT  
     id, region\_code, region\_name, issue\_id, tag  
FROM  
    issue\_record t   
WHERE  
    ST\_DWithin ( ST\_GeographyFromText ( 'SRID=4326;POINT(113.678425 34.796591666)' ) , t.geom::geography, 5000)

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3k2NjY2NjZ5_size_16_color_FFFFFF_t_70]: /images/20221005/e6ee06981da24fa6891113f3ff8c5fa7.png