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 _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
