[postgis-users] Improving Performance

[David Blasby]

colin> We were wondering why a sample 200MB text file of 8
colin> million points ballooned into over a gigabyte when

Your text file is using about 26 bytes/point.

WKB representation (in posgresql) would be 29 bytes/point.
If you want, you can create this now with "CREATE TABLE myWKB AS
SELECT asBinary(the_geom), ... FROM ...". Unfortunately there isnt
much support wrapped around the WKB type at the momenet.

The current postgis implementation is about 114 bytes/point (thats
why you're seeing 5* larger size in postgresql).

By taking the redundant info (56 bytes) out of the postgis type,
it would be about 58 bytes.

redunandant info: BVOL.zmin (8 bytes), BVOL.zmax (8 bytes),
                   offsetX (8 bytes), offsetY (8 bytes), scale (8 bytes),
                   convert the bounding box to float32s (16 bytes).

** I'm not sure if its a good idea to convert the bounding box to 
float32s.  We might be able to remove the entire bounding box, but
I'm not sure how that would affect performance.

I'm a bit wary of using a bunch of different types for different 
"geometric things" in the database. There's a high cost associated with 
getting a type up and running, indexable, and convertable to and from 
the GEOMETRY type.  Thats why I recommended a full WKB type since it can 
represent any geometry relatively efficiently.  Because there already
GEOMETRY <-> WKB functions, we can add all the geometric functions to 
the type automatically.

dave