[postgis-users] Mapserv Issues

[Zamil Murji]

Ok, after doing some more research,  I have found my bottleneck.  Inside mappostgis.c there is a function called msPOSTGISLayerGetItems. This function formats a query:
sprintf(sql,"SELECT * FROM %s LIMIT 0",table_name); // attempt the query, but dont actually do much (this might take sometime if there is an order by!)

the result sql looks like so:
SELECT * FROM (select oid, to_date(main_well1.spud_date, 'YYYY-MM-DD') as pretty_spud_date, to_date(main_well1.rig_release_date, 'YYYY-MM-DD') as pretty_rig_release_date, * from main_well1) as new_table LIMIT 0

an explain analyze on this query shows the following:
idc=# explain analyze SELECT * FROM (select oid, to_date(main_well1.spud_date, 'YYYY-MM-DD') as pretty_spud_date, to_date(main_well1.rig_release_date, 'YYYY-MM-DD') as pretty_rig_release_date, * from main_well1) as new_table LIMIT 0;
                                                        QUERY PLAN              
--------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..0.00 rows=1 width=331) (actual time=3646.33..3646.33 rows=0 loops=1)
   ->  Seq Scan on main_well1  (cost=0.00..113056.36 rows=487336 width=331) (actual time=3646.33..3646.33 rows=1 loops=1)
 Total runtime: 3646.40 msec
(3 rows)


There is the 3.6 seconds that I'm looking to get rid of. Can somebody please tell me how I can speed up this query?
Thanks
Zamil

-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net]On Behalf Of Zamil
Murji
Sent: Monday, May 17, 2004 9:30 AM
To: PostGIS Users Discussion
Subject: [postgis-users] Mapserv Issues


Hello list,

I've been having some weird problems with my mapserv install. These speed issues arise predominantly in NQUERY mode. Here is the situation:
1) I've created a spatial index on my geometry column
2) I've created an index on the oid column.
3) I've vacuum analyzed
4) I've run SELECT UPDATE_GEOMETRY_STATS();

After all of this, running the query at the psql command prompt gives excellent times (<0.5 seconds), but running it using mapserv takes ~5 seconds. I'm now convinced that this is not a database issue, but a bottleneck somewhere else. Maybe the connection is not getting setup right away but taking 2-3 seconds. Can anyone point me in the right direction? I'm having a tough time with this one. 

In the past doing the 4 steps above worked (speed issues would go away for a while but then everything would be back to slowness after a while). Now it seems that these 4 steps are doing nothing to help mapserv run faster.

I have also tried replacing my DATA statement with a simpler one:
DATA "the_geom from main_well1 using unique oid"
This does not seem to have an effect on the speed. Like I said before the query takes no time at all.

Here is my .map file
NAME TEST1

PROJECTION
 "proj=merc"
  "lat_ts=54.458353"
  "lon_0=-111.729445"
  "ellps=GRS80"
  "datum=NAD83"
END

LAYER
   NAME "wells"
#   DEBUG on
   TYPE point
   STATUS default
   MAXSCALE 1000000
   CONNECTIONTYPE postgis
   CONNECTION "user=idc dbname=idc host=idcmap port=5432"
   DATA "the_geom from (select oid, to_date(main_well1.spud_date, 'YYYY-MM-DD') as pretty_spud_date, to_date(main_well1.rig_rele\
ase_date, 'YYYY-MM-DD') as pretty_rig_release_date, * from main_well1) as new_table using unique oid"
   TEMPLATE "./idcdev_wells.html"
   PROJECTION
    "init=epsg:4269"
   END
END

END


Eternally grateful for you help.
Zamil
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users