On Wed, Sep 18, 2013 at 6:08 AM, Jayadevan M <maymala.jayadevan@xxxxxxxxx> wrote:
The function doesn't do much, code given below -
CREATE OR REPLACE FUNCTION geoip_city(IN p_ip bigint, OUT loc_desc character varying)
RETURNS character varying AS
$BODY$
SELECT l.id || l.country ||l.region || l.city FROM blocks b JOIN locations l ON (b.location_id = l.id)
WHERE $1 >= start_ip and $1 <= end_ip limit 1 ;
$BODY$
LANGUAGE sql IMMUTABLE
COST 100;There are indexes on the start_ip and end_ip and an explain tells me the indexes are being used (if I execute the SELECT in the function using a valid value for the ip value.
That construct is not efficiently indexable using two independent btree indexes. What did the plan look like that used the index? Two large bitmaps with a bitmap_and?
If you build ranges consisting of [start_ip, end_ip] and then build an index that specializes in range queries (GiST, I think) it should be able to do it efficiently, but you would have to rewrite the query to use a contains operator rather than two independent inequality tests.
Cheers,
Jeff
