[postgis-users] please help me optimize my mapserver query

[Purvis, Charlton]

Zamil, I have a similar issue, but my main trimming factor is time (like
your source column).  I have found that dropping the gist index, altogether,
helps tremendously.

> -----Original Message-----
> From: Zamil Murji [mailto:Zamil.Murji at divestco.com]
> Sent: Wednesday, December 15, 2004 11:55 AM
> To: PostGIS Users Discussion
> Subject: RE: [postgis-users] please help me optimize my mapserver query
> 
> The source always changes. Creating views for each source is not the
> proper way to do this because then I would have to create multiple layers
> in mapserver. Right now I'm using variable substitution to pull stuff
> dynamically.
> 
> Zamil
> 
> -----Original Message-----
> From: postgis-users-bounces at postgis.refractions.net
> [mailto:postgis-users-bounces at postgis.refractions.net]On Behalf Of
> Martel, Christian
> Sent: Wednesday, December 15, 2004 9:51 AM
> To: 'PostGIS Users Discussion'
> Subject: RE: [postgis-users] please help me optimize my mapserver query
> 
> 
> If the source is always 'TIG600', create a view.
> 
> Christian
> 
> -----Original Message-----
> From: Zamil Murji [mailto:Zamil.Murji at divestco.com]
> Sent: Wednesday, December 15, 2004 11:31
> To: PostGIS Users Discussion
> Cc: MAPSERVER-USERS at LISTS.UMN.EDU
> Subject: [postgis-users] please help me optimize my mapserver query
> 
> 
> Hello list,
> 
> I have a mapserver layer that is extremely slow. After yesterday's data
> update, this layer is taking forever to draw.
> 
> Here is the query that is generated:
> SELECT
> uwi::text,asbinary(force_collection(force_2d(the_geom)),'NDR'),oid::text
> from (select  main_well2.the_geom as the_geom, main_well2.oid as oid,
> main_well2.uwi as uwi,  well_log_curve2.source as source from main_well2
> inner join well_log_curve2 on main_well2.uwi =  well_log_curve2.uwi) as
> new_table WHERE (source = 'TIG600') and (the_geom && setSRID(
> 'BOX3D(-142.633293514961 38.5465516731216,-51.4598436170248
> 76.5484665317199)'::BOX3D,find_srid('','main_well2','the_geom') ))
> 
> The key part to this query is the FILTER tag (source = 'TIG600').
> well_log_curve2 has approximately 5.6 million rows. main_well2 has
> approximately 543000 rows. Before the update, the query plan use to filter
> the well_log_curve table by source before joining to main_well2 and then
> apply the gist index. Now it is filtering by source at the very end. This
> is
> significant because if you filter by source first, then you will only
> receive back around 100 rows.
> 
> Here is the plan now:
>                                                                QUERY PLAN
> 
> --------------------------------------------------------------------------
> --
> ------------------------------------------------------------
>  Nested Loop  (cost=0.00..844.36 rows=1 width=74)
>    ->  Index Scan using main_well2_the_geom_idx on main_well2
> (cost=0.00..12.90 rows=3 width=55)
>          Index Cond: (the_geom && 'SRID=-1;BOX3D(-142.633293514961
> 38.5465516731216 0,-51.4598436170248 76.5484665317199 0)'::geometry)
>    ->  Index Scan using well_log_curve2_uwi_idx on well_log_curve2
> (cost=0.00..306.22 rows=2 width=19)
>          Index Cond: ("outer".uwi = well_log_curve2.uwi)
>          Filter: (source = 'TIG600'::character varying)
> (6 rows)
> 
> Also, there is no point in using the gist index first because EVERYTHING
> fits inside that box.
> Here are the relevant parts to my .map file:
> 
>    DATA "the_geom from (select main_well2.the_geom as the_geom,
> main_well2.oid as oid, main_well2.uwi as uwi, well_log_curve2.source as
> source from main_well2 inner join well_log_curve2 on main_well2.uwi =
> well_log_curve2.uwi) as new_table using unique oid"
>    FILTER "source = '%source%'"
> 
> I have tried the following:
> SELECT update_geometry_stats('main_well2','the_geom');
> VACUUM ANALYZE;
> 
> after this, the query still returns the same query plan.
> How can I force it to filter by source before joining with main_well2. I
> am
> up for changing the DATA statement and/or removing the FILTER tag and
> incorporating it into the DATA statement somehow.
> 
> Thanks in advance,
> Zamil
> _______________________________________________
> 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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20041215/fb933dce/attachment.html>