Re: B-Tree support function number 3 (strxfrm() optimization)

On 04/07/2014 11:35 PM, Peter Geoghegan wrote:
> Okay. Here is a worst-case, with the pgbench script the same as my
> original test-case, but with much almost maximally unsympathetic data
> to sort:
>
> [local]/postgres=# update customers set firstname =
> 'padding-padding-padding-padding' || firstname;

Hmm. I would expect the worst case to be where the strxfrm is not
helping because all the entries have the same prefix, but the actual key
is as short and cheap-to-compare as possible. So the padding should be
as short as possible. Also, we have a fast path for pre-sorted input,
which reduces the number of comparisons performed; that will make the
strxfrm overhead more significant.

I'm getting about 2x slowdown on this test case:

create table sorttest (t text);
insert into sorttest select 'foobarfo' || (g) || repeat('a', 75) from
generate_series(10000, 30000) g;

explain analyze select * from sorttest order by t;

Now, you can argue that that's acceptable because it's such a special
case, but if we're looking for the worst-case..

(BTW, IMHO it's way too late to do this for 9.4)

- Heikki