mapserver + postgis performance problem

[Paul Ramsey]

What version of PostGIS/PostgreSQL?
If >= 8, run VACUUM ANALYZE
If < 8, run SELECT UPDATE_GEOMETRY_STATS()
Sounds like the query planner doesn't have enough information to do a  
good plan combining the spatial and attribute tests.

Paul

On 2-Sep-05, at 2:41 AM, Speiser Ferenc wrote:

> Paul,
>
> The Mapserver generates a query like below:
>
> SELECT
> ARANY::text,asbinary(force_collection(force_2d 
> (the_geom)),'NDR'),gid::te
> xt from (
> the original query
> ) as foo WHERE the_geom && setSRID('BOX3D(425974.5938
> 31897.4055999999,938226.3125 373398.5514)'::BOX3D, -1 );
>
> If I cut the where clause from the end, the difference disappear, and
> everything is okay.
> But in both of the cases this clause is the same! :(
> The indexes on the geometry fields are the same type.
>
> Any clues?
>
> Thx,
> Feri
>
> -----Original Message-----
> From: Paul Ramsey [mailto:pramsey at refractions.net]
> Sent: Thursday, September 01, 2005 11:44 PM
> To: Speiser Ferenc
> Subject: Re: [UMN_MAPSERVER-USERS] mapserver + postgis performance
> problem
>
> Feri,
> There's no way anyone is going to be able to read those SQL lines and
> help, they are just too complicated.
> Turn on statement logging in PostgreSQL, and see exactly what SQL is
> being sent by Mapserver to PostgreSQL for the renders.  Then run that
> SQL with EXPLAIN ANALYZE to see if there is a query planner problem,
> or if you are missing an index somewhere.
> Paul
>
> On 1-Sep-05, at 2:18 AM, Speiser Ferenc wrote:
>
>
>> Hi Everyone!
>>
>>
>> I have a weird performance problem with postgis and mapserver.
>>
>> I have two similar queries, the involved tables and geometry tables
>> has
>> the same structure and indexes and they are running over postgreSQL
>> for
>> 3 seconds.
>>
>> But the rendering time is much longer for the second query (1
>> minute!).
>> The first is okay, it is completed in 3 seconds.
>>
>> I can't find the problem. Both of the queries give correct resultset
>> nearly with the same time over PostreSQL, but during the rendering,
>> there is a huge time-difference.
>>
>> What can cause the difference between the two queries? I inserted the
>> data lines from my mapfiles.
>> Are there any suggestions?
>>
>> Thanks in advance,
>>
>> Feri
>>
>> 2nd query:
>> DATA "the_geom from (select the_geom, gid, KSHKOD, arany/max *100 as
>> arany from (
>>   select distinct query.the_geom, query.gid, query.KSHKOD,
>>         (query.ARANY/queryNorm.ARANY_NORM)*100 as ARANY from
>>           (select the_geom, telhat98_tstar.gid,
>> telhat98_tstar.KSHKOD,         DATA as ARANY from TSTAR,
>> telhat98_tstar
>> where                     telhat98_tstar.KSHKOD =
>> TSTAR.KSHKOD and TSTAR.PT_ID = 'TAAB001'         and
>> YEAR_ID=2001) query
>>       left join
>>           (select telhat98_tstar.KSHKOD, DATA as ARANY_NORM
>>             from TSTAR, telhat98_tstar
>>             where telhat98_tstar.KSHKOD = TSTAR.KSHKOD and
>> TSTAR.PT_ID =
>> 'TAAA200' and YEAR_ID=2001) queryNorm
>>       on query.KSHKOD = queryNorm.KSHKOD) norm,
>>       (select max(arany) from (
>>      select distinct query.the_geom, query.gid, query.KSHKOD,
>>             (query.ARANY/queryNorm.ARANY_NORM)*100 as ARANY
>> from
>>               (select the_geom, telhat98_tstar.gid,
>> telhat98_tstar.KSHKOD, DATA as ARANY from TSTAR,
>> telhat98_tstar where telhat98_tstar.KSHKOD = TSTAR.KSHKOD
>> and TSTAR.PT_ID = 'TAAB001' and YEAR_ID=2001) query
>>       left join
>>           (select telhat98_tstar.KSHKOD, DATA as ARANY_NORM
>>             from TSTAR, telhat98_tstar
>>             where telhat98_tstar.KSHKOD = TSTAR.KSHKOD and
>> TSTAR.PT_ID =           'TAAA200' and YEAR_ID=2001) queryNorm
>>       on query.KSHKOD = queryNorm.KSHKOD) norm) max)
>>  as foo USING UNIQUE gid USING SRID=-1"
>>
>>
>> 1st query:
>>
>> DATA "the_geom from (select the_geom, gid, KSHKOD, arany/max *100 as
>> arany from (
>>   select distinct query.the_geom, query.gid, query.KSHKOD,
>>     (query.ARANY/queryNorm.ARANY_NORM)*100 as ARANY from
>>               (select the_geom, telhat02.gid, telhat02.KSHKOD,
>> DATA as ARANY from NEPSZAMLALAS, telhat02 where
>> telhat02.KSHKOD = NEPSZAMLALAS.KSHKOD and
>> NEPSZAMLALAS.PT_ID = 'NAAC102' and YEAR_ID=2001) query
>>       left join
>>               (select telhat02.KSHKOD, DATA as ARANY_NORM
>>               from NEPSZAMLALAS, telhat02
>>                 where telhat02.KSHKOD = NEPSZAMLALAS.KSHKOD and
>> NEPSZAMLALAS.PT_ID = 'NAAF100' and YEAR_ID=2001) queryNorm
>>       on query.KSHKOD = queryNorm.KSHKOD) norm,
>>           (select max(arany) from (
>>           select distinct query.the_geom, query.gid,
>> query.KSHKOD,
>>             (query.ARANY/queryNorm.ARANY_NORM)*100 as ARANY
>> from
>>               (select the_geom, telhat02.gid, telhat02.KSHKOD,
>> DATA as             ARANY from NEPSZAMLALAS, telhat02 where
>> telhat02.KSHKOD =             NEPSZAMLALAS.KSHKOD and
>> NEPSZAMLALAS.PT_ID = 'NAAC102' and             YEAR_ID=2001)
>> query
>>       left join
>>           (select telhat02.KSHKOD, DATA as ARANY_NORM
>>             from NEPSZAMLALAS, telhat02
>>             where telhat02.KSHKOD = NEPSZAMLALAS.KSHKOD and
>> NEPSZAMLALAS.PT_ID = 'NAAF100' and YEAR_ID=2001) queryNorm
>>       on query.KSHKOD = queryNorm.KSHKOD) norm) max)
>> as foo USING UNIQUE gid USING SRID=-1"
>>
>>
>
>
>