[postgis-users] Re: [postgis-users] Postgis too slow

[shpr@libero.it]

Yes, in my solution I USE the *itemquery* mode (mode=itemquery) as Obe 
did in his solution.

> Note that the problem I describe is *not* with itemquery or itemnquery
.
> It is with Stefanos particular use of the FILTER parameter to try and
> make a layer which *acts* as if it is doing an itemquery. He is not
> using the layer in itemquery mode, he is using it in normal browse mod
e
> and pushing parameters in which would (on a smaller dataset) cleanly
> subset the layer using the parameters. It is a cute trick, but it does
> not scale at all, because in the presence of the spatial query
> associated with browse mode, the attribute query becomes a sequence sc
an
> over all 800000 of his records.
> 
> "Obe, Regina DND\\MIS" wrote:
> > 
> > I find all this pretty fascinating and so far educational.  I didn't
 realize
> > how the mapserver and postgis integration works.  I have a similar q
uery
> > setup using CGI and the speed is pretty decent (under a second local
)  (but
> > the table I use generally for street segment only has 40,000 street 
segments
> > and my memory and processor are a bit better than Stefano's.
> > 
> > One thing I was wondering about - and whether anyone has tried this.
> > Stefano says he has a 600 Mhz processor with 192mb of ram and an 800
,000
> > record set.  I'm wondering if anyone has actually tried doing what h
e is
> > doing using 192mb of memory with that many records.  I told him I ca
n barely
> > get my server to work with all the services it has to run for that l
ittle
> > amount of on-board memory.  All my servers have at least 256MB ram -
 even
> > the really stupid ones that do nothing.
> > 
> > The other curiosity I had is that if you set the Layer type to QUERY
 instead
> > of LINE does it still incur the same spatial query penalty -
 or would
> > mapserver-
postgis be smart enough to say ("I'm not going to render this so
> > just need the extent") and further if the map mode was set to itemqu
ery
> > instead of itemnquery -
 if that minimizes on spatial query penalty since it
> > knows to only grab first result.
> > 
> > 
> > 
> > -----Original Message-----
> > From: Paul Ramsey [mailto:pramsey at refractions.net]
> > Sent: Wednesday, May 22, 2002 7:09 PM
> > To: postgis-users at postgis.refractions.net
> > Subject: Re: [postgis-users] Postgis too slow
> > 
> > OK, I am now pretty sure the problem was (2) in my previous email. Y
our
> > mapfile layer definition is below. What you are doing is emulating a
n
> > item query by using the FILTER parameter. This is *bad*. It will *no
t*
> > work, because the spatial parameter is way too big, and your attribu
te
> > indexes will never ever get used. The pgsql query optimizer just doe
s
> > not know how to handle this combination of spatial and attribute
> > indexes.
> > 
> > To verify that this is the problem, either (1) change your EXTENT so
> > that it is a very *small* box which contains just your one test road
 and
> > very little else. Now see how fast it runs. Or (2) remove the FILTER
> > entirely (don't to 1=2, *remove* it) and zoom into a small area. Now
 pan
> > around the dataset. That should demonstrate the native spatial query
> > speed.
> > 
> > You are not going to be able to do this query with the standard CGI
> > interface. You are going to have to use PHP MapScript and use PHP di
rect
> > connection to pgsql to do the attribute query and find the extent of
 the
> > resultant lines, then set the extent of your map appropriately, then
> > render. If you have simple queries, you can use the itemquery in the
 CGI
> > interface, but your query is too complex for that.
> > 
> > This is map postgis layer:
> > 
> > LAYER
> > STATUS OFF
> >   NAME "stradeQ"
> > CONNECTIONTYPE postgis
> > CONNECTION "user=postgres password=postgres dbname=market host=serve
r
> > port=5432"
> >   DATA "the_geom from streets"
> > TYPE LINE
> > FILTER "(upper(st_nm_base) Like upper('%
street%')) AND (l_area_id Like
> > '%value%')
> > AND ('%number%'='' or '%number%' between l_refaddr and l_nrefaddr or
> > '%number%'
> > between r_refaddr and r_nrefaddr) "
> > 
> > CLASS
> >   SYMBOL 0
> >   OUTLINECOLOR 25 25 0
> > END
> > TEMPLATE market.html
> > END
> > 
> > --
> >       __
> >      /
> >      | Paul Ramsey
> >      | Refractions Research
> >      | Email: pramsey at refractions.net
> >      | Phone: (250) 885-0632
> >      \_
> > 
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users at postgis.refractions.net
> > http://postgis.refractions.net/mailman/listinfo/postgis-users
> > 
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users at postgis.refractions.net
> > http://postgis.refractions.net/mailman/listinfo/postgis-users
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>