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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
