Re: Suggestion to improve query performance for GIS query.
postgann2020 s <[email protected]> Fri, 22 May 2020 13:04:21 +0530
| Newsgroups | gmane.comp.gis.postgis,gmane.comp.db.postgresql.admin,gmane.comp.db.postgresql.performance |
|---|---|
| Message-ID | <CANynezPVwipwro1y-MAWg8UnLr1iu4XufjXynq-Uhatg1B5CiQ@mail.gmail.com> |
--===============5174355951705327531== Content-Type: multipart/alternative; boundary="000000000000b3ba8405a637aad9" --000000000000b3ba8405a637aad9 Content-Type: text/plain; charset="UTF-8" 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 <[email protected]> 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 <[email protected]> > 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. >> > --000000000000b3ba8405a637aad9 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable <div dir=3D"ltr">Thanks for your support David and Afsar.<div><br></div><di= v>Hi David,</div><div><br></div><div>Could you please suggest the resource = link to=C2=A0 "<font color=3D"#ff00ff">Add a trigger to the table to n= ormalize the contents of column1 upon insert and then rewrite your query to= reference the newly created normalized fields</font>." if anything av= ailable. So that it will help me to get into issues.</div><div><br></div><d= iv>Thanks for your support.</div><div><br></div><div>Regards,</div><div>Pos= tgann.</div><div><br></div></div><br><div class=3D"gmail_quote"><div dir=3D= "ltr" class=3D"gmail_attr">On Fri, May 22, 2020 at 12:46 PM Mohammed Afsar = <<a href=3D"mailto:[email protected]">[email protected]</a>> wrote:= <br></div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8= ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"aut= o">Dear team,<div dir=3D"auto"><br></div><div dir=3D"auto">Kindly try to ex= ecute the vacuum analyzer on that particular table and refresh the session = and execute the query.</div><div dir=3D"auto"><br></div><div dir=3D"auto">V= ACUUM (VERBOSE, ANALYZE) tablename;</div><div dir=3D"auto"><br></div><div d= ir=3D"auto">Regards,</div><div dir=3D"auto">Mohammed Afsar</div><div dir=3D= "auto">Database engineer</div></div><br><div class=3D"gmail_quote"><div dir= =3D"ltr" class=3D"gmail_attr">On Fri, May 22, 2020, 12:30 PM postgann2020 s= <<a href=3D"mailto:[email protected]" target=3D"_blank">postgann20= [email protected]</a>> wrote:<br></div><blockquote class=3D"gmail_quote" styl= e=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);paddin= g-left:1ex"><div dir=3D"ltr">Hi Team,<br><br>Thanks for your support.<br><b= r>Could you please suggest on below query.<br><br>EnvironmentPostgreSQL: 9.= 5.15<br>Postgis: 2.2.7<br><br>The table contains GIS data which is fiber da= ta(underground routes).<br><br>We are using the below query inside the proc= which is taking a long time to complete.<div><br></div><div>**************= ***********************************************<br><br>SELECT seq_no+1 INTO= pair_seq_no FROM SCHEMA.TABLE WHERE (Column1 like '%,sheath--'||ca= ble_seq_id ||',%' or Column1 like 'sheath--'||cable_seq_id = ||',%' or Column1 like '%,sheath--'||cable_seq_id =C2=A0or = Column1=3D'sheath--'||cable_seq_id) order by seq_no desc limit 1 ;<= br><br>****************************************************************<br>= <br>We have created an index on parental_path Column1 still it is taking 4s= ecs to get the results.<br><br>Could you please suggest a better way to exe= cute the query.<br><br>Thanks for your support.<br><br>Regards,<br>PostgAnn= .<br></div></div> </blockquote></div> </blockquote></div> --000000000000b3ba8405a637aad9-- --===============5174355951705327531== Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: base64 Content-Disposition: inline X19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX18KcG9zdGdpcy11 c2VycyBtYWlsaW5nIGxpc3QKcG9zdGdpcy11c2Vyc0BsaXN0cy5vc2dlby5vcmcKaHR0cHM6Ly9s aXN0cy5vc2dlby5vcmcvbWFpbG1hbi9saXN0aW5mby9wb3N0Z2lzLXVzZXJz --===============5174355951705327531==--