Suggestion to improve query performance for GIS query.

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Suggestion to improve query performance for GIS query.

postgann2020 s
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.
Reply | Threaded
Open this post in threaded view
|

Re: Suggestion to improve query performance for GIS query.

David G Johnston
On Thursday, May 21, 2020, postgann2020 s <[hidden email]> wrote:

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 ;


Could you please suggest a better way to execute the query

Add a trigger to the table to normalize the contents of column1 upon insert and then rewrite your query to reference the newly created normalized fields.

David J.

Reply | Threaded
Open this post in threaded view
|

Re: Suggestion to improve query performance for GIS query.

Mohammed Afsar
In reply to this post by postgann2020 s
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 <[hidden email]> 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.
Reply | Threaded
Open this post in threaded view
|

Re: Suggestion to improve query performance for GIS query.

postgann2020 s
Thanks for your support David and Afsar.

Hi David,

Could you please suggest the resource link to  "Add a trigger to the table to normalize the contents of column1 upon insert and then rewrite your query to reference the newly created normalized fields." if anything available. So that it will help me to get into issues.

Thanks for your support.

Regards,
Postgann.


On Fri, May 22, 2020 at 12:46 PM Mohammed Afsar <[hidden email]> wrote:
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 <[hidden email]> 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.