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