index for inet column

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