From: | Alex Magnum <magnum11200(at)gmail(dot)com> |
---|---|
To: | Postgres General <pgsql-general(at)postgresql(dot)org> |
Subject: | Left Join with Limit 1 |
Date: | 2015-10-12 15:03:08 |
Message-ID: | CA+cR4zdpctEi31uPupZwvCOZ5zcY1LUiGPxqKYY4Fv84qpr6Qw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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*
From | Date | Subject | |
---|---|---|---|
Next Message | Paul Jungwirth | 2015-10-12 15:12:58 | Re: Left Join with Limit 1 |
Previous Message | Steve Pribyl | 2015-10-12 14:37:07 | BDR workers exiting? |