[postgis-devel] Doublebox does not look good

[Paragon Corporation]

I redid my tests on doublebox spike for the 3rd time.  This time with pseudo
real data.  I was puzzled by my second batch of tests why double box didn't
look convincingly different from trunk.  Then I realized my test specimen
was contaminated and I wasn't running double box at all the second time
around when I checked the memsizes and noticed they were the same.
 
This time, Double box is clearly worse at least on my 9.1 windows install.
I couldn't load my full real dataset cause its too big so I took a random
sampling of 250 polygons, 10,000 linestrings and 100,000 points for this
test.

One observation is the caching effects of double box seem worse, but
probably because I have my shared mem etc at default states.
 
Here is what I have:

 
-- 0 records 
-- r8305 after restart 9.1.2: 91ms, 20ms, 31 ms, 21 ms
-- after fresh restart 9.1.2 dbox: 51 ms, 41ms
-- after fresh restart 9.1.2 dbox: 234ms, 46ms, 63 ms
-- after 9.1.2 r8305: 78 ms, 31ms
SELECT p.gid, COUNT(pt.gid) As num_pts 
 FROM test.poly_4326 As p INNER JOIN test.point_4326 As pt 
    ON (p.geom && pt.geom)
    GROUP BY p.gid;
-- r8305 -- 0 records
-- dbox after restart 9.1.2: 51 ms,41ms, 41ms
-- dbox after restart 9.1.2: 109 ms,63ms, 62ms
-- r8305 after restart 9.1.2: 78ms, 62ms, 47ms
-- r8305 9.1.2: 80ms, 62ms, 31ms
SELECT p.gid As poly_gid, COUNT(pt.gid) As num_pts 
 FROM test.poly_3785 As p INNER JOIN test.point_3785 As pt 
    ON (p.geom && pt.geom)
    GROUP BY p.gid;

-- 817 rows r8305: 792 ms, 811 ms, 783 ms, 772 ms
--dbox 9.1.2 fresh restart: 1181ms, 1201 ms,1172 ms, 1181 ms
--dbox 9.1.2 fresh restart: 1638ms, 1139ms, 1139ms, 1248 ms
--r8305 9.1.2: 998ms,826 ms, 858ms, 780 ms,765ms
-- r8305 9.1.2: 1370 ms,891 ms, 911ms, 891 ms,891 ms (db load running at
time)
SELECT p.gid, COUNT(pt.gid) As num_pts 
 FROM test.line_4326 As p INNER JOIN test.point_4326 As pt 
    ON (p.geom && pt.geom)
    GROUP BY p.gid;
    
-- 818 rows r8305 - 9.1.1 : 922 ms, 884 ms, 881 ms, 854 ms
-- dbox 9.1.2: 1322 ms,1322 ms, 1304 ms, 1311 ms
-- dbox 9.1.2 fresh restart: 1560 ms, 1311ms
-- r8305 9.1.2: 1092 ms,827ms, 858 ms, 842 ms
-- r8305 9.1.2: 1432 ms,994ms, 981 ms, 991 ms (db load running at time)
SELECT p.gid As poly_gid, COUNT(pt.gid) As num_pts 
 FROM test.line_3785 As p INNER JOIN test.point_3785 As pt 
    ON (p.geom && pt.geom)
    GROUP BY p.gid;

-- 0 rows r8305 9.1.1: 891 ms, 971 ms, 941 ms, 921 ms
-- 0 rows dbox: 1361ms,1351ms,1341ms
-- 0 rows dbox after restart: 1264ms,1263ms, 1311ms 
-- 0 rows r8305 9.1.2: 873 ms, 952 ms,905 ms, 874 ms, 843 ms
-- 0 rows r8305 9.1.2: 911 ms, 914 ms,921 ms, 874 ms, 911 ms 
SELECT p.gid , COUNT(pt.gid) As num_pts 
 FROM test.line_3785 As p INNER JOIN test.point_3785 As pt 
    ON ST_Intersects(p.geom,pt.geom)
    GROUP BY p.gid;

-- 0 rows r8305 9.1.1: 12 ms, 10 ms, 11 ms, 11 ms
-- dbox 9.1.2: 11ms, 11ms, 12ms
-- dbox restart 9.1.2: 11ms, 11ms, 12ms
-- r8305 9.1.2: 15ms, 31 ms, 16ms, 15ms
-- r8305 9.1.2: 10ms, 11ms, 11ms
SELECT p.gid , COUNT(pt.gid) As num_pts 
 FROM test.poly_3785 As p INNER JOIN test.line_3785 As pt 
    ON (p.geom && pt.geom)
    GROUP BY p.gid;

Thanks,
Regina