To: PostgreSQL-development <pgsql-hackers@postgresql.org>
In-Reply-To: 
From: "Sven R. Kunze" <srkunze@mail.de>
Subject: Re: Indirect indexes
Message-ID: <a3ddce35-8c3e-2021-ab93-4b137cb4bee0@mail.de>
Date: Fri, 21 Oct 2016 21:48:33 +0200
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org

On 2016-10-18 20:04:32, Claudio Freire wrote:

 > You don't need that limitation (and vacuum will be simpler) if you add
the PK as another key, akin to:
 >
 > CREATE INDIRECT INDEX idx ON tab (a, b, c);
 >
 > turns into
 >
 > CREATE INDEX idx ON tab (a, b, c, pk);


I know I am late to this point but I wanted to present my mere user's 
point of view.

First I liked it, as does not introduce yet another syntax to learn. 
However, after following the discussion, I see that indirect indexes 
have their disadvantages/slowdowns as well. If adding "pk" to the end of 
the column list just converts the index to an indirect index, I am 
unable to use a direct index which might be better in certain cases.

So, from a "dumb" user's point of view, I wonder if PostgreSQL can make 
the right decision of direct/indirect reliably (which would be great). 
And if not, what would be the alternatives? Introducing CREATE DIRECT INDEX?

Cheers,
Sven

PS: I mot saying I would be affected by this but IIRC we have (..., pk) 
indexes in production which then would be converted to indirect ones. 
But I cannot tell whether indirect indexes would do good or harm there.


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