[postgis-devel] Doublebox does not look good

[Brian Hamlin]

On Dec 6, 2011, at 2:46 AM, Paragon Corporation wrote:

> 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.
>

I have never been a stats person, but I have played the tester in  
software development
with that said
  these jumps in times from one run to another, both dbox and non-dbox
  say to me that there are dimensions of the runtime that is not  
being isolated.
  "cold" and "not-cold" runs are not enough to explain it, I surmise  
without substantiation...

I asked on #postgresql actually, and at least andre said that some  
tests could be looked at
to characterize what is going on.. from a postgres engine point of  
view.. or, lets say
there was a sense of a door open to that, in his response.. The pg  
team seems pretty active
right now, both in 9.1 cleanups and 9.2 plus whatever else

I feel my jumpy times are vindicated, too. (though I dont follow  
exactly these test results)

lastly, I am not sure how much time I can put in, but I continue to  
follow this thread
I think I can help some.. it seems important


> 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
>
>
> _______________________________________________
> postgis-devel mailing list
> postgis-devel at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-devel
>