query.sql

=> EXPLAIN ANALYZE SELECT * FROM routing_details r,
LATERAL (SELECT * FROM netblock_details n WHERE r.range <@ n.range ORDER BY upper(n.range) - lower(n.range) LIMIT 1) nb;

QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=71477.65..42350246739.30 rows=592496 width=91) (actual time=0.184..48941305.631 rows=592496 loops=1)
   ->  Seq Scan on routing_details r  (cost=0.00..11458.96 rows=592496 width=43) (actual time=0.006..658.266 rows=592496 loops=1)
   ->  Limit  (cost=71477.65..71477.65 rows=1 width=48) (actual time=82.596..82.597 rows=1 loops=592496)
         ->  Sort  (cost=71477.65..71580.47 rows=41128 width=48) (actual time=82.593..82.593 rows=1 loops=592496)
               Sort Key: ((upper(n.range) - lower(n.range)))
               Sort Method: top-N heapsort  Memory: 25kB
               ->  Bitmap Heap Scan on netblock_details n  (cost=1627.16..71272.01 rows=41128 width=48) (actual time=6.243..46.361 rows=33931 loops=592496)
                     Recheck Cond: (r.range <@ range)
                     ->  Bitmap Index Scan on idx_netblock_details_range  (cost=0.00..1616.88 rows=41128 width=0) (actual time=6.159..6.159 rows=33931 loops=592496)
                           Index Cond: (r.range <@ range)
 Total runtime: 48941880.284 ms
(11 rows)