Mapserver 4.4.1 /POSTGIS 0.8/PostgreSQL 7.4.3 query performance puzzler

[John Novak]

Thanks for the feedback and suggestions.  It is certainly true that
something different is happening when using a cursor.  Using pgAdmin,
explain is reporting identical plans both with and without the cursor
even though executions times are radically different.

I managed to get response times reasonable by experimenting with
several table index designs so this was certainly caused by a table
scan.

The unique oid clause had no measurable effect on the query time, but
it's possible that the table scan time was so much larger that its
effect was relatively small.


On Apr 6, 2005 9:39 AM, Fernando Simon <fsimon at univali.br> wrote:
> 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
>