--- "Boehm, Andreas" <[EMAIL PROTECTED]> 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.
As I understand it, the geometry index is essentially a bounding box of the feature, so has limited advantages on point features compared with line & polygon ones. But I could be wrong ablout this :-) Tiling the trees allows an integer index to quickly restrict the tree geom index search to only a few tens of thousands of trees. I have got orders of magnitude performance boosts from this approach, both via tiling & table partitioning. I believe there have been indexing performance enhancements to very recent versions, which I have not tested. My datasets max out at a few hundred million records, so I can't guarantee they'll scale up to yours, but it seems likely. Cheers, Brent > > --- 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 > > --- > > P.S. Here are the explain analyses: > > EXPLAIN ANALYZE > SELECT * FROM land_parcel WHERE lp_id = 123456; > > Index Scan using land_parcel_pkey on land_parcel (cost=0.00..9.25 > rows=1 width=342) (actual time=0.016..0.017 rows=1 loops=1) > Index Cond: (lp_id = 123456) > Total runtime: 0.045 ms > > --- > > EXPLAIN ANALYSE > SELECT x(geom), y(geom) > FROM trees > WHERE geom && (SELECT geom FROM land_parcel WHERE lp_id = 123456); > > Bitmap Heap Scan on trees (cost=108.73..5063.01 rows=1250 width=21) > (actual time=716.319..717.002 rows=18 loops=1) > Filter: (geom && $0) > InitPlan > -> Index Scan using land_parcel_pkey on land_parcel > (cost=0.00..9.25 rows=1 width=317) (actual time=0.096..0.098 rows=1 > loops=1) > Index Cond: (lp_id = 123456) > -> Bitmap Index Scan on trees_index (cost=0.00..99.16 rows=1250 > width=0) (actual time=716.176..716.176 rows=18 loops=1) > Index Cond: (geom && $0) > Total runtime: 717.082 ms > > --- > > EXPLAIN ANALYZE > select x(t.geom), y(t.geom) > from land_parcel as l, trees as t > where (l.lp_id = 123456) > and (l.geom && t.geom) and Contains(l.geom, t.geom) > > Nested Loop (cost=99.47..5072.48 rows=19 width=21) (actual > time=722.931..723.639 rows=16 loops=1) > Join Filter: contains(l.geom, t.geom) > -> Index Scan using land_parcel_pkey on land_parcel l > (cost=0.00..9.25 rows=1 width=317) (actual time=0.205..0.208 rows=1 > loops=1) > Index Cond: (lp_id = 123456) > -> Bitmap Heap Scan on trees t (cost=99.47..5044.38 rows=1250 > width=21) (actual time=722.541..723.197 rows=18 loops=1) > Filter: (l.geom && t.geom) > -> Bitmap Index Scan on trees_index (cost=0.00..99.16 > rows=1250 width=0) (actual time=722.417..722.417 rows=18 loops=1) > Index Cond: (l.geom && t.geom) > Total runtime: 723.759 ms > > _______________________________________________ > postgis-users mailing list > [email protected] > http://postgis.refractions.net/mailman/listinfo/postgis-users > _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
