[postgis-users] st_equals strangeness

Fri Jan 8 14:15:22 PST 2010

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