Re: [HACKERS] Indirect indexes

Hi Álvaro, On 1/31/17 11:54 PM, Michael Paquier wrote: > On Sat, Dec 31, 2016 at 7:35 AM, Alvaro Herrera > wrote: >> Attached is v4, which fixes a couple of relatively minor bugs. There >> are still things to tackle before this is committable, but coding review >> of

Re: [HACKERS] Indirect indexes

On Sat, Dec 31, 2016 at 7:35 AM, Alvaro Herrera wrote: > Attached is v4, which fixes a couple of relatively minor bugs. There > are still things to tackle before this is committable, but coding review > of the new executor node would be welcome. Moved to CF 2017-03

Re: [HACKERS] Indirect indexes

On Fri, Dec 30, 2016 at 5:35 PM, Alvaro Herrera wrote: > Also, vacuuming: my answer continues to be that the killtuple > interface should be good enough, ... How deeply do you believe in that answer? I mean, I grant you that there are many use cases for which that will

Re: [HACKERS] Indirect indexes

On Fri, Dec 30, 2016 at 07:35:30PM -0300, Alvaro Herrera wrote: > Attached is v4, which fixes a couple of relatively minor bugs. There > are still things to tackle before this is committable, but coding review > of the new executor node would be welcome. > > The big remaining item is still

Re: [HACKERS] Indirect indexes

Attached is v4, which fixes a couple of relatively minor bugs. There are still things to tackle before this is committable, but coding review of the new executor node would be welcome. The big remaining item is still fitting the PK data in TIDs 6 bytes. I've been looking at reworking the btree

Re: [HACKERS] Indirect indexes

Alvaro Herrera writes: > One exception: in htup_details.h I changed the definition of > HeapTupleHeaderIsHeapOnly() so that it returns a true boolean rather > than a random bit in a wider integer. Without this change, my compiler > complains when the result is passed as

Re: [HACKERS] Indirect indexes

Here's v3. This one applies on top of the "interesting-attrs" patch I sent a few hours ago: https://www.postgresql.org/message-id/20161228232018.4hc66ndrzpz4g4wn@alvherre.pgsql and contains a number of bugfixes that I discovered on my own (and which therefore require no further explanation, since

Re: [HACKERS] Indirect indexes

Alvaro Herrera wrote: > There are a few broken things yet, such as "REINDEX TABLE pg_class" and > some other operations specifically on pg_class. This one in particular > breaks the regression tests, but that shouldn't be terribly difficult to > fix. This version fixes this problem, so the

Re: [HACKERS] Indirect indexes

Here's a still-WIP patch that's a better candidate for inclusion. In this patch, I have created an executor node for indirect index scans. This node is created whenever an indexscan path is chosen for an indirect index. The planner treats indirect indexes just like regular indexes, except that

Re: [HACKERS] Indirect indexes

On Tue, Dec 13, 2016 at 11:11 PM, Pavan Deolasee wrote: > To be fair to myself, I did try to find patches with equal or more > complexity. But most of them had (multiple) reviewers assigned and were > being discussed for weeks and months. I did not think I could

Re: [HACKERS] Indirect indexes

On Thu, Dec 8, 2016 at 3:11 AM, Robert Haas wrote: > On Thu, Oct 20, 2016 at 11:30 AM, Pavan Deolasee > wrote: > > We have a design to convert WARM chains back to HOT and that will > increase > > the percentage of WARM updates much beyond 50%. I

Re: [HACKERS] Indirect indexes

On Thu, Oct 20, 2016 at 11:30 AM, Pavan Deolasee wrote: > We have a design to convert WARM chains back to HOT and that will increase > the percentage of WARM updates much beyond 50%. I was waiting for feedback > on the basic patch before putting in more efforts, but it

Re: [HACKERS] Indirect indexes

On Fri, Oct 21, 2016 at 7:04 PM, Alvaro Herrera wrote: > Robert Haas wrote: >> So, I think that this is a really promising direction, but also that >> you should try very hard to try to get out from under this 6-byte PK >> limitation. That seems really ugly, and in

Re: [HACKERS] Indirect indexes

On Tue, Dec 6, 2016 at 4:55 AM, Alvaro Herrera wrote: > Haribabu Kommi wrote: > > > Closed in 2016-11 commitfest with "returned with feedback" status. > > What feedback? Sorry, I was not able to find that there is no feedback on the patch earlier. Thanks for your

Re: [HACKERS] Indirect indexes

Haribabu Kommi wrote: > Closed in 2016-11 commitfest with "returned with feedback" status. What feedback? -- Álvaro Herrerahttps://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-hackers mailing list

Re: [HACKERS] Indirect indexes

