<!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.18702"></HEAD>
<BODY>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=812123407-22122010>Hi,</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=812123407-22122010></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=812123407-22122010>This is a problem with big databases and
standard WFS where users have no limits and they can build
however complicated queries. Indices are essential but they cannot handle
all situations. For example strings from the middle of text fields
with propertyIsLike usually leads to full table scan. Finding
intersections with a polygon of tens of thousands or vertices is heavy. By
using a lots of ANDs and ORs and some imagination and knowledge of WFS
and databases a WFS user can rather easily build requests which will jam
your database server. A clever user can even guess what database you have
behind WFS by experimenting what kind of queries are exceptionally
slow.</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=812123407-22122010></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=812123407-22122010>If you are running some critical production system I
believe you should consider making a kind of proxy service in front of the real
WFS server. The proxy should investigate the GetFeature requests and deny
or modify them by adding some additional filter elements if they do not fulfil
your rules.</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=812123407-22122010></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=812123407-22122010>-Jukka Rahkonen-</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=812123407-22122010></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=812123407-22122010></SPAN></FONT> </DIV>
<DIV dir=ltr align=left>
<HR tabIndex=-1>
</DIV>
<DIV dir=ltr align=left><FONT size=2 face=Tahoma><B>Lähettäjä:</B>
mapserver-users-bounces@lists.osgeo.org
[mailto:mapserver-users-bounces@lists.osgeo.org] <B>Puolesta </B>Matt
Mendick<BR><B>Lähetetty:</B> 21. joulukuuta 2010 18:44<BR><B>Vastaanottaja:</B>
Carlos Ruiz; mapserver-users@lists.osgeo.org<BR><B>Aihe:</B> RE:
[mapserver-users] long-running postgres queries initiated
bymapserver<BR></FONT><BR></DIV>
<BLOCKQUOTE
style="BORDER-LEFT: #0000ff 2px solid; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; MARGIN-RIGHT: 0px">
<DIV></DIV>
<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.
</BLOCKQUOTE></BODY></HTML>