Oooops, typo. I don't know where I got that version from. SELECT VERSION(); PostgreSQL 8.3.7, compiled by Visual C++ build 1400
> -----Original Message----- > From: [email protected] > [mailto:[email protected]] On > Behalf Of Paragon Corporation > Sent: Friday, January 08, 2010 2:07 PM > To: 'PostGIS Users Discussion' > Subject: Re: [postgis-users] st_equals strangeness > > > 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 > _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
