<html><body style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space; ">I probably can't help, but am interested in the optimisation of my own tables.<div><br></div><div>However, without knowing a bit more about your setup I doubt anyone else can either.<div><br></div><div>The output of </div><div><br></div><div><span class="Apple-tab-span" style="white-space:pre"> </span>select postgis_full_version();</div><div><br></div><div>is a good starting point.</div><div><br></div><div>Other details like EXPLAIN output, how big is the table, what other columns are there, is it in a separate tablespace, etc. are often useful.</div><div><br></div><div>Maybe submit that now so when other's look at it they have a bit more to work on.</div><div><br></div><div>cheers</div><div><br></div><div>Ben</div><div><br></div><div><br></div><div><div><div>On 28/08/2009, at 1:35 AM, Narayanan, Divya wrote:</div><br class="Apple-interchange-newline"><blockquote type="cite"><span class="Apple-style-span" style="border-collapse: separate; color: rgb(0, 0, 0); font-family: Helvetica; font-size: medium; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: 2; text-align: auto; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; -webkit-border-horizontal-spacing: 0px; -webkit-border-vertical-spacing: 0px; -webkit-text-decorations-in-effect: none; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px; "><div lang="EN-US" link="blue" vlink="purple"><div><div class="Section1"><div style="margin-top: 0in; margin-right: 0in; margin-bottom: 0.0001pt; margin-left: 0in; font-size: 12pt; font-family: 'Times New Roman'; "><span class="Apple-style-span" style="font-family: Arial; font-size: 13px; ">We were testing some queries to improve performance and found that the queries using PostGIS were slower than those that did not.</span></div><div style="margin-top: 0in; margin-right: 0in; margin-bottom: 0.0001pt; margin-left: 0in; font-size: 12pt; font-family: 'Times New Roman'; "><font size="2" face="Arial"><span style="font-size: 10pt; font-family: Arial; "><o:p> </o:p></span></font></div><div style="margin-top: 0in; margin-right: 0in; margin-bottom: 0.0001pt; margin-left: 0in; font-size: 12pt; font-family: 'Times New Roman'; "><font size="2" face="Arial"><span style="font-size: 10pt; font-family: Arial; ">I’ve included one of the queries tested<o:p></o:p></span></font></div><div style="margin-top: 0in; margin-right: 0in; margin-bottom: 0.0001pt; margin-left: 0in; font-size: 12pt; font-family: 'Times New Roman'; "><font size="2" face="Arial"><span style="font-size: 10pt; font-family: Arial; "><o:p> </o:p></span></font></div><div style="margin-top: 0in; margin-right: 0in; margin-bottom: 0.0001pt; margin-left: 0in; font-size: 12pt; font-family: 'Times New Roman'; "><b><font size="2" face="Arial"><span style="font-size: 10pt; font-family: Arial; font-weight: bold; ">Query with PostGIS<o:p></o:p></span></font></b></div><div style="margin-top: 0in; margin-right: 0in; margin-bottom: 0.0001pt; margin-left: 0in; font-size: 12pt; font-family: 'Times New Roman'; "><b><font size="2" face="Arial"><span style="font-size: 10pt; font-family: Arial; font-weight: bold; "><o:p> </o:p></span></font></b></div><div style="margin-top: 0in; margin-right: 0in; margin-bottom: 0.0001pt; margin-left: 0in; font-size: 12pt; font-family: 'Times New Roman'; "><font size="2" face="Arial"><span style="font-size: 10pt; font-family: Arial; ">SELECT ntb.id<o:p></o:p></span></font></div><div style="margin-top: 0in; margin-right: 0in; margin-bottom: 0.0001pt; margin-left: 0in; font-size: 12pt; font-family: 'Times New Roman'; "><font size="2" face="Arial"><span style="font-size: 10pt; font-family: Arial; ">FROM navteq as ntb<o:p></o:p></span></font></div><div style="margin-top: 0in; margin-right: 0in; margin-bottom: 0.0001pt; margin-left: 0in; font-size: 12pt; font-family: 'Times New Roman'; "><font size="2" face="Arial"><span style="font-size: 10pt; font-family: Arial; ">WHERE ( ntb.positioned_tsv @@ '(hotel|hodels|hoteles)'::tsquery)<o:p></o:p></span></font></div><div style="margin-top: 0in; margin-right: 0in; margin-bottom: 0.0001pt; margin-left: 0in; font-size: 12pt; font-family: 'Times New Roman'; "><font size="2" face="Arial"><span style="font-size: 10pt; font-family: Arial; ">AND (latitude>34.00846 AND latitude<34.09855 AND longitude>-118.29926 AND longitude<-118.1907)<o:p></o:p></span></font></div><div style="margin-top: 0in; margin-right: 0in; margin-bottom: 0.0001pt; margin-left: 0in; font-size: 12pt; font-family: 'Times New Roman'; "><font size="2" face="Arial"><span style="font-size: 10pt; font-family: Arial; ">LIMIT 250<o:p></o:p></span></font></div><div style="margin-top: 0in; margin-right: 0in; margin-bottom: 0.0001pt; margin-left: 0in; font-size: 12pt; font-family: 'Times New Roman'; "><font size="2" face="Arial"><span style="font-size: 10pt; font-family: Arial; "><o:p> </o:p></span></font></div><div style="margin-top: 0in; margin-right: 0in; margin-bottom: 0.0001pt; margin-left: 0in; font-size: 12pt; font-family: 'Times New Roman'; "><b><font size="2" face="Arial"><span style="font-size: 10pt; font-family: Arial; font-weight: bold; ">Query time: 1 second<o:p></o:p></span></font></b></div><div style="margin-top: 0in; margin-right: 0in; margin-bottom: 0.0001pt; margin-left: 0in; font-size: 12pt; font-family: 'Times New Roman'; "><b><font size="2" face="Arial"><span style="font-size: 10pt; font-family: Arial; font-weight: bold; "><o:p> </o:p></span></font></b></div><div style="margin-top: 0in; margin-right: 0in; margin-bottom: 0.0001pt; margin-left: 0in; font-size: 12pt; font-family: 'Times New Roman'; "><b><font size="2" face="Arial"><span style="font-size: 10pt; font-family: Arial; font-weight: bold; ">Query using postgis:<o:p></o:p></span></font></b></div><div style="margin-top: 0in; margin-right: 0in; margin-bottom: 0.0001pt; margin-left: 0in; font-size: 12pt; font-family: 'Times New Roman'; "><font size="2" face="Arial"><span style="font-size: 10pt; font-family: Arial; "><o:p> </o:p></span></font></div><div style="margin-top: 0in; margin-right: 0in; margin-bottom: 0.0001pt; margin-left: 0in; font-size: 12pt; font-family: 'Times New Roman'; "><font size="2" face="Arial"><span style="font-size: 10pt; font-family: Arial; ">SELECT ntb.id<o:p></o:p></span></font></div><div style="margin-top: 0in; margin-right: 0in; margin-bottom: 0.0001pt; margin-left: 0in; font-size: 12pt; font-family: 'Times New Roman'; "><font size="2" face="Arial"><span style="font-size: 10pt; font-family: Arial; ">FROM navteq_xtd_noothers as ntb<o:p></o:p></span></font></div><div style="margin-top: 0in; margin-right: 0in; margin-bottom: 0.0001pt; margin-left: 0in; font-size: 12pt; font-family: 'Times New Roman'; "><font size="2" face="Arial"><span style="font-size: 10pt; font-family: Arial; ">WHERE ( ntb.positioned_tsv @@ '(hotel|hodels|hoteles)'::tsquery)<o:p></o:p></span></font></div><div style="margin-top: 0in; margin-right: 0in; margin-bottom: 0.0001pt; margin-left: 0in; font-size: 12pt; font-family: 'Times New Roman'; "><font size="2" face="Arial"><span style="font-size: 10pt; font-family: Arial; ">AND geom && ST_SetSRID(ST_MakeBox2D(ST_Point(-118.29926, 34.00846),ST_Point(-118.1907, 34.09855)),4269)<o:p></o:p></span></font></div><div style="margin-top: 0in; margin-right: 0in; margin-bottom: 0.0001pt; margin-left: 0in; font-size: 12pt; font-family: 'Times New Roman'; "><font size="2" face="Arial"><span style="font-size: 10pt; font-family: Arial; ">LIMIT 250<o:p></o:p></span></font></div><div style="margin-top: 0in; margin-right: 0in; margin-bottom: 0.0001pt; margin-left: 0in; font-size: 12pt; font-family: 'Times New Roman'; "><font size="2" face="Arial"><span style="font-size: 10pt; font-family: Arial; "><o:p> </o:p></span></font></div><div style="margin-top: 0in; margin-right: 0in; margin-bottom: 0.0001pt; margin-left: 0in; font-size: 12pt; font-family: 'Times New Roman'; "><b><font size="2" face="Arial"><span style="font-size: 10pt; font-family: Arial; font-weight: bold; ">Query time: 4 seconds<o:p></o:p></span></font></b></div><div style="margin-top: 0in; margin-right: 0in; margin-bottom: 0.0001pt; margin-left: 0in; font-size: 12pt; font-family: 'Times New Roman'; "><font size="2" face="Arial"><span style="font-size: 10pt; font-family: Arial; "><o:p> </o:p></span></font></div><div style="margin-top: 0in; margin-right: 0in; margin-bottom: 0.0001pt; margin-left: 0in; font-size: 12pt; font-family: 'Times New Roman'; "><font size="2" face="Arial"><span style="font-size: 10pt; font-family: Arial; "><o:p> </o:p></span></font></div><div style="margin-top: 0in; margin-right: 0in; margin-bottom: 0.0001pt; margin-left: 0in; font-size: 12pt; font-family: 'Times New Roman'; "><font size="2" face="Arial"><span style="font-size: 10pt; font-family: Arial; ">The latitude and longitude columns are indexed. The geom column uses a GIST index. We’re using Postgres v 8.1. Is there something we’re doing wrong? Please let me know if you need more information.<o:p></o:p></span></font></div><div style="margin-top: 0in; margin-right: 0in; margin-bottom: 0.0001pt; margin-left: 0in; font-size: 12pt; font-family: 'Times New Roman'; "><font size="2" face="Arial"><span style="font-size: 10pt; font-family: Arial; "><o:p> </o:p></span></font></div><div style="margin-top: 0in; margin-right: 0in; margin-bottom: 0.0001pt; margin-left: 0in; font-size: 12pt; font-family: 'Times New Roman'; "><font size="2" face="Arial"><span style="font-size: 10pt; font-family: Arial; "><o:p> </o:p></span></font></div><div style="margin-top: 0in; margin-right: 0in; margin-bottom: 0.0001pt; margin-left: 0in; font-size: 12pt; font-family: 'Times New Roman'; "><font size="2" face="Arial"><span style="font-size: 10pt; font-family: Arial; ">Thanks,<o:p></o:p></span></font></div><div style="margin-top: 0in; margin-right: 0in; margin-bottom: 0.0001pt; margin-left: 0in; font-size: 12pt; font-family: 'Times New Roman'; "><font size="2" face="Arial"><span style="font-size: 10pt; font-family: Arial; ">Divya<o:p></o:p></span></font></div></div></div></div></span></blockquote></div><br><div apple-content-edited="true"> <span class="Apple-style-span" style="border-collapse: separate; border-spacing: 0px 0px; color: rgb(0, 0, 0); font-family: Helvetica; font-size: 12px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; text-align: auto; -khtml-text-decorations-in-effect: none; text-indent: 0px; -apple-text-size-adjust: auto; text-transform: none; orphans: 2; white-space: normal; widows: 2; word-spacing: 0px; "><div style="word-wrap: break-word; -khtml-nbsp-mode: space; -khtml-line-break: after-white-space; "><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">-- </div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 12px/normal Helvetica; min-height: 14px; "><br></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">Ben Madin</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">REMOTE INFORMATION</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 12px/normal Helvetica; min-height: 14px; "><br></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">t : +61 8 9192 5455</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">f : +61 8 9192 5535</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">m : 0448 887 220</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">Broome WA 6725</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 12px/normal Helvetica; min-height: 14px; "><br></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 12px/normal Helvetica; min-height: 14px; "><a href="mailto:ben@remoteinformation.com.au">ben@remoteinformation.com.au</a></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 12px/normal Helvetica; min-height: 14px; "><br></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 12px/normal Helvetica; min-height: 14px; "><br></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 12px/normal Helvetica; min-height: 14px; "><br></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "><span class="Apple-tab-span" style="white-space:pre"><span class="Apple-style-span" style="white-space: pre; "> </span></span>Out here, it pays to know...</div></div><br class="Apple-interchange-newline"></span> </div><br></div></div></body></html>