#### 自定义函数
```
CREATE
OR REPLACE FUNCTION getLongByIp (ip VARCHAR) RETURNS BIGINT AS $BODY$
DECLARE res BIGINT ; len INT ; arr BIGINT [];
BEGIN
res = 0 ; arr = regexp_split_to_array(ip, '\.') ; len = array_length(arr, 1) ;
IF len != 4 THEN
RETURN 0 ;
ELSE
res = res + (arr [ 1 ] << 24) ; res = res + (arr [ 2 ] << 16) ; res = res + (arr [ 3 ] << 8) ; res = res + arr [ 4 ]; RETURN res ;
END
IF ;
END ; $BODY$ LANGUAGE plpgsql;
```
#### 原SQL
```
SELECT
drf.*, ici.city
FROM
(
SELECT
*
FROM
d_runtime_feedback
WHERE
feedback_type = 'key_speed_up'
) drf
JOIN ip_china_ip ici ON (
getlongbyip (drf.ip) >= ici.ip_long_start
AND getlongbyip (drf.ip) <= ici.ip_long_end
)
```
#### 优化后的SQL
```
SELECT
tmp.*, ici.city
FROM
(
SELECT
*, getlongbyip (ip) AS iplong
FROM
d_runtime_feedback
WHERE
feedback_type = 'key_speed_up'
) tmp
JOIN ip_china_ip ici ON (
tmp.iplong >= ici.ip_long_start
AND tmp.iplong <= ici.ip_long_end
)
```