From: Sven R. Kunze <srkunze@tbz-pariv.de>
To: pgsql-performance@postgresql.org
Subject: Re: Reverse Key Index
Date: Thu, 26 Feb 2015 13:23:33 +0100
Message-ID: <54EF1045.5040609@tbz-pariv.de> (raw)
In-Reply-To: <mcn11a$lf8$1@ger.gmane.org>
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>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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 and well-used in ORM frameworks.
> If your ORM framework needs to know about the internals of an index definition 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
and btree_gist, at least to me, it seems as if people would not want to
use that for integers; which in turn is the main use-case scenario for
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:
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 yes, which problem exactly?
>
This is not the real question. I never said I personally have to solve
issue around that. If so, I would have provide more detailed information
on the issue.
However, I clearly see benefits of Oracle's solution over "You could get
the effect easily enough with an expression index on a byte-reversing
function. A related thing that people often do is create an index on a
hash function."
These benefits, I described here: "On the other hand, I see potential
when it comes to applications which use PostgreSQL. There, programmers
would have to change a lot of code to tweak existing (and more
importantly working) queries to hash/reverse an id column first. Using
ORMs would make this change even more painful and maybe even impossible."
So, this discussion is more about what can PostgreSQL offer in
comparison to already existing solutions. I perfectly see Tom's proposal
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
should not support convenience features for easily improving performance
without breaking the querying API or you won't have any intentions to
include such a patch, just let me know and we can close the issue
immediately.
--
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äftsführer: Dr. Reiner Wohlgemuth
Sitz der Gesellschaft: Chemnitz
Registergericht: Chemnitz HRB 8543
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: srkunze@tbz-pariv.de
Subject: Re: Reverse Key Index
In-Reply-To: <54EF1045.5040609@tbz-pariv.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox