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

From: Andrew Gierth <andrew(at)tao11(dot)riddles(dot)org(dot)uk>
To: David Fetter <david(at)fetter(dot)org>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, PostgreSQL Development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Reverse collations (initially for making keyset pagination cover more cases)
Date: 2019-11-17 23:23:23
Message-ID: 87v9ri14pw.fsf@news-spur.riddles.org.uk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

>>>>> "David" == David Fetter <david(at)fetter(dot)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.036 rows=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.

--
Andrew (irc:RhodiumToad)

Attachment Content-Type Size
revcoll_x.patch text/x-patch 1.6 KB

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Alvaro Herrera 2019-11-18 00:49:24 Re: Invisible PROMPT2
Previous Message David Fetter 2019-11-17 21:54:26 Re: Reverse collations (initially for making keyset pagination cover more cases)