Re: indices and cidr/inet type

in looking over the section of the users manual on indices, i see that
R-Tree indices are recommended for <<, but this advice is clearly tied to
the geometric interpretation of << ("to the left of") rather than the
network interpretation ("is contained in").

what type of index is recommended when the dominant lookup on a field is

'<networkaddress>' << ipblock

where the ipblock column is of type cidr?

thanks,
richard
--
Richard Welty rwelty(at)averillpark(dot)net
Averill Park Networking 518-573-7592
Java, PHP, PostgreSQL, Unix, Linux, IP Network Engineering, Security

Richard Welty <rwelty(at)averillpark(dot)net> writes:
> what type of index is recommended when the dominant lookup on a field is
> '<networkaddress>' << ipblock

I don't think we have a suitable index type at the moment; or more
accurately, we don't have a suitable operator class. It would be an
interesting exercise to write an inet opclass for either rtree or GiST.
I'm not sure which one would be more suitable, but probably you could
handle << with one or the other.

regards, tom lane

On Tue, 05 Aug 2003 15:09:00 -0400 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Richard Welty <rwelty(at)averillpark(dot)net> writes:
> > what type of index is recommended when the dominant lookup on a field
> is
> > '<networkaddress>' << ipblock
>
> I don't think we have a suitable index type at the moment; or more
> accurately, we don't have a suitable operator class. It would be an
> interesting exercise to write an inet opclass for either rtree or GiST.
> I'm not sure which one would be more suitable, but probably you could
> handle << with one or the other.

hmmm. where should i go looking for sample code implementing operator
classes as an example?

by way of context, i do a _lot_ of email work, and my client of the moment
is the owner of an ISP who is migrating his user & system configurations
into postgresql as much as he possibly can. this includes his list of
allowed mail relay hosts and locally blacklisted spam sources. while the
tables aren't that large right now and the planner probably wouldn't use an
index even if one existed, i can definitely see fast << operations on CIDR
and INET types as being more than a little valuble in cases where things do
get large.

richard
--
Richard Welty rwelty(at)averillpark(dot)net
Averill Park Networking 518-573-7592
Java, PHP, PostgreSQL, Unix, Linux, IP Network Engineering, Security

Richard Welty <rwelty(at)averillpark(dot)net> writes:
> On Tue, 05 Aug 2003 15:09:00 -0400 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> I don't think we have a suitable index type at the moment; or more
>> accurately, we don't have a suitable operator class. It would be an
>> interesting exercise to write an inet opclass for either rtree or GiST.

> hmmm. where should i go looking for sample code implementing operator
> classes as an example?

contrib. Specifically, I'd suggest looking at contrib/seg, which
defines a datatype for "interval on the real line" and builds a GIST
opclass for it. This seems like it'd map very easily to CIDR subnets.
There is a lot of cruft in contrib/seg that you don't need (at least
some coming from the fact that it originally was an rtree opclass not
a GiST opclass --- in GiST you can make your own choices about what
set of operators need to be supported, but seg just slavishly follows
the rtree design). But it's a working example...

I'd also suggest trying to get Oleg and Teodor involved, as they are the
only active hackers who have done anything much with GiST.

regards, tom lane

On Tue, 05 Aug 2003 18:46:47 -0400 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Richard Welty <rwelty(at)averillpark(dot)net> writes:
> > hmmm. where should i go looking for sample code implementing operator
> > classes as an example?
>
> contrib. Specifically, I'd suggest looking at contrib/seg, which
> defines a datatype for "interval on the real line" and builds a GIST
> opclass for it. This seems like it'd map very easily to CIDR subnets.

very good, thanks.

i'll go rooting about for documentation on how GIST works.

also, how does 7.4 handle comparison of v6 and v4 addresses? i should think
it should map the v4 addresses into the v6 space provided for v4 mapping,
but that's not the only thing that might have been done.

richard
--
Richard Welty rwelty(at)averillpark(dot)net
Averill Park Networking 518-573-7592
Java, PHP, PostgreSQL, Unix, Linux, IP Network Engineering, Security

On Tue, 5 Aug 2003, Richard Welty wrote:

> On Tue, 05 Aug 2003 15:09:00 -0400 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> > Richard Welty <rwelty(at)averillpark(dot)net> writes:
> > > what type of index is recommended when the dominant lookup on a field
> > is
> > > '<networkaddress>' << ipblock
> >
> > I don't think we have a suitable index type at the moment; or more
> > accurately, we don't have a suitable operator class. It would be an
> > interesting exercise to write an inet opclass for either rtree or GiST.
> > I'm not sure which one would be more suitable, but probably you could
> > handle << with one or the other.
>
> hmmm. where should i go looking for sample code implementing operator
> classes as an example?

you may look at http://www.sai.msu.su/~megera/postgres/gist/tree/README.tree.english

>
> by way of context, i do a _lot_ of email work, and my client of the moment
> is the owner of an ISP who is migrating his user & system configurations
> into postgresql as much as he possibly can. this includes his list of
> allowed mail relay hosts and locally blacklisted spam sources. while the
> tables aren't that large right now and the planner probably wouldn't use an
> index even if one existed, i can definitely see fast << operations on CIDR
> and INET types as being more than a little valuble in cases where things do
> get large.
>
> richard
> --
> Richard Welty rwelty(at)averillpark(dot)net
> Averill Park Networking 518-573-7592
> Java, PHP, PostgreSQL, Unix, Linux, IP Network Engineering, Security
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>

Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83