Message-ID: <54EF1045.5040609@tbz-pariv.de>
Date: Thu, 26 Feb 2015 13:23:33 +0100
From: "Sven R. Kunze" <srkunze@tbz-pariv.de>
Organization: TBZ-PARIV GmbH
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
 rv:31.0) Gecko/20100101 Thunderbird/31.4.0
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: Reverse Key Index
References: <54DF9036.8080502@tbz-pariv.de> <7978.1423937899@sss.pgh.pa.us>
 <54DF9559.8080109@tbz-pariv.de>
 <WM!ac1d64cef832673f1f6089d7c26e717d0c09326c990d42e4ac6c27c98e44612c4261de1a99f3eaf40ab4ae5310bbe616!@asav-1.01.com>
 <54EE4D45.1060704@agliodbs.com> <54EEFDA1.7020609@tbz-pariv.de>
 <mcn11a$lf8$1@ger.gmane.org>
In-Reply-To: <mcn11a$lf8$1@ger.gmane.org>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: quoted-printable
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

On 26.02.2015 12:45, Thomas Kellerer wrote:
> Sven R. Kunze schrieb am 26.02.2015 um 12:04:
>> I just thought about btree indexes here mainly because they well-known a=
nd well-used in ORM frameworks.
> If your ORM framework needs to know about the internals of an index defin=
ition or even requires a certain index type, then you should ditch that ORM=
 framework.

As I said "Considering the documentation and third-party posts on GiST=20
and btree_gist, at least to me, it seems as if people would not want to=20
use that for integers; which in turn is the main use-case scenario for=20
reverse key indexes."

> Apart from indexes supporting business constraints (e.g. a unique index) =
neither the application nor the the ORM framework should care about indexes=
 at all.

Well, the world is not perfect:=20
http://www.joelonsoftware.com/articles/LeakyAbstractions.html

>> does PostgreSQL support the concept of reverse key indexing as described=
 here?
> The real question is: why do you think you need such an index?
> Do you have any performance problems with the existing BTree index? If ye=
s, which problem exactly?
>

This is not the real question. I never said I personally have to solve=20
issue around that. If so, I would have provide more detailed information=20
on the issue.

However, I clearly see benefits of Oracle's solution over "You could get=20
the effect easily enough with an expression index on a byte-reversing=20
function. A related thing that people often do is create an index on a=20
hash function."

These benefits, I described here: "On the other hand, I see potential=20
when it comes to applications which use PostgreSQL. There, programmers=20
would have to change a lot of code to tweak existing (and more=20
importantly working) queries to hash/reverse an id column first. Using=20
ORMs would make this change even more painful and maybe even impossible."


So, this discussion is more about what can PostgreSQL offer in=20
comparison to already existing solutions. I perfectly see Tom's proposal=20
as a as-is solution but it has the drawbacks described above.


If you think Reverse Key Indexes have no usage here in PostgreSQL, you=20
should not support convenience features for easily improving performance=20
without breaking the querying API or you won't have any intentions to=20
include such a patch, just let me know and we can close the issue=20
immediately.

--=20
Sven R. Kunze
TBZ-PARIV GmbH, Bernsdorfer Str. 210-212, 09126 Chemnitz
Tel: +49 (0)371 33714721, Fax: +49 (0)371 5347920
e-mail: srkunze@tbz-pariv.de
web: www.tbz-pariv.de

Gesch=C3=A4ftsf=C3=BChrer: Dr. Reiner Wohlgemuth
Sitz der Gesellschaft: Chemnitz
Registergericht: Chemnitz HRB 8543


--=20
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance