RhodiumToad
Indexing ipaddress type
The README isn't clear how best to index ipaddress type - should GIST be specified explicitly or implicit defaults are OK? I have 800 million rows with mixed IPv4 and IPv6 address (Postgresql 11 with ip4r 2.2)
CREATE INDEX master_clientip_idx ON network_events using gist (clientip);
or
CREATE INDEX master_clientip_idx ON network_events (clientip);
Thank you!
Assuming clientip is a single address (not a range), then you would use a btree index for this. The GiST index support is for when you want to store ranges in the table.
With any data type, the choice of what index to use is generally dictated by what comparison operators you want to use on the data. So a condition like WHERE clientip = $1 or WHERE clientip BETWEEN lower($1) AND upper($1) would make use of a btree index. Whereas if you were doing WHERE range >>= $1 then a GiST index would be indicated.
Excellent, that answers it. And yes, I meant the ipaddress type for a single address
Would you mind adding your comment above to the README, please? Both para 1 and 2 above, as they are non-obvious to n00bs like me.
Thanks again!
I'll leave this issue open and update the readme the next time I have other work to do on it.