Re: Suggestion to improve query performance for GIS query. - Mailing list pgsql-performance

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@gmail.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.