|From:||Konstantin Knizhnik <k(dot)knizhnik(at)postgrespro(dot)ru>|
|Subject:||Re: [HACKERS] Secondary index access optimizations|
|Views:||Raw Message | Whole Thread | Download mbox|
On 11.01.2018 12:34, Antonin Houska wrote:
> Konstantin Knizhnik <k(dot)knizhnik(at)postgrespro(dot)ru> wrote:
>> On 09.01.2018 19:48, Antonin Houska wrote:
>>> Have you considered using the range types (internally in
>>> operator_predicate_proof()) instead of hard-coding operator OIDs? The range
>>> types do have the knowledge that k < 20001 and k <= 20000 are equivalent for
>>> integer type:
>>> postgres=# SELECT int4range '(, 20001)' = int4range '(, 20000]';
>>> (1 row)
>> It is bright idea, but it is not quit clear to me how to implement it.
>> There are several builtin ranges types in Postgres: int4range, int8range, numrange, tsrange, tstzrange, daterange.
>> Among them int4range, int8range and daterange are discrete types having canonical function, for which this transformation rules are applicable.
>> Now I perform checks for all this types. So the challenge is to support user defined range types with canonical function.
>> As input operator_predicate_proof function has Oid of comparison operator and Const * expression representing literal value.
>> So I see the following generic way of checking equivalence of ranges:
>> 1. Get name of operator. If it is '<=' or '>=' then it is closed interval, if it is '<' or '>' then it is open interval.
>> 2. Convert Const to text (using type's out function) and construct interval: '(,"$value"]' for '<=', '["$value",)' for '>=', '(,"$value")' for '<' and '("$value",)' for '>'.
>> 3. Find range type from type of the constant:
>> select * from pg_range where rngsubtype=?;
>> 4. Try to cast constructed above string to this range type (using type's in function).
>> 5. Compare two produced ranges and if them are equal, then operator_predicate_proof should return true.
> I haven't thought that much about details, so just one comment: you shouldn't
> need the conversion to text and back to binary form. utils/adt/rangetypes.c
> contains constructors that accept the binary values.
Attached please find new version of the patch which uses range type to
interval matching in operator_predicate_proof function.
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
|Next Message||Chapman Flack||2018-01-11 12:26:42||Re: numeric regression test passes, but why?|
|Previous Message||Teodor Sigaev||2018-01-11 11:56:50||Re: CUBE seems a bit confused about ORDER BY|