<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META content="text/html; charset=iso-8859-1" http-equiv=Content-Type>
<STYLE type=text/css>DIV {
MARGIN: 0px
}
</STYLE>
<META name=GENERATOR content="MSHTML 8.00.6001.18999"></HEAD>
<BODY>
<DIV dir=ltr align=left><SPAN class=053004116-21122010><FONT color=#0000ff
size=2 face=Arial>Carlos,</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=053004116-21122010><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=053004116-21122010><FONT color=#0000ff
size=2 face=Arial>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
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=053004116-21122010><FONT color=#0000ff
size=2 face=Arial>-Matt</FONT></SPAN></DIV><BR>
<DIV dir=ltr lang=en-us class=OutlookMessageHeader align=left>
<HR tabIndex=-1>
<FONT size=2 face=Tahoma><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
size=2 face=Tahoma>
<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>View this message in context: <A
href="http://osgeo-org.1803224.n2.nabble.com/long-running-postgres-queries-initiated-by-mapserver-tp5855539p5855539.html"
target=_blank>http://osgeo-org.1803224.n2.nabble.com/long-running-postgres-queries-initiated-by-mapserver-tp5855539p5855539.html</A></SPAN><BR>Sent
from the Mapserver - User mailing list archive at <A href="http://Nabble.com"
target=_blank>Nabble.com</A>.<BR>_______________________________________________<BR>mapserver-users
mailing list<BR><A href="mailto:mapserver-users@lists.osgeo.org"
ymailto="mailto:mapserver-users@lists.osgeo.org">mapserver-users@lists.osgeo.org</A><BR><SPAN><A
href="http://lists.osgeo.org/mailman/listinfo/mapserver-users"
target=_blank>http://lists.osgeo.org/mailman/listinfo/mapserver-users</A></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.
</BODY></HTML>