[Geomoose-users] query.php performance advice sought

[Johan Forsman]

All:

Preemptive apologies for a long post. I have attempted to collect enough data for the collective to have a chance at theorizing.

I am attempting to use the new query.php to extract a subset of a PostGIS table based on integer values stored in the "syspop" field. My service definition looks like this at the moment (I made a copy of the original query.php and changed the name to allow for troubleshooting without risking destroying the original):

	<service name="advanced_search">
		<url>php/query_sdwp.php</url>
		<step type="input">
			<input type="hidden" name="highlight" value="false"/>
			<input type="hidden" name="mode" value="search"/>

			<input type="hidden" name="layer0" value="wells/sdwis_wells"/>
			<input type="hidden" name="template0" value="itemquery"/>

			<input type="hidden" name="fieldname0" value="syspop"/>
			<input type="user" name="value0" title="Population is greater than"/>
			<input type="hidden" name="comparitor0" value="gt"/>
						
			<input type="hidden" name="operator1" value="and"/>

			<input type="hidden" name="fieldname1" value="syspop"/>			
			<input type="user" name="value1" title="and less than"/>
			<input type="hidden" name="comparitor1" value="lt"/>
		</step>
	</service>

It's not pretty but it works. Sort of. I am having problems with the performance to such extent it's not usable. I have made some attempts to quantify this below. It's not scientific, but it is illustrative.

Relevant stats:
Server hardware is 2 x 2.0 GHz Xeon, 3 GB RAM.
Server OS is Ubuntu Server 9.10.
GeoMoose 2.2 in FGS 9.5 with MapServer 5.4.2.

Dataset of interest is a point set with 4100 total records, stored in PostGIS on the same server. An index on the syspop field has no discernible effect on the performance.

During testing I was the only user on the server.

I turned off highlighting so the query would only run once.

I ran two scenarios:
1) syspop >5000 and syspop <10000
2) syspop >0 and syspop <5000

I uncommented the existing diagnostic lines in query.php to verify the request and the result count for both scenarios and they are both correct:

Search Layer: wells/sdwis_wells Template: itemquery FILTER: syspop > 5000 and syspop < 10000
Total Results: 306
Stop watch time to completion: 13 seconds
Time spent by postgres: 7 seconds
Time spent by httpd: 6 seconds

Search Layer: wells/sdwis_wells Template: itemquery FILTER: syspop > 0 and syspop < 5000
Total Results: 306
Stop watch time to completion: 5.5 minutes!
Time spent by postgres: 30 seconds
Time spent by httpd: 5 minutes!

My baseline comparison is that for both scenarios a SQL query from Access 2007 on my workstation on the linked table from the server, or a row filter from PgAdmin, returns the expected results instantaneously. Apples and oranges I know, but confirmation that the database can work quickly and return the expected results.


----------------------
Johan Forsman
Geologist
Safe Drinking Water Program
Louisiana Department of Health and Hospitals
Office of Public Health
Telephone: 225.342.7309
Telefax: 225.342.7303