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

[Mark Cave-Ayland]

Hi Zamil,

Can you return the output of the query as given by EXPLAIN ANALYZE, not just
EXPLAIN? This will show the differences between the estimated and actual
values. Also, which versions of PostgreSQL and PostGIS are you using?


Kind regards,

Mark.

------------------------
WebBased Ltd
South West Technology Centre
Tamar Science Park
Plymouth
PL6 8BT 

T: +44 (0)1752 791021
F: +44 (0)1752 791023
W: http://www.webbased.co.uk
 

> -----Original Message-----
> From: postgis-users-bounces at postgis.refractions.net 
> [mailto:postgis-users-bounces at postgis.refractions.net] On 
> Behalf Of Zamil Murji
> Sent: 15 December 2004 16: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
>