Thank you Adrian -
On Fri, Jan 11, 2019 at 4:55 PM Adrian Klaver <adrian.klaver@xxxxxxxxxxx> wrote:
On 1/11/19 4:50 AM, Alexander Farber wrote:
> https://www.db-fiddle.com/f/22jfWnsvqD8hVeFPXsyLbV/0
Why not put a test for the block in the function and then use different
UPDATE's depending on the result?
I didn't want to use IF and switch that statement to PL/pgSQL from pure SQL, so Andrew's answer
UPDATE users u SET
visited = now(),
ip = v.ip,
lat = i.lat,
lng = i.lng
FROM (VALUES ('20.20.20.20'::inet)) v(ip)
LEFT JOIN geoip i ON (v.ip <<= i.block)
WHERE u.uid = 2;
suits me better, even though I wonder what is the (VALUES ('20.20.20.20'::inet)) v(ip) construct there, some temporary table which is then LEGT JOINed to the geoip table?
Also, Andrew you have been right - with spgist index my queries against geoip are fast enough, I was looking at the wrong spot in my EXPLAIN ANALYZE output (the average values are slow, I am going to cache them soon)
Regards
Alex
