<html><head><style type="text/css"><!-- DIV {margin:0px;} --></style></head><body><div style="font-family:arial,helvetica,sans-serif;font-size:10pt">Your welcome, Matt.<br><br>Now it is clear why the query is slow.<br><br>You must have indices on the columns that you're searching in (and run VACUUM frecuently to keep them <br>updated). If you're using LIKE operator, then the indices are not useful if the columns are not LOCALEd.<br><br>Check if you have defined LOCALE to speed up the LIKE matching.<br><br>Also, if the data doesn't change at all, you can CLUSTER the table to improve the query speed.<br><br><div style="font-weight: bold; color: rgb(0, 96, 191);">IC Carlos Ruiz</div><div style="font-family: arial,helvetica,sans-serif; font-size: 10pt;"><br><div style="font-family: times new roman,new york,times,serif; font-size: 12pt;"><font face="Tahoma" size="2"><hr size="1"><b><span style="font-weight: bold;">From:</span></b> Matt Mendick
<matt.mendick@pictometry.com><br><b><span style="font-weight: bold;">To:</span></b> Carlos Ruiz <boolean10001@yahoo.com>; "mapserver-users@lists.osgeo.org" <mapserver-users@lists.osgeo.org><br><b><span style="font-weight: bold;">Sent:</span></b> Tue, December 21, 2010 10:43:46 AM<br><b><span style="font-weight: bold;">Subject:</span></b> RE: [mapserver-users] long-running postgres queries initiated by mapserver<br></font><br>
<style type="text/css">DIV {
MARGIN:0px;}
</style>
<div dir="ltr" align="left"><span class="053004116-21122010"><font color="#0000ff" face="Arial" size="2">Carlos,</font></span></div>
<div dir="ltr" align="left"><span class="053004116-21122010"><font color="#0000ff" face="Arial" size="2"></font></span> </div>
<div dir="ltr" align="left"><span class="053004116-21122010"><font color="#0000ff" face="Arial" size="2">Thank you for your response, and indeed the tables do have
indices. The spatial search is just fine and comes back very quickly, however
the text-based searches do not come back quickly (propertyIsLike for
example). I suppose I wasn't clear enough in my original question.
Mapserver does a case insensitive "like" query on the postgres server and on
tables that are very large (millions of rows), this takes too long. We are
training the customers to search for things more intelligently, but we cannot
stop them entirely. We are looking into a full-text indexing methodology,
but in the meantime, we need to solve the problem of the long-running
queries.</font></span></div>
<div dir="ltr" align="left"><span class="053004116-21122010"><font color="#0000ff" face="Arial" size="2"></font></span> </div>
<div dir="ltr" align="left"><span class="053004116-21122010"><font color="#0000ff" face="Arial" size="2">-Matt</font></span></div><br>
<div dir="ltr" class="OutlookMessageHeader" align="left" lang="en-us">
<hr tabindex="-1">
<font face="Tahoma" size="2"><b>From:</b> Carlos Ruiz
[mailto:boolean10001@yahoo.com] <br><b>Sent:</b> Tuesday, December 21, 2010
10:23 AM<br><b>To:</b> Matt Mendick;
mapserver-users@lists.osgeo.org<br><b>Subject:</b> Re: [mapserver-users]
long-running postgres queries initiated by mapserver<br></font><br></div>
<div></div>
<div style="font-family: arial,helvetica,sans-serif; font-size: 10pt;">Matt,<br><br>I
suggest to enhance the PostgreSQL performance at first. Each table with geometry
must have a GiST spatial <br>index. When you upload a shape file to PostgreSQL,
the shp2pgsql creates this index specifying the -I <br>parameter.<br><br>Have
you uploaded your data in this way ?<br><br>If the geometry have been modified
with PostGIS, you must recompute statistics to have the index
updated.<br><br>Check if you have a spatial index in your table.<br><br>Cheers
from México<br><br>
<div style="color: rgb(0, 96, 191); font-weight: bold;">IC Carlos Ruiz</div>
<div style="font-family: arial,helvetica,sans-serif; font-size: 10pt;"><br>
<div style="font-family: arial,helvetica,sans-serif; font-size: 13px;"><font face="Tahoma" size="2">
<hr size="1">
<b><span style="font-weight: bold;">From:</span></b> mattmendick
<matt.mendick@pictometry.com><br><b><span style="font-weight: bold;">To:</span></b>
mapserver-users@lists.osgeo.org<br><b><span style="font-weight: bold;">Sent:</span></b> Tue, December 21, 2010 8:48:19
AM<br><b><span style="font-weight: bold;">Subject:</span></b> [mapserver-users]
long-running postgres queries initiated by mapserver<br></font><br><br>Hi
All-<br><br>I'm running mapserver 5.6.3 under centos 5.4, using fastcgi. I'm
using<br>postgres as the data storage container, and mapserver is primarily
serving<br>WFS requests. Sometimes, people do WFS queries that take a very
long time<br>(searching for "virginia" on a nation-wide layer) and eventually
postgres<br>will return with the result after a long time (hours later), however
the<br>client has cancelled the WFS request. Is there any way for
mapserver to<br>know that the client has cancelled the http request, and it can
therefore<br>stop the DB query to postgres? I tried using persistent and
non-persistent<br>connections with this:<br><br>PROCESSING
"CLOSE_CONNECTION=DEFER"<br><br>but that didn't change the
behavior.<br><br>Thanks a lot!<br>-- <br><span><span>View this message in context: <a target="_blank" href="http://osgeo-org.1803224.n2.nabble.com/long-running-postgres-queries-initiated-by-mapserver-tp5855539p5855539.html">http://osgeo-org.1803224.n2.nabble.com/long-running-postgres-queries-initiated-by-mapserver-tp5855539p5855539.html</a></span></span><br>Sent
from the Mapserver - User mailing list archive at <a rel="nofollow" target="_blank" href="http://Nabble.com">Nabble.com</a>.<br>_______________________________________________<br>mapserver-users
mailing list<br><a rel="nofollow" ymailto="mailto:mapserver-users@lists.osgeo.org" target="_blank" href="mailto:mapserver-users@lists.osgeo.org">mapserver-users@lists.osgeo.org</a><br><span><span><a target="_blank" href="http://lists.osgeo.org/mailman/listinfo/mapserver-users">http://lists.osgeo.org/mailman/listinfo/mapserver-users</a></span></span><br></div></div></div><br>NOTICE: This message is covered by the Electronic Communications Privacy Act, Title 18, United States Code, Sections 2510-2521. This e-mail and any attached files are the exclusive property of Pictometry International Corp., are deemed privileged and confidential, and are intended solely for the use of the individual(s) or entity to whom this e-mail is addressed. If you are not one of the named recipient(s) or believe that you have received this message in error, please delete this e-mail and any attachments and notify the sender immediately. Any other use, re-creation, dissemination,
forwarding or copying of this e-mail is strictly prohibited and may be unlawful.
</div></div>
</div><br>
</body></html>