[postgis-users] st_equals strangeness

Fri Jan 8 13:48:48 PST 2010

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
>