[postgis-devel] Re: LW_GEOM questions

[David Blasby]

ralph.mason wrote:


> I am glad to hear that the indexing is working  from results it sounds like the bounding boxes will need to be stored.  I would expect that both LWGEOM and GEOMETRY should perform about the same until the memory wall is hit (much sooner with GEOMERTY).

I'm going to experiment with this.

1. Use the last remaining bit of the geometry's type to say "there's a 
BOX2DFLOAT4" comming up.  This is a pretty major change!
2. provide a "addBBOX(lwgeom)" function that will stick one in (defaults 
to no).
3. change indexing functions to be aware of the bounding box.

I've noticed that index searches for lwgeoms are very slightly slower 
compared to postgis index searches (up to 1ms).  This is most likely 
because (1) it has to compute the bounding box and the postgis version 
already has one available and (2) the actual index tree is somewhat 
different between the two.

For queries like:

SELECT * FROM <table> WHERE lwgeom && <geom>;

you will not notice any speed degregation (1ms is too short)!  In fact, 
you'll probably find its faster because the index and actual tuple data 
will come in faster (less disk pages to read).

For queries like:

SELECT ... FROM <table1>,<table2> WHERE table1.lwgeom && table2.lwgeom;

there is a noticable speed degregation:
1,000  rows -- about 300ms    (0.3 sec)
10,000 rows -- about 18,000ms (18  secs)

In order to get this going faster, you would first attach the bounding 
box to the lwgeoms.  (ie. "UPDATE table1 SET lwgeom = addBBOX(lwgeom)").

I'll experiment and see how much it improves performance...

dave