Re: [mapserver-users] long-running postgres queries initiated by mapserver
Your welcome, Matt. Now it is clear why the query is slow. You must have indices on the columns that you're searching in (and run VACUUM frecuently to keep them updated). If you're using LIKE operator, then the indices are not useful if the columns are not LOCALEd. Check if you have defined LOCALE to speed up the LIKE matching. Also, if the data doesn't change at all, you can CLUSTER the table to improve the query speed. IC Carlos Ruiz From: Matt Mendick To: Carlos Ruiz ; "mapserver-users@lists.osgeo.org" Sent: Tue, December 21, 2010 10:43:46 AM Subject: RE: [mapserver-users] long-running postgres queries initiated by mapserver Carlos, 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. -Matt From: Carlos Ruiz [mailto:boolean10...@yahoo.com] Sent: Tuesday, December 21, 2010 10:23 AM To: Matt Mendick; mapserver-users@lists.osgeo.org Subject: Re: [mapserver-users] long-running postgres queries initiated by mapserver Matt, I suggest to enhance the PostgreSQL performance at first. Each table with geometry must have a GiST spatial index. When you upload a shape file to PostgreSQL, the shp2pgsql creates this index specifying the -I parameter. Have you uploaded your data in this way ? If the geometry have been modified with PostGIS, you must recompute statistics to have the index updated. Check if you have a spatial index in your table. Cheers from México IC Carlos Ruiz From: mattmendick To: mapserver-users@lists.osgeo.org Sent: Tue, December 21, 2010 8:48:19 AM Subject: [mapserver-users] long-running postgres queries initiated by mapserver Hi All- I'm running mapserver 5.6.3 under centos 5.4, using fastcgi. I'm using postgres as the data storage container, and mapserver is primarily serving WFS requests. Sometimes, people do WFS queries that take a very long time (searching for "virginia" on a nation-wide layer) and eventually postgres will return with the result after a long time (hours later), however the client has cancelled the WFS request. Is there any way for mapserver to know that the client has cancelled the http request, and it can therefore stop the DB query to postgres? I tried using persistent and non-persistent connections with this: PROCESSING "CLOSE_CONNECTION=DEFER" but that didn't change the behavior. Thanks a lot! -- View this message in context: http://osgeo-org.1803224.n2.nabble.com/long-running-postgres-queries-initiated-by-mapserver-tp5855539p5855539.html Sent from the Mapserver - User mailing list archive at Nabble.com. ___ mapserver-users mailing list mapserver-users@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/mapserver-users 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. ___ mapserver-users mailing list mapserver-users@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/mapserver-users
RE: [mapserver-users] long-running postgres queries initiated by mapserver
Carlos, 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. -Matt From: Carlos Ruiz [mailto:boolean10...@yahoo.com] Sent: Tuesday, December 21, 2010 10:23 AM To: Matt Mendick; mapserver-users@lists.osgeo.org Subject: Re: [mapserver-users] long-running postgres queries initiated by mapserver Matt, I suggest to enhance the PostgreSQL performance at first. Each table with geometry must have a GiST spatial index. When you upload a shape file to PostgreSQL, the shp2pgsql creates this index specifying the -I parameter. Have you uploaded your data in this way ? If the geometry have been modified with PostGIS, you must recompute statistics to have the index updated. Check if you have a spatial index in your table. Cheers from México IC Carlos Ruiz From: mattmendick To: mapserver-users@lists.osgeo.org Sent: Tue, December 21, 2010 8:48:19 AM Subject: [mapserver-users] long-running postgres queries initiated by mapserver Hi All- I'm running mapserver 5.6.3 under centos 5.4, using fastcgi. I'm using postgres as the data storage container, and mapserver is primarily serving WFS requests. Sometimes, people do WFS queries that take a very long time (searching for "virginia" on a nation-wide layer) and eventually postgres will return with the result after a long time (hours later), however the client has cancelled the WFS request. Is there any way for mapserver to know that the client has cancelled the http request, and it can therefore stop the DB query to postgres? I tried using persistent and non-persistent connections with this: PROCESSING "CLOSE_CONNECTION=DEFER" but that didn't change the behavior. Thanks a lot! -- View this message in context: http://osgeo-org.1803224.n2.nabble.com/long-running-postgres-queries-initiated-by-mapserver-tp5855539p5855539.html Sent from the Mapserver - User mailing list archive at Nabble.com<http://Nabble.com>. ___ mapserver-users mailing list mapserver-users@lists.osgeo.org<mailto:mapserver-users@lists.osgeo.org> http://lists.osgeo.org/mailman/listinfo/mapserver-users 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. ___ mapserver-users mailing list mapserver-users@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/mapserver-users
Re: [mapserver-users] long-running postgres queries initiated by mapserver
Matt, I suggest to enhance the PostgreSQL performance at first. Each table with geometry must have a GiST spatial index. When you upload a shape file to PostgreSQL, the shp2pgsql creates this index specifying the -I parameter. Have you uploaded your data in this way ? If the geometry have been modified with PostGIS, you must recompute statistics to have the index updated. Check if you have a spatial index in your table. Cheers from México IC Carlos Ruiz From: mattmendick To: mapserver-users@lists.osgeo.org Sent: Tue, December 21, 2010 8:48:19 AM Subject: [mapserver-users] long-running postgres queries initiated by mapserver Hi All- I'm running mapserver 5.6.3 under centos 5.4, using fastcgi. I'm using postgres as the data storage container, and mapserver is primarily serving WFS requests. Sometimes, people do WFS queries that take a very long time (searching for "virginia" on a nation-wide layer) and eventually postgres will return with the result after a long time (hours later), however the client has cancelled the WFS request. Is there any way for mapserver to know that the client has cancelled the http request, and it can therefore stop the DB query to postgres? I tried using persistent and non-persistent connections with this: PROCESSING "CLOSE_CONNECTION=DEFER" but that didn't change the behavior. Thanks a lot! -- View this message in context: http://osgeo-org.1803224.n2.nabble.com/long-running-postgres-queries-initiated-by-mapserver-tp5855539p5855539.html Sent from the Mapserver - User mailing list archive at Nabble.com. ___ mapserver-users mailing list mapserver-users@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/mapserver-users ___ mapserver-users mailing list mapserver-users@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/mapserver-users
[mapserver-users] long-running postgres queries initiated by mapserver
Hi All- I'm running mapserver 5.6.3 under centos 5.4, using fastcgi. I'm using postgres as the data storage container, and mapserver is primarily serving WFS requests. Sometimes, people do WFS queries that take a very long time (searching for "virginia" on a nation-wide layer) and eventually postgres will return with the result after a long time (hours later), however the client has cancelled the WFS request. Is there any way for mapserver to know that the client has cancelled the http request, and it can therefore stop the DB query to postgres? I tried using persistent and non-persistent connections with this: PROCESSING "CLOSE_CONNECTION=DEFER" but that didn't change the behavior. Thanks a lot! -- View this message in context: http://osgeo-org.1803224.n2.nabble.com/long-running-postgres-queries-initiated-by-mapserver-tp5855539p5855539.html Sent from the Mapserver - User mailing list archive at Nabble.com. ___ mapserver-users mailing list mapserver-users@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/mapserver-users
