Thread: can system catalogs have GIN indexes?
Hi, Today, I found myself wondering whether we could add a non-btree index, and in particular a GIN index, to a system catalog if we so desired. Currently, after initdb, we only create tables and btree indexes, but I'm not sure if there is any good reason why we couldn't do something else. One problem with a GIN index specifically is that it doesn't implement amgettuple, so routines like systable_getnext() and index_getnext() couldn't be used, and we'd have to code up a bitmap scan. Currently, bitmap scans aren't used anywhere other than the executor, but I don't know of any reason why it wouldn't be practical to use them elsewhere. I think I may pursue a different approach to the problem that led me to think about this, at least for the moment. But I'm still curious about the general question: if somebody showed up with a well-written patch that added a GIN index to a system catalog, would that potentially be acceptable, or DOA? -- Robert Haas EDB: http://www.enterprisedb.com
On Thu, Apr 27, 2023 at 11:04 AM Robert Haas <robertmhaas@gmail.com> wrote: > I think I may pursue a different approach to the problem that led me > to think about this, at least for the moment. But I'm still curious > about the general question: if somebody showed up with a well-written > patch that added a GIN index to a system catalog, would that > potentially be acceptable, or DOA? Surely it would depend on the use case. Is this just an intellectual exercise, or do you actually plan on doing something like this, in whatever way? For example, does the posting list compression seem like it might offer a compelling trade-off for some system catalog indexes over and above B-Tree deduplication? I'm asking this (at least in part) because it affects the answer. Lots of stuff that GIN does that seems like it would be particularly tricky to integrate with a system catalog is non-essential. It could be (and sometimes is) selectively disabled. Whereas B-Tree indexes don't really have any optional features (you can disable deduplication selectively, but I believe that approximately nobody ever found it useful to do so). -- Peter Geoghegan
On Thu, Apr 27, 2023 at 11:17 AM Peter Geoghegan <pg@bowt.ie> wrote: > I'm asking this (at least in part) because it affects the answer. Lots > of stuff that GIN does that seems like it would be particularly tricky > to integrate with a system catalog is non-essential. It could be (and > sometimes is) selectively disabled. Whereas B-Tree indexes don't > really have any optional features (you can disable deduplication > selectively, but I believe that approximately nobody ever found it > useful to do so). Actually, you *can't* disable deduplication against a system catalog index in practice, due to a limitation with storage parameters. That's why deduplication was disabled against system catalogs until Postgres 15. That limitation would be much harder to ignore with GIN indexes. B-Tree/nbtree deduplication is more or less optimized for being enabled by default. It has most of the upside of posting lists, with very few downsides. For example, you can sometimes get a lot more space savings with Posting list compression (very low cardinality indexes can be as much as 10x smaller), but that's not really compatible with the design of nbtree deduplication. -- Peter Geoghegan
On Thu, Apr 27, 2023 at 2:17 PM Peter Geoghegan <pg@bowt.ie> wrote: > On Thu, Apr 27, 2023 at 11:04 AM Robert Haas <robertmhaas@gmail.com> wrote: > > I think I may pursue a different approach to the problem that led me > > to think about this, at least for the moment. But I'm still curious > > about the general question: if somebody showed up with a well-written > > patch that added a GIN index to a system catalog, would that > > potentially be acceptable, or DOA? > > Surely it would depend on the use case. Is this just an intellectual > exercise, or do you actually plan on doing something like this, in > whatever way? For example, does the posting list compression seem like > it might offer a compelling trade-off for some system catalog indexes > over and above B-Tree deduplication? > > I'm asking this (at least in part) because it affects the answer. Lots > of stuff that GIN does that seems like it would be particularly tricky > to integrate with a system catalog is non-essential. It could be (and > sometimes is) selectively disabled. Whereas B-Tree indexes don't > really have any optional features (you can disable deduplication > selectively, but I believe that approximately nobody ever found it > useful to do so). My interest was in being able to index operators that GIN can index and btree cannot. -- Robert Haas EDB: http://www.enterprisedb.com