[Mapserver-users] Mapscript drawQuery() on big PostGIS layers, oid indexes, and getResult()

[Daniel Faivre]

  Great thanks to David and Paul for their good advices. As I used 
"using unique", I've wrongly thought that indexing oid wasn't necessary. 
With an index on oid, my problem was "half-solved": it's now possible to 
use the drawQuery() on my big layer.

But there's still a problem: how could I properly retrieve fields values 
for all queried features ? The problem is that if I use 
$my_big_layer->getResult($idx) in a loop to get the features ids, 
execution time is too long.

I see two ways to solve this last problem:
1) Adding a PostGIS query after the similar Mapserver query, and using 
PostGIS results for db processes and Mapserver result only for drawing ?
2) Adding another web page just to do the same query a second time 
without any other task, to break the timeout barrier ?

Which is the best idea ? Is there a third solution ?

Best regards,

-- 
Daniel Faivre
Ohceo - Camptocamp
04 79 75 28 19
180 rue du Genevois
73000 Chambéry le Haut




David Blasby wrote:

> Paul Ramsey wrote:
>
>>   Cost analysis. On large enough tables and complex enough queries,  
>> cost analysis gets important. A PostGIS installation with the STATS  
>> option enabled and stats calculated can do the "right thing" in 
>> query  planning which a naive installation completely screws up.
>
>
> Whats happening is that postgresql is using the spatial index (which 
> presumably returns the entire layer), then does a sequencial scan to 
> find the rows with the "filter" attributes.
>
> The most likely problem is you dont have an index on OID:
>
> CREATE INDEX mybigpolygon_table_oid_idx ON mybigpolygon_table (oid);
>
> You might find that turning on stats will give you speed improvements. 
> To do this:
> 1. make sure your postGIS makefile has "USESTATS=1" set
> 2. in postgresql, do a "SELECT UPDATE_GEOMETRY_STATS();"
> 3. in postgresql, do a "VACUUM ANALYSE"
>
> If this doesnt work, I'll need more information.  Turn the layer's 
> DEBUG flag to on, and run your script.  You should see the SQL query 
> go by (it starts with "DECLARE CURSOR mycursor FOR ...".
>
> If it says "DECLARE CURSOR mycursor FOR SELECT the_geom,oid FROM 
> mybigpolygon_table", then go into postgresql and type:
>
> EXPLAIN ANALYSE SELECT the_geom,oid FROM mybigpolygon_table
>
> (I replaced the 'DECLARE CURSOR mycursor FOR' with 'EXPLAIN ANALYSE')
>
> The output will tell you what postgresql is doing and how long its 
> taking.
>
> dave
>
>
>
> _______________________________________________
> Mapserver-users mailing list
> Mapserver-users at lists.gis.umn.edu
> http://lists.gis.umn.edu/mailman/listinfo/mapserver-users


-- 
Daniel Faivre
Ohceo - Camptocamp
04 79 75 28 19
180 rue du Genevois
73000 Chambéry le Haut