Обсуждение: Re: [BUGS] Bug in create operator and/or initdb

> I suspect that the right thing to do is to kill the inet type
> entirely, and replace it with a special case of cidr. (And
> possibly then to kill cidr and replace it with something that
> can be indexed more effectively.)

Yes, which is actually what brought this to my attention.
I'll be sending an rtree index implementation shortly for review/comments.

> For a replacement type, how important is it that it be
> completely compatible with the existing inet/cidr types? Is
> anyone actually using inet types with a non-cidr mask?

I wouldn't think so, anyone I've spoken with has come up with other ways of managing that kind of info, because of, as
youmentioned, it's lack of proper index methods. 

Kind Regards,

John Hansen

"John Hansen" <john@geeknet.com.au> writes:

> I wouldn't think so, anyone I've spoken with has come up with other ways of
> managing that kind of info, because of, as you mentioned, it's lack of
> proper index methods.

On the contrary I'm using it for something that isn't really what it was
designed for precisely *because* of the index methods. What index access
methods are you looking for that are lacking?

db=> explain select * from foo where foo_code << '4.0.0.0/8';                                       QUERY PLAN
                             
 
------------------------------------------------------------------------------------------Index Scan using foo_foo_code
onfoo  (cost=0.00..34.56 rows=1695 width=229)  Index Cond: ((foo_code > '4.0.0.0/8'::cidr) AND (foo_code <=
'4.255.255.255'::cidr)) Filter: (foo_code << '4.0.0.0/8'::cidr)
 
(3 rows)


-- 
greg

> On the contrary I'm using it for something that isn't really what it was
> designed for precisely *because* of the index methods. What index access
> methods are you looking for that are lacking?
> 
> db=> explain select * from foo where foo_code << '4.0.0.0/8';

explain select * from foo where foo_code >> '220.244.179.214/32';

John Hansen <john@geeknet.com.au> writes:
>> On the contrary I'm using it for something that isn't really what it was
>> designed for precisely *because* of the index methods. What index access
>> methods are you looking for that are lacking?
>> 
>> db=> explain select * from foo where foo_code << '4.0.0.0/8';

> explain select * from foo where foo_code >> '220.244.179.214/32';

Note also that the btree optimization for << depends on having a
plan-time-constant righthand side; so it's useless for joins, for
instance.  I didn't look closely, but I'd suppose that rtree could
help for << searches without that constraint.

Looking to the future, it might be better to base this on gist instead
of rtree indexes --- gist is being worked on semi-actively, rtree isn't
really being touched at all.

But the immediate problem is that I don't think we can integrate this
if it doesn't handle IPv6 addresses.  We aren't going to want to
backslide on having full IPv6 support.
        regards, tom lane

> Note also that the btree optimization for << depends on having a
> plan-time-constant righthand side; so it's useless for joins, for
> instance.  I didn't look closely, but I'd suppose that rtree could
> help for << searches without that constraint.

Indeed it can... tho rtree seems to be unable to do merge joins, so you
only get an index scan on one of the joined tables. Which index is
chosen has proven a bit hard to predict.

> 
> Looking to the future, it might be better to base this on gist instead
> of rtree indexes --- gist is being worked on semi-actively, rtree isn't
> really being touched at all.

Yea, rtree is also broken, tho I think andrew is going to attempt fixing
it.

> 
> But the immediate problem is that I don't think we can integrate this
> if it doesn't handle IPv6 addresses.  We aren't going to want to
> backslide on having full IPv6 support.

Right,. i'll be adding ipv6 support...

... John