[postgis-users] Improving Performance

[Ralph Mason]

I think it wouldn't be to difficult to benchmark the bounding box being 
floats / doubles / gone altogether.  The optimum setting is likely to be 
based on the actual machine it's running on.  I wouldn't be surprised if 
no bounding box was actually the fastest on a modern fast processor with 
a good sized cache.

 It seems the space savings could be significant ( probably 40% when you 
consider also an extra 8 bytes in each point in the geometry).  And with 
Disk access followed by memory access being the main bottle necks 
anything to reduce the volume of data has to have a beneficial effect on 
performance.

I don't think it needs to lead to a proliferation of types.  Just 
another type geometry_2d or something like that.  I am also in favor or 
removing the projection, so that functions working with 2d geometrys 
don't need to consider it. 

It would be interesting to know for sure, but I suspect that most users 
of postgis are using 2d geometries and all their data is in one 
projection.  Meaning that a faster smaller 2d type would probably make 
up the bulk of the use, with the full geometry being used to massage 
data into the smaller type.

Anyway I am keen to support any effort, with time and or a donation.

Ralph



David Blasby wrote:

> 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
>
>