mapserver + postgis performance problem
Speiser Ferenc
speiser.ferenc at FREEMAIL.HU
Thu Sep 1 02:18:16 PDT 2005
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"
More information about the MapServer-users
mailing list