Обсуждение: ip address data type
Hi
I have queries that use like operators and regex patterns to determine if an ip address is internal or external (this is against a table with say 100 million distinct ip addresses).
Does the inet data type offer comparison/search performance benefits over plain text for ip addresses..
On Mon, Apr 24, 2006 at 03:45:14PM -0700, Sriram Dandapani wrote: > Hi > > I have queries that use like operators and regex patterns to determine > if an ip address is internal or external (this is against a table with > say 100 million distinct ip addresses). > > Does the inet data type offer comparison/search performance benefits > over plain text for ip addresses.. Well, benchmark it and find out. :) But it'd be hard to be slower than like or regex... -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
On Apr 24, 2006, at 3:45 PM, Sriram Dandapani wrote: > Hi > > > > I have queries that use like operators and regex patterns to > determine if an ip address is internal or external (this is against > a table with say 100 million distinct ip addresses). > > > > Does the inet data type offer comparison/search performance > benefits over plain text for ip addresses.. It's probably better than text-based, but it's hard to be worse than regex and like. Depending on your exact needs http://pgfoundry.org/projects/ip4r/ may be interesting, and I've also found pretty good behavior by mapping an IP address onto a 2^31 offset integer. Cheers, Steve
* Sriram Dandapani: > Does the inet data type offer comparison/search performance benefits > over plain text for ip addresses.. Queries like "host << '192.168.17.192/28'" use an available index on the host column. In theory, you could do this with LIKE and strings, but this gets pretty messy and needs a lot of client-side logic.