[postgis-users] Performance question (how many years will thistake?)

[Mark Cave-Ayland]

Hi Brent,

> -----Original Message-----
> From: postgis-users-bounces at postgis.refractions.net 
> [mailto:postgis-users-bounces at postgis.refractions.net] On 
> Behalf Of Wood Brent
> Sent: 11 May 2004 03:05
> To: PostGIS Users Discussion
> Subject: Re: [postgis-users] Performance question (how many 
> years will thistake?)

(lots cut)

> I assume from this that _intersects_ does NOT do a pre-check 
> with the GiST
> bounding box before the actual polygon check?

Correct.

> Anyway, that appeared to run much faster, until it exited 
> with out-of-memory
> errors. Went well up into over 2G of swap being used.

This bug has been fixed by Dave in CVS. You should be able to solve this
by downloading the postgis_gist_72.c file from the latest CVS snapshot
and recompiling PostGIS. Also if you have compiled with statistics
enabled, make sure that you have run a SELECT update_geometry_stats()
before you run your query.

> What I have now done is add a 
>     "where cell_id >= $START_ID and $CELL_ID < $END_ID"
> into the sql, and wrap it up in a script which starts at 
> cell_id of 0 and
> increments in batches of 100 cells per iteration until it 
> gets to the maximum
> cell_id. (note: there is a unique index on cell_id which is 
> an integer attr)

It would be interesting to compare the results of this with the results
of your original query after applying the fix from CVS.


Cheers,

Mark.

---

Mark Cave-Ayland
Webbased Ltd.
Tamar Science Park
Derriford
Plymouth
PL6 8BX
England

Tel: +44 (0)1752 764445
Fax: +44 (0)1752 764446


This email and any attachments are confidential to the intended
recipient and may also be privileged. If you are not the intended
recipient please delete it from your system and notify the sender. You
should not copy it or use it for any purpose nor disclose or distribute
its contents to any other person.