From: Sven R. Kunze <srkunze@tbz-pariv.de>
To: pgsql-performance@postgresql.org
Subject: Re: Reverse Key Index
Date: Thu, 26 Feb 2015 14:20:48 +0100
Message-ID: <54EF1DB0.3060304@tbz-pariv.de> (raw)
In-Reply-To: <mcn4m4$o9b$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>
<54EF1045.5040609@tbz-pariv.de>
<mcn4m4$o9b$1@ger.gmane.org>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
On 26.02.2015 13:48, Thomas Kellerer wrote:
> Sven R. Kunze schrieb am 26.02.2015 um 13:23:
>> 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
Sorry for my bad English: The if-clause ends with "just let me know and
we can close the issue immediately." You quoted an or'ed if-part.
Point was, if you see no benefits or you have no intention to include it
anyway (patch provided or not), we can stop now. I am not married to
this features and right now I can live without it.
> It's also unclear to me which "performance" you are referring to.
> Insert performance? Retrieval performance? Concurrency?
>
> The use-case for reverse indexes in Oracle is pretty small: it's _only_ about the contention when doing a lot of inserts with increasing numbers (because the different transactions will be blocked when accessing the blocks in question).
Exactly. That would include logging databases and big/high-frequency
OLTP systems.
> As Postgres manages inserts differently than Oracle I'm not so sure that this problem exists in Postgres the same way it does in Oracle.
Maybe, PostgreSQL internal experts can answer that question thoroughly.
> That's why I asked if you have a _specific_ problem.
I see. Answering explicitly: no, I don't.
> Richard Footes blog post is mostly about the myth that _if_ you have a reverse index this is only used for equality operations.
> It does not claim that a reverse index is faster than a regular index _if_ it is used for a range scan.
Correct.
> The question is: do you think you need a reverse index because you have a performance problem with when doing many, many inserts at the same time using "close-by" values into a table that uses a btree index on the column?
I presume that Oracle would not invest resources in implementing
features which would have no benefits for their customers. Thus, the
research on this topic should already been done for us.
That given, if we can answer your question 'whether PostgreSQL handles
it differently from Oracle so that the contention issue cannot arise'
can be answered with a no, I tend to say: yes.
> Or do you think you need a reverse index to improve the performance of a range scan? If that is the then you can easily us a gin/gist index or even a simple btree index using a trigram index to speed up a "LIKE '%abc%'" (something Oracle can't do at all) without having to worry about obfuscation layers (aka ORM).
From what I gather, reverse key indexes are not about improving range
scans but about improving insertion speed due to diversification of
insertion location.
I actually used Richard Foote's posts only to get a proper understanding
of reverse key indexes and what can and cannot be done with them and
where their issues are:
https://richardfoote.wordpress.com/2008/01/14/introduction-to-reverse-key-indexes-part-i/https://richardfoote.wordpress.com/2008/01/16/introduction-to-reverse-key-indexes-part-ii-another-my...https://richardfoote.wordpress.com/2008/01/18/introduction-to-reverse-key-indexes-part-iii-a-space-o...https://richardfoote.wordpress.com/2008/01/21/introduction-to-reverse-key-indexes-part-iv-cluster-on...
--
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: <54EF1DB0.3060304@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