On Sun, Nov 13, 2016 at 4:28 AM, Andres Freund wrote: > Hi, > > On 2016-11-01 01:43:31 -0300, Alvaro Herrera wrote: > > Alvaro Herrera wrote: > > > I propose we introduce the concept of "indirect indexes". > > > > This is a WIP non-functional patch for indirect indexes. I've

Re: [HACKERS] Indirect indexes

Hi, On 2016-11-01 01:43:31 -0300, Alvaro Herrera wrote: > Alvaro Herrera wrote: > > I propose we introduce the concept of "indirect indexes". > > This is a WIP non-functional patch for indirect indexes. I've been > distracted from working on it for some time already and will be off-line > for

Re: [HACKERS] Indirect indexes

Alvaro Herrera wrote: > I propose we introduce the concept of "indirect indexes". This is a WIP non-functional patch for indirect indexes. I've been distracted from working on it for some time already and will be off-line for half this month yet, but since this was discussed and seems to be

Re: [HACKERS] Indirect indexes

On 21.10.2016 22:54, Jim Nasby wrote: On 10/21/16 2:48 PM, Sven R. Kunze 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

Re: [HACKERS] Indirect indexes

Alvaro Herrera writes: > Robert Haas wrote: >> So, I think that this is a really promising direction, but also that >> you should try very hard to try to get out from under this 6-byte PK >> limitation. That seems really ugly, and in practice it probably means >> your

Re: [HACKERS] Indirect indexes

Robert Haas wrote: > So, I think that this is a really promising direction, but also that > you should try very hard to try to get out from under this 6-byte PK > limitation. That seems really ugly, and in practice it probably means > your PK is probably going to be limited to int4, which is

Re: [HACKERS] Indirect indexes

On 10/21/16 4:05 PM, Adam Brusselback wrote: My main point is that maybe the reason why most users use int4 pkeys (besides conventional wisdom) is because it gets the most support from features like this, and it may not be quite as skewed if that same support were given to other types. I think

Re: [HACKERS] Indirect indexes

Just throwing an anecdote out there, but my company uses UUID for primary keys on every table in the DB. While int4 is for sure more popular, it would be nice if there weren't even more reasons to "force" people in that direction. I know I started regretting the decision to go with UUID primary

Re: [HACKERS] Indirect indexes

On 10/21/16 2:48 PM, Sven R. Kunze 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

Re: [HACKERS] Indirect indexes

On 10/19/16 7:52 AM, Robert Haas wrote: So, I think that this is a really promising direction, but also that you should try very hard to try to get out from under this 6-byte PK limitation. That seems really ugly, and in practice it probably means your PK is probably going to be limited to

Re: [HACKERS] Indirect indexes

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

Re: [HACKERS] Indirect indexes

On Thu, Oct 20, 2016 at 4:24 PM, Bruce Momjian wrote: > On Thu, Oct 20, 2016 at 05:14:51PM +0200, Petr Jelinek wrote: > > > Also, it seems indirect indexes would be useful for indexing columns > > > that are not updated frequently on tables that are updated frequently, > > >

Re: [HACKERS] Indirect indexes

On Thu, Oct 20, 2016 at 1:08 PM, Pavan Deolasee wrote: > On Thu, Oct 20, 2016 at 9:20 PM, Claudio Freire > wrote: >> >> >> >> With indirect indexes, since you don't need to insert a tid, you can >> just "insert on conflict do nothing" on the

Re: [HACKERS] Indirect indexes

On Thu, Oct 20, 2016 at 9:20 PM, Claudio Freire wrote: > > > With indirect indexes, since you don't need to insert a tid, you can > just "insert on conflict do nothing" on the index. > Would that work with non-unique indexes? Anyways, the point I was trying to make is

Re: [HACKERS] Indirect indexes

