[postgis] Gut feeling question about performance

[Paul Ramsey]

> Now, what is your gut feeling people, how fast query responses can I
> expect? Will I get a response under one second? 

Gut feeling is response will be on the order of one second. It will not
be instantaneous, there will be a slight pause. That is my guess. I
could be completely wrong :)

> Any tips that I should know when optimising the query performance...?

Check out Bruce Momjian's guide to optimizing pgsql in general. Pgsql
ships with relatively small memory segments and buffers by default, and
you can sometimes get quite noticable improvements by upping them.

Memory is so cheap right now!! Can I assume your memory problem is
because your motherboard is too old to take modern DIMMS? Really, noone
has any excuse for not buying memory right now (heck, I think I'll go
out and buy some this afternoon).

More memory gives your OS more room to cache disk pages in memory, which
will in turn increase your performance hugely. By extension, having your
disks in a RAID array will speed up disk transfers, but once you get the
index completely into memory that becomes a non-issue for the most part.

Keep your features at a homogeneous size for best speed. If you can cut
up really long features into smaller bits, do so. Long features have
huge bounding boxes and end up inside alot more windows than they
should.

Use WKB and binary cursors to retrieve data via libpq. Parsing strings
is a huge overhead (though since you are only getting things 500 items
at a time, probably not your worst problem).

"Petri J. Riipinen" wrote:

> I'm putting a big road network (about 3 million records, each representing
> a single ARC-shape) into PostgreSQL + PostGIS. This covers roughly 600x1500
> km area in real world. I'm planning on making a GiST index for this table.
> The data is originally in ESRI Shapefile-format and I have converted it to
> SQL with the shp2pgsql-program.
> 
> My main access need is to get any 1x1 (or 2x2) km block from the whole
> area. That is the window size that is shown to the user in my application
> that will act as a client to the PostgreSQL via libpq. That viewarea
> contains less than 500 shapes, i.e. records returned from the query.
> 
> The computer running the app will be a PC with 300MHz CPU and 128M of RAM.
> The main road network table (+ GiST index) will be only built once in my
> development PC (fortunately a 1.7G P4 w 512M RAM) and I will only read the
> table at runtime, never modify it in anyway.
> 
> Now, what is your gut feeling people, how fast query responses can I
> expect? Will I get a response under one second? The application should have
> a reasonable response time when user changes the window location, so 1 sec
> is pretty much the top that I can stand.
> 
> Any tips that I should know when optimising the query performance...?
> 
> - Petri
> 
> PS. I have a possibility (though undesirable) of increasing the CPU speed,
> but not really the RAM.
> PPS. Note that I only need very optimized query speed, the insertion of
> data in beginning can take as much time as needed, I'll only do it once anyway.
> <><><><><><><><>
>    Petri J. Riipinen
> petri.riipinen at nic.fi
> <><><><><><><><>
> 
> 
> To unsubscribe from this group, send an email to:
> postgis-unsubscribe at yahoogroups.com
> 
> 
> 
> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/

To unsubscribe from this group, send an email to:
postgis-unsubscribe at yahoogroups.com

 

Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/