Re: indexes on float8 vs integer

On Sat, Jul 11, 2009 at 10:15 PM, Dennis Gearon<gearond(at)sbcglobal(dot)net> wrote:
>
> Anyone got any insight or experience in the speed and size of indexes on Integer(4 byte) vs float (8byte). For a project that I'm on, I'm contemplating using an integer for:
>
>     Latitude
>     Longitude
>
> In a huge, publically searchable table.
>
> In the INSERTS, the representation would be equal to:
>
>     IntegerLatOrLong = to_integer( float8LatOrLong * to_float(1000000) );
>
> This would keep it in a smaller (4 bytes vs 8 byte) representation with simple numeric comparison for indexing values while still provide 6 decimals of precision, i.e. 4.25 inches of resolution, what google mapes provides.
>
> I am expecting this table to be very huge. Hey, I want to be the next 'portal' :-)
> Dennis Gearon

Well, floats can be bad if you need exact math or matching anyway, and
math on them is generally slower than int math. OTOH, you could look
into numeric to see if it does what you want. Used to be way slower
than int, but in recent versions of pgsql it's gotten much faster.
Numeric is exact, where float is approximate, so if having exact
values be stored is important, then either using int and treating it
like fixed point, or using numeric is usually better.