Oh Stan, Please don't tell me you are running PostgreSQL 3.5. W that the Berkeley age?
You really need to upgrade now. I know its hard and scary but its for your own good :) Thanks, Regina -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Sufficool, Stanley Sent: Friday, January 08, 2010 4:49 PM To: 'PostGIS Users Discussion' Subject: Re: [postgis-users] st_equals strangeness The internal storage for the geometry is 64bit though right? So if the BBOX was moved to double precision (64bit) there would be no type conversion between 64/32 bits and so no truncation/rounding. As far as slowdown. I've run some comparisons using PostgreSQL 3.5 between real/double precision and btree indexing and found 1.4 slower per 1,000,001 rows difference for selects, 1 second FASTER (strange) for 64 bit index creation with 1,000,001 existing rows. 2.1 seconds slowdown for 300,001 inserts with index in place. Would this be 2 times for the BBOX? I'm not sure if GiST would be much different than btree speeds, I don't have a test case for this. /* Run against Windows 2003 AMD 64 bit running PostgreSQL 3.5 */ CREATE TABLE indexed_64 (myfield float); -- inserts without an index insert into indexed_64 select random() from generate_series(0, 10000000); -- Query returned successfully: 10000001 rows affected, 41563 ms execution time. create index my_index on indexed_64 using gist(myfield); -- ERROR: data type double precision has no default operator class for access method "gist" create index my_index on indexed_64 using btree(myfield); -- Query returned successfully with no result in 52390 ms. select count(*) from indexed_64 where myfield between 0 and .5 -- result: 4999544 in 4937 ms truncate table indexed_64; -- inserts WITH an index insert into indexed_64 select random() from generate_series(0, 300000); -- Query returned successfully: 300001 rows affected, 5422 ms execution time. CREATE TABLE indexed_32 (myfield real); insert into indexed_32 select random() from generate_series(0, 10000000); -- Query returned successfully: 10000001 rows affected, 45000 ms execution time. /* TOOK LONGER THAN 64bit - STRANGE */ create index my_index on indexed_32 using gist(myfield); -- ERROR: data type double precision has no default operator class for access method "gist" create index my_index_32 on indexed_32 using btree(myfield); -- Query returned successfully with no result in 51359 ms. /* FASTER INDEX CREATION TIME */ select count(*) from indexed_32 where myfield between 0 and .5 -- result: 5001551 in 3563 ms truncate table indexed_32; -- inserts WITH an index insert into indexed_32 select random() from generate_series(0, 300000); -- Query returned successfully: 300001 rows affected, 3516 ms execution time. > -----Original Message----- > From: [email protected] > [mailto:[email protected]] On Behalf Of > Jan Hartmann > Sent: Friday, January 08, 2010 1:26 PM > To: PostGIS Users Discussion > Cc: 'PostGIS Development Discussion' > Subject: Re: [postgis-users] st_equals strangeness > > > I agree. 64 bits testing doesn't test on equality, it just tests for a > smaller rounding error. > > Jan > > On 8-1-2010 21:25, Paragon Corporation wrote: > > Paul, > > Huh. What do you want to change = to? A real =? > > > > If you do remember for better or worse it will have impact > on -- GROUP > > BY, ORDER BY > > > > as the = operator I can only guess is baked in deeply into the > > PostgreSQL implementation of these (not sure how though). > > > > So if you ever change it to a true equality operator, I > suspect these > > things may become as slow as molasses, and well I see no point of > > making it a 64-bit check because the only reason is to make > it more of > > a true equality which it isn't anyway. People should just > get out of > > that mindset. > > > > Thanks, > > Regina > > > > > > > > -----Original Message----- > > From: [email protected] > > [mailto:[email protected]] On Behalf Of > > Paul Ramsey > > Sent: Friday, January 08, 2010 12:51 PM > > To: PostGIS Users Discussion > > Subject: Re: [postgis-users] st_equals strangeness > > > > Well, that brings us to the API level, no? Because operators have > > meanings in index (32-bit) terms. Should all our operators > have 64-bit > > rechecks? Should some of them? Should '=' just get a special > > treatment, because we have such an in-grained understanding of what > > that symbol connotes, and other symbols be left as pure index ops? > > > > P > > > > On Fri, Jan 8, 2010 at 9:44 AM, Mark Cave-Ayland > > <[email protected]> wrote: > > > >> Paul Ramsey wrote: > >> > >> > >>> More to the point, it's a float32 box, not a double64 > box. Your two > >>> points are in fact different, waaaaaay down deep into the > precision > >>> of their 64-bit double coordinates. So deep in fact that > the human > >>> readable decimal representations don't show it (look at > the hexewkb > >>> output, and you'll see the differences, you should be able to do > >>> that, points are small enough to eyeball in hex). > >>> > >>> So when the 32-bit box is extracted from the 64-bit doubles, the > >>> points are identical at that level of precision and = > returns true, > >>> while st_equals working against the doubles does not. > >>> > >>> P > >>> > >> Again, another demonstration as to why BOX2DFLOAT4s should > never be > >> used for calculations and only for internal "within" checks :( > >> > >> > >> ATB, > >> > >> Mark. > >> > >> -- > >> Mark Cave-Ayland - Senior Technical Architect PostgreSQL - PostGIS > >> Sirius Corporation plc - control through freedom > >> http://www.siriusit.co.uk > >> t: +44 870 608 0063 > >> > >> Sirius Labs: http://www.siriusit.co.uk/labs > >> _______________________________________________ > >> 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 > > > > > > > > _______________________________________________ > > 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 > _______________________________________________ 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
