Postgres Pro
Downloads
Home   >   mailing lists

Thread: Left Join with Limit 1

Left Join with Limit 1

From
Alex Magnum
Date:
Hello,

I am trying to extract ip addresses from golite by joining two tables as posted below. 

Running the queries individually and using a limit on the golite ip db results are back immediately 1-2ms but when using the first query it takes 2-3 seconds.

Is there a way to use a limit in the join?

Thanks for any advice on this.

A


SELECT S.referrer_ip,
       I.geoname_id
FROM viewing_stats AS S LEFT JOIN geolite_city_ip4 AS I ON (S.referrer_ip::inet <<= network)
WHERE viewing_id=74;

  referrer_ip   | geoname_id
----------------+------------
 111.93.173.230 |    1269750
(1 row)

Time: 2609.125 ms



SELECT geoname_id,network FROM geolite_city_ip4 WHERE '111.93.173.230'::inet <<= network LIMIT 1;
 geoname_id |     network
------------+-----------------
    1269750 | 111.93.168.0/21
(1 row)

Time: 1.926 ms


SELECT geoname_id,network FROM geolite_city_ip4 WHERE '111.93.173.230'::inet <<= network;
 geoname_id |     network
------------+-----------------
    1269750 | 111.93.168.0/21
(1 row)
Time: 645.999 ms

explain
SELECT S.referrer_ip,
       I.geoname_id
FROM viewing_stats AS S LEFT JOIN geolite_city_ip4 AS I ON (S.referrer_ip::inet <<= network)
WHERE viewing_id=74;
                                       QUERY PLAN
-----------------------------------------------------------------------------------------
 Nested Loop Left Join  (cost=0.00..242446.05 rows=3746698 width=36)
   Join Filter: ((s.referrer_ip)::inet <<= i.network)
   ->  Seq Scan on viewing_stats s  (cost=0.00..16.62 rows=3 width=32)
         Filter: (viewing_id = 74)
   ->  Materialize  (cost=0.00..74411.99 rows=2497799 width=11)
         ->  Seq Scan on geolite_city_ip4 i  (cost=0.00..49725.99 rows=2497799 width=11)
(6 rows)

Time: 1.326 ms

SELECT S.referrer_ip FROM viewing_stats AS S WHERE viewing_id=74;
  referrer_ip
----------------
 111.93.173.230
(1 row)

Time: 1.268 ms

Re: Left Join with Limit 1

From
Paul Jungwirth
Date:
> Running the queries individually and using a limit on the golite ip db
> results are back immediately 1-2ms but when using the first query it
> takes 2-3 seconds.
>
> Is there a way to use a limit in the join?

This sounds like the real issue is a missing/incorrect index, but if
you're on 9.4+ you can use a lateral join like this:


SELECT S.referrer_ip,
        I.geoname_id
FROM viewing_stats AS S
LEFT JOIN LATERAL (
     SELECT *
     FROM geolite_city_ip4
     WHERE S.referrer_ip::inet <<= network
     LIMIT 1
) I
ON true
WHERE viewing_id=74;

You might also want some kind of ordering in that subquery so that the
results are deterministic.


Paul

Re: Left Join with Limit 1

From
Jim Nasby
Date:
On 10/12/15 10:03 AM, Alex Magnum wrote:
> Is there a way to use a limit in the join?

SELECT ... FROM table_a a
   LEFT JOIN ( SELECT ... FROM table_b WHERE ... LIMIT 1) b
     ON a.blah = b.blah
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com