Обсуждение: index for inet column
Hi,
I was able to create gin index on inet column in PG.
GIN is good with points/elements in sets. Is gin a good index for inet column ?
It seems gist index would be better.
Comments are welcome.
## Zhihong Yu (zyu@yugabyte.com): > I was able to create gin index on inet column in PG. > > GIN is good with points/elements in sets. Is gin a good index for inet > column ? > It seems gist index would be better. Why not use btree? The common operations are quite supported with that. (Common operations being equality and subnet/CIDR matching, the latter being a glorified less/greater than operation. If you are using non- continous netmasks, you are already in a rather painful situation networkwise and it will not get better in the database, so don't). Regards, Christoph -- Spare Space
Zhihong Yu <zyu@yugabyte.com> writes: > I was able to create gin index on inet column in PG. > GIN is good with points/elements in sets. Is gin a good index for inet > column ? As far as Postgres is concerned, inet is a scalar type: it has a linear sort order, and there aren't many operations on it that are concerned with identifiable sub-objects. That means btree is a perfectly fine index type for it, while GIN (which lives and dies by sub-objects) is pretty off-point. I suppose you used btree_gin for your index, because there are no other GIN opclasses that would take inet. As the name implies, that's a poor man's substitute for btree; there is nothing it does that btree doesn't do better. Generally speaking, the use-case for btree_gin is where you want to make a single, multi-column index in which one column is a collection type (that is well-suited for GIN) but another is just a scalar type. If you're making a one-column index with btree_gin, you're doing it wrong. > It seems gist index would be better. Largely the same comments apply to GiST: it's not really meant for scalar types either. regards, tom lane