On Wed, Sep 18, 2013 at 6:08 AM, Jayadevan M <maymala.jayade...@gmail.com>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
