With PostgreSQL 17.11, an SP-GiST index on an inet column that has IPv4 and IPv6 networks in it does not find all of them. In a test run of location-importer write with APNIC, ARIN and geofeed data, the temporary table n had 270617 networks. When looking up each of them through its SP-GiST index (n_search), 87 IPv6 networks were not found, for example 2400:8800:f100::1b4/127. Every UPDATE that joins n on the network then misses them, so that they are being written without the country from their geofeed (KR in that case), or their AS number. Containment does not work either: of 28156 pairs of networks that contain one another (>>=), the index found 28151, and of 22479 pairs the other way around (<<=), 22474. Which networks are missed depends on the order in which the rows are in the table. REINDEX does not help, and an index with IPv6 networks only finds everything. With a GiST index (inet_ops) on the same table, all networks and all pairs are being found. The importer uses SP-GiST for n, and for the announcements and networks tables, which are being joined by containment as well.
https://git.ipfire.org/?p=location/libloc.git;a=commit;h=8e4295f2941ef17af4a12ed6e0935028761dbb7a Author: Michael Tremer <michael.tremer@ipfire.org> importer: Add a function that tells whether a network contains another PostgreSQL can only use a B-tree index to find networks inside of another one if that one is a constant, in which case it searches for them as a range of addresses. network_contains() writes this range down, so that it can be used in joins as well. It is a SQL function that is being inlined, so that the planner sees the range. Fixes: #14500 - SP-GiST indexes on inet do not find all networks, so that the importer writes some networks without country or ASN Signed-off-by: Michael Tremer <michael.tremer@ipfire.org>
https://git.ipfire.org/?p=location/libloc.git;a=commit;h=5b27ac456e996684979530b6d9beb06bc09f034d Author: Michael Tremer <michael.tremer@ipfire.org> importer: Don't search for networks with an SP-GiST index when writing An SP-GiST index does not find all networks if it has IPv4 and IPv6 networks in it, like n does. In a test run with the data of APNIC, ARIN and the Geofeeds that they refer to, it did not find 87 of 270617 networks when searching for each of them, so that the networks have been written without their country or AS number. That is what made 2400:8800:f100::1b4/127 have no country, although its Geofeed says KR. Which networks are affected depends on the order of the rows. n now has a B-tree index, which network_contains() searches through. That takes 21-23 s for the test data, as long as with the SP-GiST index (23-25 s). A GiST index would have found everything as well, but took 385-424 s. The database is the same as with GiST. Fixes: #14500 - SP-GiST indexes on inet do not find all networks, so that the importer writes some networks without country or ASN Signed-off-by: Michael Tremer <michael.tremer@ipfire.org>
This has been fixed in the master branch of libloc (8e4295f, 5b27ac4, d2c25bc). The importer no longer uses any SP-GiST index. Networks inside of another one are now being found with network_contains(), which searches for them as a range in a B-tree index, like PostgreSQL does on its own when the outer network is a constant. With the test data (WHOIS of APNIC and ARIN and their Geofeeds), location-importer write takes: * SP-GiST (before): 23-25 s, but 29 networks were without their country, and others had not been merged * GiST: 385-424 s * B-tree with network_contains(): 20-23 s The database is the same with GiST and with the B-tree. The next run on location01 will show how many networks were affected in the real database.
http://git.ipfire.org/?p=location/libloc.git;a=commit;h=8e4295f2941ef17af4a12ed6e0935028761dbb7a Author: Michael Tremer <michael.tremer@ipfire.org> importer: Add a function that tells whether a network contains another PostgreSQL can only use a B-tree index to find networks inside of another one if that one is a constant, in which case it searches for them as a range of addresses. network_contains() writes this range down, so that it can be used in joins as well. It is a SQL function that is being inlined, so that the planner sees the range. Fixes: #14500 - SP-GiST indexes on inet do not find all networks, so that the importer writes some networks without country or ASN Signed-off-by: Michael Tremer <michael.tremer@ipfire.org>
http://git.ipfire.org/?p=location/libloc.git;a=commit;h=5b27ac456e996684979530b6d9beb06bc09f034d Author: Michael Tremer <michael.tremer@ipfire.org> importer: Don't search for networks with an SP-GiST index when writing An SP-GiST index does not find all networks if it has IPv4 and IPv6 networks in it, like n does. In a test run with the data of APNIC, ARIN and the Geofeeds that they refer to, it did not find 87 of 270617 networks when searching for each of them, so that the networks have been written without their country or AS number. That is what made 2400:8800:f100::1b4/127 have no country, although its Geofeed says KR. Which networks are affected depends on the order of the rows. n now has a B-tree index, which network_contains() searches through. That takes 21-23 s for the test data, as long as with the SP-GiST index (23-25 s). A GiST index would have found everything as well, but took 385-424 s. The database is the same as with GiST. Fixes: #14500 - SP-GiST indexes on inet do not find all networks, so that the importer writes some networks without country or ASN Signed-off-by: Michael Tremer <michael.tremer@ipfire.org>
http://git.ipfire.org/?p=location/libloc.git;a=commit;h=d2c25bc70dfc0b29adac4ddab160e82ecd4c3196 Author: Michael Tremer <michael.tremer@ipfire.org> importer: Drop the SP-GiST indexes of announcements and networks They have the same problem as the one of n: they do not find all networks, because they have IPv4 and IPv6 networks in them. Nothing needs them any more. Networks are being found by their unique B-tree indexes, which PostgreSQL also searches through for networks inside of a constant one, like when we drop announcements that we don't want. Fixes: #14500 - SP-GiST indexes on inet do not find all networks, so that the importer writes some networks without country or ASN Signed-off-by: Michael Tremer <michael.tremer@ipfire.org>
https://git.ipfire.org/?p=location/libloc.git;a=commit;h=a10e51708d453d8d438b5a7b521cf4a9577c0f6b Author: Michael Tremer <michael.tremer@ipfire.org> importer: Find the network that a new network is in with the unique index When a RIR's networks are being copied into networks, we look for the smallest network that each of them is in, to skip those which have the same country. That has been done with the SP-GiST index on networks, which has been dropped in d2c25bc, so that it now compared every network with every other one, and LACNIC did not finish anymore. The network can only be in a network that is the same with a shorter prefix, so we now look those up one after the other in the unique index, which are up to 32 or 128 lookups. For the networks of LACNIC (on top of ARIN), this takes 177 s, and it gives the same networks as with a GiST index, which took 634 s. Fixes: #14500 - SP-GiST indexes on inet do not find all networks, so that the importer writes some networks without country or ASN Signed-off-by: Michael Tremer <michael.tremer@ipfire.org>
Dropping the SP-GiST index of networks (d2c25bc) made "update-whois" too slow to finish for LACNIC: when a RIR's networks are being copied into networks, the smallest network that each of them is in is being looked up with c.network << networks.network, which I had missed, and which then compared every network with every other one. This has been fixed in a10e517: the network can only be in the same network with a shorter prefix, so those are now being looked up in the unique index of networks, up to 32 or 128 per network. For LACNIC on top of ARIN, this takes 177 s, and gives exactly the same networks as with a GiST index, which took 634 s.
http://git.ipfire.org/?p=location/libloc.git;a=commit;h=a10e51708d453d8d438b5a7b521cf4a9577c0f6b Author: Michael Tremer <michael.tremer@ipfire.org> importer: Find the network that a new network is in with the unique index When a RIR's networks are being copied into networks, we look for the smallest network that each of them is in, to skip those which have the same country. That has been done with the SP-GiST index on networks, which has been dropped in d2c25bc, so that it now compared every network with every other one, and LACNIC did not finish anymore. The network can only be in a network that is the same with a shorter prefix, so we now look those up one after the other in the unique index, which are up to 32 or 128 lookups. For the networks of LACNIC (on top of ARIN), this takes 177 s, and it gives the same networks as with a GiST index, which took 634 s. Fixes: #14500 - SP-GiST indexes on inet do not find all networks, so that the importer writes some networks without country or ASN Signed-off-by: Michael Tremer <michael.tremer@ipfire.org>
This has been fixed in libloc 0.9.20, which has just been tagged. Closing this as fixed.