Re: Suggestion to improve query performance for GIS query.

Dear team,

Kindly try to execute the vacuum analyzer on that particular table and
refresh the session and execute the query.

VACUUM (VERBOSE, ANALYZE) tablename;

Regards,
Mohammed Afsar
Database engineer

On Fri, May 22, 2020, 12:30 PM postgann2020 s <postgann2020(at)gmail(dot)com>
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.
>