Re: Reverse Key Index - Mailing list pgsql-performance

From Sven R. Kunze
Subject Re: Reverse Key Index
Date
Msg-id 54EF1045.5040609@tbz-pariv.de
Whole thread Raw
In response to Re: Reverse Key Index  (Thomas Kellerer <spam_eater@gmx.net>)
Responses Re: Reverse Key Index  (Thomas Kellerer <spam_eater@gmx.net>)
List pgsql-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,
thenyou 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
frameworkshould 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



pgsql-performance by date:

Previous
From: Thomas Kellerer
Date:
Subject: Re: Reverse Key Index
Next
From: Heikki Linnakangas
Date:
Subject: Re: Reverse Key Index