On Thu, Oct 20, 2016 at 12:30 PM, Pavan Deolasee wrote: > > > On Thu, Oct 20, 2016 at 8:44 PM, Petr Jelinek wrote: >> >> >> >> WARM can do WARM update 50% of time, indirect index can do HOT update >> 100% of time (provided the column is not

Re: [HACKERS] Indirect indexes

On 20/10/16 17:24, Bruce Momjian wrote: > On Thu, Oct 20, 2016 at 05:14:51PM +0200, Petr Jelinek wrote: >>> Also, it seems indirect indexes would be useful for indexing columns >>> that are not updated frequently on tables that are updated frequently, >>> and whose primary key is not updated

Re: [HACKERS] Indirect indexes

On Thu, Oct 20, 2016 at 8:44 PM, Petr Jelinek wrote: > > > WARM can do WARM update 50% of time, indirect index can do HOT update > 100% of time (provided the column is not changed), I don't see why we > could not have both solutions. > > I think the reason why I restricted

Re: [HACKERS] Indirect indexes

On Thu, Oct 20, 2016 at 05:14:51PM +0200, Petr Jelinek wrote: > > Also, it seems indirect indexes would be useful for indexing columns > > that are not updated frequently on tables that are updated frequently, > > and whose primary key is not updated frequently. That's quite a logic > > problem

Re: [HACKERS] Indirect indexes

On Thu, Oct 20, 2016 at 12:14 PM, Petr Jelinek wrote: > WARM can do WARM update 50% of time, indirect index can do HOT update > 100% of time (provided the column is not changed), I don't see why we > could not have both solutions. > > That all being said, it would be

Re: [HACKERS] Indirect indexes

On 20/10/16 14:29, Bruce Momjian wrote: > On Wed, Oct 19, 2016 at 01:04:16PM -0400, Bruce Momjian wrote: >> On Wed, Oct 19, 2016 at 06:58:05PM +0200, Simon Riggs wrote: > I agree. Also, I think the recheck mechanism will have to be something > like > what I wrote for WARM i.e. only

Re: [HACKERS] Indirect indexes

Joshua D. Drake wrote: > That said would it be possible to make this index an extension (like rum?). > Assuming of course we can get any required infrastructure changes done in a > general way. Well, the patch I currently have creates a separate index AM called "ibtree" which is an indirect

Re: [HACKERS] Indirect indexes

On 10/20/2016 06:39 AM, Alvaro Herrera wrote: Bruce Momjian wrote: Also, it seems indirect indexes would be useful for indexing columns that are not updated frequently on tables that are updated frequently, and whose primary key is not updated frequently. That's quite a logic problem for

Re: [HACKERS] Indirect indexes

On Thu, Oct 20, 2016 at 10:39:23AM -0300, Alvaro Herrera wrote: > Bruce Momjian wrote: > > > Just to clarify, if a feature improves performance by 1%, but is enabled > > by default, that is 10x more useful across our entire user base as the > > feature numbers listed above, 1% vs 0.1%. > >

Re: [HACKERS] Indirect indexes

Bruce Momjian wrote: > Just to clarify, if a feature improves performance by 1%, but is enabled > by default, that is 10x more useful across our entire user base as the > feature numbers listed above, 1% vs 0.1%. Great. But not all users are alike. We have big profile users that write blog

Re: [HACKERS] Indirect indexes

On Wed, Oct 19, 2016 at 01:04:16PM -0400, Bruce Momjian wrote: > On Wed, Oct 19, 2016 at 06:58:05PM +0200, Simon Riggs wrote: > > >> I agree. Also, I think the recheck mechanism will have to be something > > >> like > > >> what I wrote for WARM i.e. only checking for index quals won't be enough

Re: [HACKERS] Indirect indexes

Alexander Korotkov wrote: Hi, > Thank you for your proposal. One question about vacuum excites me most. > Imagine another situation: PK column was not updated, but indirect indexed > column was updated. > Thus, for single heap tuple we would have single PK tuple and two indirect > index tuples

Re: [HACKERS] Indirect indexes

On Wed, Oct 19, 2016 at 12:53 PM, Simon Riggs wrote: > On 18 October 2016 at 23:46, Alexander Korotkov > wrote: > > > Then vacuum removes (0;1) from heap, reference to (0;1) from tbl_pk_idx. > > But how will it remove (1,1) tuple from

Re: [HACKERS] Indirect indexes

On Wed, Oct 19, 2016 at 2:04 PM, Bruce Momjian wrote: >> What we should ask is what is the difference between indirect indexes >> and WARM and to what extent they overlap. >> >> My current understanding is that WARM won't help you if you update >> parts of a JSON document and/or

Re: [HACKERS] Indirect indexes

On Wed, Oct 19, 2016 at 06:58:05PM +0200, Simon Riggs wrote: > >> I agree. Also, I think the recheck mechanism will have to be something like > >> what I wrote for WARM i.e. only checking for index quals won't be enough > >> and we > >> would actually need to verify that the heap tuple satisfies

Re: [HACKERS] Indirect indexes

On 19 October 2016 at 18:40, Bruce Momjian wrote: > On Wed, Oct 19, 2016 at 07:23:28PM +0530, Pavan Deolasee wrote: >> AFAICS, even without considering VACUUM, indirect indexes would be always >> used with recheck. >> As long as they don't contain visibility

Re: [HACKERS] Indirect indexes

On Wed, Oct 19, 2016 at 10:21 AM, Simon Riggs wrote: >> Simon objected that putting the PK >> into the index tuple would disable HOT, but I don't think that's a >> valid objection. > > Just to be clear, that's not what I objected to. Claudio appeared to > be suggesting that

Re: [HACKERS] Indirect indexes

On Wed, Oct 19, 2016 at 07:23:28PM +0530, Pavan Deolasee wrote: > AFAICS, even without considering VACUUM, indirect indexes would be always > used with recheck. > As long as they don't contain visibility information.  When indirect > indexed column was updated, indirect index would

Re: [HACKERS] Indirect indexes

On Wed, Oct 19, 2016 at 9:21 AM, Simon Riggs wrote: > The main problem IMV is GIN indexes. It's relatively easy to discuss > variable length PKs with btrees, but the GIN format is designed around > use of 6byte values, so expanding beyond that would require > significant

Re: [HACKERS] Indirect indexes

On Wed, Oct 19, 2016 at 7:19 PM, Alexander Korotkov < a.korot...@postgrespro.ru> wrote: > On Wed, Oct 19, 2016 at 3:52 PM, Robert Haas > wrote: > >> The VACUUM problems seem fairly serious. It's true that these indexes >> will be less subject to bloat, because they only

Re: [HACKERS] Indirect indexes

On Wed, Oct 19, 2016 at 3:52 PM, Robert Haas wrote: > The VACUUM problems seem fairly serious. It's true that these indexes > will be less subject to bloat, because they only need updating when > the PK or the indexed columns change, not when other indexed columns >

Re: [HACKERS] Indirect indexes

On 19 October 2016 at 14:52, Robert Haas wrote: > On Tue, Oct 18, 2016 at 2:28 PM, Alvaro Herrera > wrote: >> I propose we introduce the concept of "indirect indexes". I have a toy >> implementation and before I go further with it, I'd like this

Re: [HACKERS] Indirect indexes

On Tue, Oct 18, 2016 at 2:28 PM, Alvaro Herrera wrote: > I propose we introduce the concept of "indirect indexes". I have a toy > implementation and before I go further with it, I'd like this assembly's > input on the general direction. > > Indirect indexes are similar

Re: [HACKERS] Indirect indexes

On 18 October 2016 at 23:46, Alexander Korotkov wrote: > Then vacuum removes (0;1) from heap, reference to (0;1) from tbl_pk_idx. > But how will it remove (1,1) tuple from tbl_val_indirect_idx? Thus, before > vacuuming tbl_val_indirect_idx we should know not only

Re: [HACKERS] Indirect indexes

On Tue, Oct 18, 2016 at 5:48 PM, Simon Riggs wrote: > On 18 October 2016 at 22:04, Claudio Freire wrote: >> On Tue, Oct 18, 2016 at 3:28 PM, Alvaro Herrera >> wrote: >>> I propose we introduce the concept of "indirect

Re: [HACKERS] Indirect indexes

On Wed, Oct 19, 2016 at 12:21 AM, Alexander Korotkov < a.korot...@postgrespro.ru> wrote: > On Tue, Oct 18, 2016 at 9:28 PM, Alvaro Herrera > wrote: > >> Vacuuming presents an additional challenge: in order to remove index >> items from an indirect index, it's critical

Re: [HACKERS] Indirect indexes

Hi, Alvaro! Thank you for your proposal. One question about vacuum excites me most. On Tue, Oct 18, 2016 at 9:28 PM, Alvaro Herrera wrote: > Vacuuming presents an additional challenge: in order to remove index > items from an indirect index, it's critical to scan the

Re: [HACKERS] Indirect indexes

On 18 October 2016 at 22:04, Claudio Freire wrote: > On Tue, Oct 18, 2016 at 3:28 PM, Alvaro Herrera > wrote: >> I propose we introduce the concept of "indirect indexes". I have a toy >> implementation and before I go further with it, I'd like

Re: [HACKERS] Indirect indexes

On Tue, Oct 18, 2016 at 3:28 PM, Alvaro Herrera wrote: > I propose we introduce the concept of "indirect indexes". I have a toy > implementation and before I go further with it, I'd like this assembly's > input on the general direction. > > Indirect indexes are similar

Re: [HACKERS] Indirect indexes

On 18 October 2016 at 21:41, Joshua D. Drake wrote: > Are we > trading initial performance gains for performance degradation through > maintenance? Eh? That's backwards, so No. The whole point of this is it avoids long term degradation currently caused by non-HOT

Re: [HACKERS] Indirect indexes

On 10/18/2016 11:28 AM, Alvaro Herrera wrote: Vacuuming presents an additional challenge: in order to remove index items from an indirect index, it's critical to scan the PK index first and collect the PK values that are being removed. Then scan the indirect index and remove any items that