[postgis-users] Postgis too slow

[Obe, Regina DND\MIS]

Does the speed vary depending on the number street segments you get back and
does your template list each street result (e.g . you have it set for
itemnquery mode) ?

The TEMPLATE gets drawn for each result returned (if you have it set for
itemnquery mode) so the bottleneck might be the drawing of each result and
if you have a map defined in your template, then it could be drawing a map
for each result - which could be needlessly intensive.

I noticed for mine - since I didn't need the streets listed - just
highlighted on the map, that I was able to significantly reduce the load
time by setting my template to a blank page, but displaying the map with
street segments highlighted using the HEADER template

e.g. something like (where blank.html - is a blank page).

LAYER
STATUS OFF
  NAME "stradeQ"
CONNECTIONTYPE postgis
CONNECTION "user=postgres password=postgres dbname=market host=server
port=5432"
  DATA "the_geom from streets"
TYPE LINE
FILTER "(upper(st_nm_base) Like upper('%street%')) AND (l_area_id Like
'%value%')
AND ('%number%'='' or '%number%' between l_refaddr and l_nrefaddr or
'%number%'
between r_refaddr and r_nrefaddr) "

CLASS
  SYMBOL 0
  OUTLINECOLOR 25 25 0
END

HEADER market.html
TEMPLATE blank.html

END


Also I am assuming you have a GIST index on your the_geom field and index on
oid?





-----Original Message-----
From: Stefano Bonnin [mailto:shpr at libero.it]
Sent: Thursday, May 16, 2002 4:00 AM
To: postgis-users at postgis.refractions.net
Cc: aborruso at spaziogis.it; Obe, Regina DND\MIS
Subject: Re: [postgis-users] Postgis too slow


Yesterday I didn't explain my problem very well, but now I can do this.

This is map postgis layer:

LAYER
STATUS OFF
  NAME "stradeQ"
CONNECTIONTYPE postgis
CONNECTION "user=postgres password=postgres dbname=market host=server
port=5432"
  DATA "the_geom from streets"
TYPE LINE
FILTER "(upper(st_nm_base) Like upper('%street%')) AND (l_area_id Like
'%value%')
AND ('%number%'='' or '%number%' between l_refaddr and l_nrefaddr or
'%number%'
between r_refaddr and r_nrefaddr) "

CLASS
  SYMBOL 0
  OUTLINECOLOR 25 25 0
END

TEMPLATE market.html

END

I have created the following index:

create index street_index on streets (st_nm_base, l_area_id);

where st_nm_base is the street name and l_area_id is a city identifier.

if I try this query under postgresql, it responds immediately (but postgis
is always very very slow).

I know that I'm doing something wrong but I don't know what!

Thanks!

Stefano Bonnin


----- Original Message -----
From: "Dave Blasby" <dblasby at refractions.net>
To: <postgis-users at postgis.refractions.net>
Sent: Wednesday, May 15, 2002 6:51 PM
Subject: Re: [postgis-users] Postgis too slow


> Paul Ramsey wrote:
> >
> > Check some queries, do some bounding box queries using 'explain' and see
> > if you are getting index scans. Remember to 'vacuum analyze' your
> > database after building the indexes. Be peaceful and good.
>
> Dont forget to put an index on the OID of the table you're using.
>
> create index oid_geo_idx on <table> (OID);
>
> dave
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users


---
Outgoing mail is certified Virus Free. (FLASHH!)
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.361 / Virus Database: 199 - Release Date: 07/05/02