Message-ID: <54EF1DB0.3060304@tbz-pariv.de>
Date: Thu, 26 Feb 2015 14:20:48 +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> <54EF1045.5040609@tbz-pariv.de>
 <mcn4m4$o9b$1@ger.gmane.org>
In-Reply-To: <mcn4m4$o9b$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 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 s=
hould 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=20
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=20
anyway (patch provided or not), we can stop now. I am not married to=20
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_ a=
bout the contention when doing a lot of inserts with increasing numbers (be=
cause the different transactions will be blocked when accessing the blocks =
in question).
Exactly. That would include logging databases and big/high-frequency=20
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 re=
verse 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 us=
ing "close-by" values into a table that uses a btree index on the column?

I presume that Oracle would not invest resources in implementing=20
features which would have no benefits for their customers. Thus, the=20
research on this topic should already been done for us.

That given, if we can answer your question 'whether PostgreSQL handles=20
it differently from Oracle so that the contention issue cannot arise'=20
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%'" (so=
mething Oracle can't do at all) without having to worry about obfuscation l=
ayers (aka ORM).

 From what I gather, reverse key indexes are not about improving range=20
scans but about improving insertion speed due to diversification of=20
insertion location.


I actually used Richard Foote's posts only to get a proper understanding=20
of reverse key indexes and what can and cannot be done with them and=20
where their issues are:

https://richardfoote.wordpress.com/2008/01/14/introduction-to-reverse-key-i=
ndexes-part-i/
https://richardfoote.wordpress.com/2008/01/16/introduction-to-reverse-key-i=
ndexes-part-ii-another-myth-bites-the-dust/
https://richardfoote.wordpress.com/2008/01/18/introduction-to-reverse-key-i=
ndexes-part-iii-a-space-oddity/
https://richardfoote.wordpress.com/2008/01/21/introduction-to-reverse-key-i=
ndexes-part-iv-cluster-one/

--=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