Re: Abbreviated keys for Numeric (was: Re: B-Tree support function number 3 (strxfrm() optimization))

Hi,

On 26.1.2015 17:43, Andrew Gierth wrote:
> Another spinoff from the abbreviation discussion. Peter Geoghegan
> suggested on IRC that numeric would benefit from abbreviation, and
> indeed it does (in some cases by a factor of about 6-7x or more, because
> numeric comparison is no speed demon).
>
> This patch abbreviates numerics to a weight+initial digits
> representation (the details differ slightly between 32bit and 64bit
> builds to make the best use of the available bits).
>
> On 32bit, numeric values that are between about 10^-44 and 10^83, and
> which differ either in order of magnitude or in the leading 7
> significant decimal digits (not base-10000 digits, single decimals) will
> get distinct abbreviations. On 64bit the range is 10^-176 to 10^332 and
> the first 4 base-10000 digits are kept, thus comparing 13 to 16 decimal
> digits. This is expected to be ample for applications using numeric to
> store numbers; applications that store things in numeric that aren't
> actually numbers might not see the benefit, but I have not found any
> detectable slowdown from the patch even on constructed pathological
> data.

I've done some testing on this (along with the other patch doing the
same with Datum values), but I'm yet to see a query that actually
benefits from this.

For example with the same percentile_disc() test as in the other thread:

create table stuff as select random()::numeric as randnum
from generate_series(1,1000000);

analyze stuff;

select percentile_disc(0) within group (order by randnum) from stuff;

I get pretty much no difference in runtimes (not even for the smallest
dataset, where the Datum patch speedup was significant).

What am I doing wrong?

--
Tomas Vondra http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services