importer: Restructure SQL query to be executed in parallel

There are no functional changes, this just runs quicker now.

Signed-off-by: Michael Tremer <michael.tremer@ipfire.org>

diff --git a/src/python/location-importer.in b/src/python/location-importer.in
index fe21d731631e5dd484dd079157b42e23d3a461a4..3c1e5e24890637e73eb2f538a0b07a151af3dcd0 100644 (file)
--- a/src/python/location-importer.in
+++ b/src/python/location-importer.in
@@ -237,34 +237,24 @@ class CLI(object):
 
                # Select all known networks
                rows = self.db.query("""
-                       -- Get a (sorted) list of all known networks
-                       WITH known_networks AS (
-                                       SELECT network FROM announcements
-                               UNION
-                                       SELECT network FROM networks
-                               UNION
-                                       SELECT network FROM network_overrides
-                               ORDER BY network
-                       )
-
                        -- Return a list of those networks enriched with all
                        -- other information that we store in the database
                        SELECT
-                               DISTINCT ON (known_networks.network)
-                               known_networks.network AS network,
-                               announcements.autnum AS autnum,
+                               DISTINCT ON (network)
+                               network,
+                               autnum,
 
                                -- Country
                                COALESCE(
                                        (
                                                SELECT country FROM network_overrides overrides
-                                                       WHERE announcements.network <<= overrides.network
+                                                       WHERE networks.network <<= overrides.network
                                                        ORDER BY masklen(overrides.network) DESC
                                                        LIMIT 1
                                        ),
                                        (
                                                SELECT country FROM autnum_overrides overrides
-                                                       WHERE announcements.autnum = overrides.number
+                                                       WHERE networks.autnum = overrides.number
                                        ),
                                        networks.country
                                ) AS country,
@@ -273,50 +263,67 @@ class CLI(object):
                                COALESCE(
                                        (
                                                SELECT is_anonymous_proxy FROM network_overrides overrides
-                                                       WHERE announcements.network <<= overrides.network
+                                                       WHERE networks.network <<= overrides.network
                                                        ORDER BY masklen(overrides.network) DESC
                                                        LIMIT 1
                                        ),
                                        (
                                                SELECT is_anonymous_proxy FROM autnum_overrides overrides
-                                                       WHERE announcements.autnum = overrides.number
+                                                       WHERE networks.autnum = overrides.number
                                        ),
                                        FALSE
                                ) AS is_anonymous_proxy,
                                COALESCE(
                                        (
                                                SELECT is_satellite_provider FROM network_overrides overrides
-                                                       WHERE announcements.network <<= overrides.network
+                                                       WHERE networks.network <<= overrides.network
                                                        ORDER BY masklen(overrides.network) DESC
                                                        LIMIT 1
                                        ),
                                        (
                                                SELECT is_satellite_provider FROM autnum_overrides overrides
-                                                       WHERE announcements.autnum = overrides.number
+                                                       WHERE networks.autnum = overrides.number
                                        ),
                                        FALSE
                                ) AS is_satellite_provider,
                                COALESCE(
                                        (
                                                SELECT is_anycast FROM network_overrides overrides
-                                                       WHERE announcements.network <<= overrides.network
+                                                       WHERE networks.network <<= overrides.network
                                                        ORDER BY masklen(overrides.network) DESC
                                                        LIMIT 1
                                        ),
                                        (
                                                SELECT is_anycast FROM autnum_overrides overrides
-                                                       WHERE announcements.autnum = overrides.number
+                                                       WHERE networks.autnum = overrides.number
                                        ),
                                        FALSE
-                               ) AS is_anycast,
-
-                               -- Must be part of returned values for ORDER BY clause
-                               masklen(announcements.network) AS sort_a,
-                               masklen(networks.network) AS sort_b
-                       FROM known_networks
-                               LEFT JOIN announcements ON known_networks.network <<= announcements.network
-                               LEFT JOIN networks ON known_networks.network <<= networks.network
-                       ORDER BY known_networks.network, sort_a DESC, sort_b DESC
+                               ) AS is_anycast
+                       FROM (
+                               SELECT
+                                       known_networks.network AS network,
+                                       announcements.autnum AS autnum,
+                                       networks.country AS country,
+
+                                       -- Must be part of returned values for ORDER BY clause
+                                       masklen(announcements.network) AS sort_a,
+                                       masklen(networks.network) AS sort_b
+                               FROM (
+                                               SELECT network FROM announcements
+                                       UNION ALL
+                                               SELECT network FROM networks
+                                       UNION ALL
+                                               SELECT network FROM network_overrides
+                                       ) known_networks
+                               LEFT JOIN
+                                       announcements ON known_networks.network <<= announcements.network
+                               LEFT JOIN
+                                       networks ON known_networks.network <<= networks.network
+                               ORDER BY
+                                       known_networks.network,
+                                       sort_a DESC,
+                                       sort_b DESC
+                       ) networks
                """)
 
                for row in rows: