[postgis-users] Multiple table join - mapserver

[Paul Ramsey]

I am surprised it is eating up memory, but not surprised it is slow,  
it is possible you are getting a bad plan... make sure you have run  
VACUUM ANALYZE.

You could try:

DATA "the_geom FROM (SELECT akmon_pt.the_geom AS the_geom ,akmon_pt.maid
AS maid,akmon_pt.locid AS locid FROM akmon_pt,i5dbxma , i5maxat where
akmon.the_geom && !BOX! and akmon_pt.maid=i5dbxma.maid and  
i5maxat.maid=i5dbxma.maid and i5maxat.atid=1 and i5dbxma.fyear>=1992  
and i5dbxma.lyear<=2005 and
i5dbxma.spp=2 and i5dbxma.esu=-363155345) AS foo USING UNIQUE locid
USING SRID=-1"

I am not sure how robust the DATA parsing method is, but the !BOX!  
hack exists as a way of ensuring Mapserver creates a SQL query  
exactly the way you expect, rather than tacking the box on the end  
and hoping the PgSQL planner can resolve it into something efficient  
(usually, it can).

Paul



On May 19, 2006, at 1:49 PM, Greg Robillard wrote:

> I'm using a subselect in my mapfile that uses three tables:
> DATA "the_geom FROM (SELECT akmon_pt.the_geom AS  
> the_geom ,akmon_pt.maid
> AS maid,akmon_pt.locid AS locid FROM akmon_pt,i5dbxma , i5maxat where
> akmon_pt.maid=i5dbxma.maid and i5maxat.maid=i5dbxma.maid and
> i5maxat.atid=1 and i5dbxma.fyear>=1992 and i5dbxma.lyear<=2005 and
> i5dbxma.spp=2 and i5dbxma.esu=-363155345) AS foo USING UNIQUE locid
> USING SRID=-1"
>
> It causes postgres to eat up all available memory on the machine. If I
> execute the above in psql, the record returns instantly. If I  
> attempt to
> join only two tables, Mapserver works fine.
>
> Is this a known limitation?
>
> Best,
>
> Greg Robillard
> Database Developer
> State of the Salmon
> greg at ecotrust.org
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users