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

[Zamil Murji]

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