Skip to content

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

Description

@bernhardreiter

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)))));

Metadata

Metadata

Assignees

Labels

No labels
No labels

Type

No type

Fields

No fields configured for issues without a type.

Projects

No projects

Milestone

No milestone

Relationships

None yet

Development

No branches or pull requests

Issue actions