Re: [HACKERS] Secondary index access optimizations

From: Antonin Houska <ah(at)cybertec(dot)at>
To: Konstantin Knizhnik <k(dot)knizhnik(at)postgrespro(dot)ru>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: [HACKERS] Secondary index access optimizations
Date: 2018-01-11 09:34:42
Message-ID: 30724.1515663282@localhost
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

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]';
>> ?column?
>> ----------
>> t
>> (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.

Antonin Houska
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26, A-2700 Wiener Neustadt

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Amit Langote 2018-01-11 09:52:09 Re: [Sender Address Forgery]Re: [HACKERS] path toward faster partition pruning
Previous Message Kyotaro HORIGUCHI 2018-01-11 08:08:39 Re: [HACKERS] asynchronous execution