Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Remove old indexes on cidr colum of network_* tables #12

Open
bernhardreiter opened this issue Feb 8, 2021 · 0 comments
Open

Remove old indexes on cidr colum of network_* tables #12

bernhardreiter opened this issue Feb 8, 2021 · 0 comments
Assignees

Comments

@bernhardreiter
Copy link
Member

bernhardreiter commented Feb 8, 2021

Since 052a712 we require at least PostgreSQL 9.4 because we use the GiST index.

And thus we probably don't need the other indexes anymore. This could be checked if there is no other code
with the more complicated form of doing the request. Also the removal of the old indexes need to be part of
an db update instruction set.

Advantages: cleaner code (which is shorter) and no need to maintain the indexes in operation.

This is the code that probably should be removed:

-- Indexes on the cidr column to improve queries that look up a network
-- based on an IP-address. The default btree index of PostgreSQL is not
-- used for those queries, so we need to do it in some other way. A
-- simple way is to have indexes for the lower and upper bounds of the
-- address range represented by the cidr value, so that's what we do
-- here. The main downside is that the queries will have to use the same
-- expressions as the ones used in the indexes. E.g. a query matching
-- network that contain the IP-address ip and using n as the local alias
-- for the table should use a where clause condition of the form
--
-- inet(host(network(n.address))) <= ip
-- AND ip <= inet(host(broadcast(n.address)))
--
-- FIXME: In PostgreSQL 9.4 there's GiST indexes for the inet and cidr
-- types (see http://www.postgresql.org/docs/9.4/static/release-9-4.html).
-- We cannot use that at the moment, because we still need to support
-- PostgreSQL 9.3 which is the version available in Ubuntu 14.04LTS.
--
-- XXX COMMENT Aaron: please let's simply depend on postgresql >= 9.4
-- IMHO that's okay to demand this XXX
--
CREATE INDEX network_cidr_lower_idx
ON network ((inet(host(network(address)))));
CREATE INDEX network_cidr_upper_idx
ON network ((inet(host(broadcast(address)))));
CREATE INDEX network_automatic_cidr_lower_idx
ON network_automatic ((inet(host(network(address)))));
CREATE INDEX network_automatic_cidr_upper_idx
ON network_automatic ((inet(host(broadcast(address)))));

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

2 participants