[postgis-users] Performace problems with 250.000.000 points

[Mark Cave-Ayland]

On Wed, 2007-07-25 at 20:42 +0200, Boehm, Andreas wrote:
> Hi Mark, Patricio, Brent and Paul (and all the others of course)
> 
> thanks for your answers.
> 
> --- Mark wrote: 
> > SELECT * FROM trees WHERE geom && (SELECT geom FROM 
> > land_parcel WHERE lp_id = 123456);
> There's quite no difference. Please refer to the explain analyses at the
> end of this mail.
> 
> --- Patricio wrote:
> > Aplicate case box3d
> Should I realy add bounding boxes for points? Could it be faster testing
> the box than testing the point? Or does it just blow up the trees table?
> 
> --- Brent wrote:
> > speed up by tiling your trees into (for example) 10000 "boxes"
> I have not implemented it yet. But isn't it the way the geometry index
> is for? The index should also reduce the trees to be tested.
> 
> --- Paul wrote:
> > use
> > shared_buffers = 1024MB
> > effective_cache_size = 1024MB
> > random_page_cost = 3
> It sounds silly, but these parameters even slow it down. With this
> configuration Posgres uses much more memory. Starting your query you can
> see the mem usage raising. But at the end the results aren't faster than
> the original settings.
> I changed the testing a bit. Now I read 100 land_parcels uniformly
> distributed over the database. For each land_parcel I additionally read
> the next 10 land_parcels. These neighbours return very quickly, off
> course.
> 
> Thanks
> Andreas


Hi Andreas,

I'm not quite sure what you mean about these queries being slow - in
your first email you mentioned execution times of 300s and 700s - did
you mean ms, as this is what the EXPLAIN ANALYZE shows?

If so, you may be able to get something a bit faster by pulling up your
land_parcel geometry out of the WHERE clause. Does the following perform
any better for you?


select x(t.geom), y(t.geom) 
from (SELECT geom FROM land_parcel WHERE lp_id = 123456 ORDER BY lp_id
LIMIT 1) as l, trees as t 
where l.geom && t.geom and Contains(l.geom, t.geom);

(Note the ORDER BY and LIMIT are "hints" to the planner that only one
row will be returned for that part of the query)


ATB,

Mark.

-- 
ILande - Open Source Consultancy
http://www.ilande.co.uk