Mapserver 4.4.1 /POSTGIS 0.8/PostgreSQL 7.4.3 query performance puzzler

[Fernando Simon]

Hi all,
    I saw this problem in client test, but was a problem with data
definition. I don`t know if is your case but I believe that this
hints can help you.
    Here the client defined this data query: "the_geom from (SELECT
regiao.the_geom AS the_geom, fundo.no_programa AS no_programa,
fundo.nu_ano AS ano, fundo.vl_pago AS vl_pago FROM regiao inner join
fundo on regiao.co_ibge = fundo.co_ibge and no_programa = 'PAB FIXO'
and fundo.nu_ano = 2004 and regiao.co_ibge <= 150000 and
regiao.co_ibge >= 140000) as pab_fixo using unique regiao.oid using
srid=4291"
    But this query needed 55s to finish, and when I used the psql to
run the asbinary query the results appear in 3s.
    The problem was with the UNIQUE parameter. The right data
definition was: the_geom from (SELECT regiao.the_geom AS the_geom,
regiao.oid as oid, fundo.no_programa AS no_programa, fundo.nu_ano AS
ano, fundo.vl_pago AS vl_pago FROM regiao inner join fundo on
regiao.co_ibge = fundo.co_ibge and no_programa = 'PAB FIXO' and
fundo.nu_ano = 2004 and regiao.co_ibge <= 150000 and regiao.co_ibge
>=140000) as pab_fixo using unique oid using srid=4291. And now the
results appear in 5s.
    Thanks for all.


------------------------------------------------------------------------
Fernando Simon
Mapserver and Oracle Spatial developer
G10 - Laboratorio de Computacao Aplicada - Brazil
http://g10.cttmar.univali.br - UNIVALI/CTTMAR
------------------------------------------------------------------------



Citando Bill Binko <bill at BINKO.NET>:

> On Wed, 6 Apr 2005, John Novak wrote:
> >
> > A trace from PostgreSQL shows the following SQL block executing
> at the server:
> >
> > begin; DECLARE mycursor CURSOR FOR SELECT
> >
>
asbinary(force_collection(force_2d(mapdata2.the_geom)),'NDR'),OID::text
> from
> > (select mc.the_geom, mc.oid as oid from
> > products_components as pc, mapdata as mc where pc.products_id > 0
> and
> > pc.roll = mc.roll and pc.frame = mc.frame) as mapdata2
> > WHERE mapcat2.the_geom && setSRID('BOX3D(-84.6 35.4625,-69.6
> > 46.7125)'::BOX3D, 4326 ); ; FETCH ALL in mycursor; commit
> >
> ...
> >
> > Running just the SELECT asbinary 
  in PgAdmin III shows a 516ms
> response
> > including data transfer, so something related to the FETCH ALL is
> causing
> > the problem.  I suspect I have the server badly tuned as most
> parameters are
> > still at installation default values.  Running the entire block
> replicates
> > the very sad behavior.
>
> I am no PostGIS expert, but I had a similar problem recently.  It
> seems
> that Postgresql uses different query optimization routines for the
> two
> types of queries.  I solved my problem (with help from a stray
> comment on
> postGIS's site) by changing this parameter in postgresql.conf:
>
> random_page_cost = 2
> (It was 4).
>
> This made it use the GIST index, where it was doing a full table
> scan.
>
> You can tell which one it's using by running your two queries with
> EXPLAIN
> ANALYZE before them.  I would bet that one uses the index and the
> other
> doesn't.
>
> Bill
>




-------------------------------------------------
  Univali - Webmail - http://webmail.univali.br