Re: Left Join with Limit 1

> 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