[postgis-users] Postgis too slow

[Andy Turk]

On Thursday 23 May 2002 07:40 am, Obe, Regina wrote:
> I'm wondering if anyone has actually tried doing what he is
> doing using 192mb of memory with that many records.  

We don't use mapserver here, but we are using postgis and the GiST
indexing on a moderate database consisting of over 2 million records.

Our server is a 500Mhz Athlon box with 256MB of memory and a SCSI
disk. This is not a large machine by any stretch, but I've been amazed at
performance we're seeing.

Our Java code issues straight geographic queries that use a GiST index
and end up returning anywhere up to several hundred rows. If the pages
of the index and the leaf data are in the postgres cache, results come back
basically instantaneously. We've never actually measured the response time,
but we get maps on the screen very, very quickly.

I'm not a hard-core DB guy, but I think the performance comes from:

1) the use of a GiST index
2) both the index pages and the leaf pages are in RAM, not on disk
3) our geometry data is not skewed in size and has relatively little overlap

Our experience with postgis makes me think that the slowness that others
are experiencing is due to some other factor than the efficiency of postgres
or postgis.

For the cost of a bottom-end Oracle/Spatial license, we can buy
a rack full of big, honking Linux boxen that'll run like the wind.

Andy