location-importer: Speed up exporting the database

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>

diff --git a/src/python/location-importer.in b/src/python/location-importer.in
index 0551cf6605b9a58426df329ff3342d1fb875912d..1c9f8c8427a297dab94f836ba5719a1ce95572e5 100644 (file)
--- a/src/python/location-importer.in
+++ b/src/python/location-importer.in
@@ -216,14 +216,13 @@ class CLI(object):
                # Select all known networks
                rows = self.db.query("""
                        SELECT
+                               DISTINCT ON (announcements.network)
                                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,
 
                                -- Flags
                                COALESCE(
@@ -263,6 +262,8 @@ class CLI(object):
                                        )
                                ) AS is_anycast
                        FROM announcements
+                               LEFT JOIN networks ON announcements.network <<= networks.network
+                       ORDER BY announcements.network, sort DESC
                """)
 
                for row in rows: