[postgis-users] Suggestion to improve query performance for GIS query.

[Stephen Woodbridge]

This query will most likely do a full table because the where cause 
uses  like '%...'. Because the leading % is a wildcard, it has to do a 
full table scan and can not use an index.

-Steve W

On 5/22/2020 2:59 AM, postgann2020 s wrote:
> Hi Team,
>
> Thanks for your support.
>
> Could you please suggest on below query.
>
> EnvironmentPostgreSQL: 9.5.15
> Postgis: 2.2.7
>
> The table contains GIS data which is fiber data(underground routes).
>
> We are using the below query inside the proc which is taking a long 
> time to complete.
>
> *************************************************************
>
> SELECT seq_no+1 INTO pair_seq_no FROM SCHEMA.TABLE WHERE (Column1 like 
> '%,sheath--'||cable_seq_id ||',%' or Column1 like 
> 'sheath--'||cable_seq_id ||',%' or Column1 like 
> '%,sheath--'||cable_seq_id  or Column1='sheath--'||cable_seq_id) order 
> by seq_no desc limit 1 ;
>
> ****************************************************************
>
> We have created an index on parental_path Column1 still it is taking 
> 4secs to get the results.
>
> Could you please suggest a better way to execute the query.
>
> Thanks for your support.
>
> Regards,
> PostgAnn.
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users