This optimisation returns the same result, but in about
one fifteenth of the time than the previous version of
the query.
This can also be parallelised by PostgreSQL so that more
CPU cores will decrease the time it takes to generate
the network list.
On my system this went down from 75 to 5 minutes.
Signed-off-by: Michael Tremer <michael.tremer@ipfire.org>
# Select all known networks
rows = self.db.query("""
SELECT
# Select all known networks
rows = self.db.query("""
SELECT
+ DISTINCT ON (announcements.network)
announcements.network AS network,
announcements.autnum AS autnum,
announcements.network AS network,
announcements.autnum AS autnum,
- (
- SELECT networks.country FROM networks
- WHERE announcements.network <<= networks.network
- ORDER BY masklen(networks.network) DESC
- LIMIT 1
- ) AS country,
+ networks.country AS country,
+
+ -- Must be part of returned values for ORDER BY clause
+ masklen(networks.network) AS sort,
)
) AS is_anycast
FROM announcements
)
) AS is_anycast
FROM announcements
+ LEFT JOIN networks ON announcements.network <<= networks.network
+ ORDER BY announcements.network, sort DESC