[postgis-users] st_equals strangeness

[Paragon Corporation]

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: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] 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: postgis-users-bounces at postgis.refractions.net
> [mailto:postgis-users-bounces at postgis.refractions.net] 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: postgis-users-bounces at postgis.refractions.net
> > [mailto:postgis-users-bounces at postgis.refractions.net] 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 
> > <mark.cave-ayland at siriusit.co.uk>  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 postgis-users at postgis.refractions.net
> >> http://postgis.refractions.net/mailman/listinfo/postgis-users
> >>
> >>
> > _______________________________________________
> > postgis-users mailing list postgis-users at postgis.refractions.net
> > http://postgis.refractions.net/mailman/listinfo/postgis-users
> >
> >
> >
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users at postgis.refractions.net
> > http://postgis.refractions.net/mailman/listinfo/postgis-users
> >
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users