Re: Reverse collations (initially for making keyset pagination covermore cases)

On Sun, Nov 17, 2019 at 11:23:23PM +0000, Andrew Gierth wrote:
> >>>>> "David" == David Fetter <david@fetter.org> writes:
> 
> First, in testing the patch I found there were indeed some missing
> cases: the sortsupport version of the comparator needs to be fixed too.
> I attach a draft addition to your patch, you should probably look at
> adding test cases that need this to work.
> 
>  David> (a, b, c) < ($1, $2 COLLATE "C_backwards", $3)
>  David> ...
>  David> ORDER BY a, b DESC, c
> 
> That would have to be:
> 
>  WHERE (a, b COLLATE "C_backwards", c) < ($1, $2, $3)
>  ...
>  ORDER BY a, b COLLATE "C_backwards", c
> 
> Adding the below patch to yours, I can get this on the regression test
> db (note that this is a -O0 asserts build, timings may be slow relative
> to a production build):
> 
> create collation "C_rev" ( LOCALE = "C", REVERSE = true );
> create index on tenk1 (hundred, (stringu1::text collate "C_rev"), string4);
> 
> explain analyze
>   select hundred, stringu1::text, string4
>     from tenk1
>    where (hundred, stringu1::text COLLATE "C_rev", string4)
>            > (10, 'WKAAAA', 'VVVVxx')
>    order by hundred, (stringu1::text collate "C_rev"), string4
>    limit 5;
>                                                                        QUERY PLAN
                                 
 
>
--------------------------------------------------------------------------------------------------------------------------------------------------------
>  Limit  (cost=0.29..1.28 rows=5 width=132) (actual time=0.029..0.038 rows=5 loops=1)
>    ->  Index Scan using tenk1_hundred_stringu1_string4_idx on tenk1  (cost=0.29..1768.49 rows=8900 width=132) (actual
time=0.028..0.036rows=5 loops=1)
 
>          Index Cond: (ROW(hundred, ((stringu1)::text)::text, string4) > ROW(10, 'WKAAAA'::text, 'VVVVxx'::name))
>  Planning Time: 0.225 ms
>  Execution Time: 0.072 ms
> (5 rows)
> 
> and I checked the results, and they look correct now.

Here's that patch with your correction rolled in.

This will need more tests, and possibly more documentation.

Best,
David.
-- 
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate