间隔搜索有时候很慢,大部分原因是索引优化器不使用索引,并且在开始列和结束列比较独立。一个解决方案是使用空间索引,它可以把两个独立的值当做一个值来使用。
postgres=# EXPLAIN ANALYZE SELECT * FROM testip WHERE 19999999 BETWEEN startip AND endip;
QUERY PLAN ---------------------------------------------------------------- Seq Scan on testip (cost=0.00..19902.00 rows=200814 width=12) (actual time=3.457..434.218 rows=1 loops=1) Filter: ((19999999 >= startip) AND (19999999 <= endip)) Total runtime: 434.299 ms (3 rows) Time: 435,865 ms
结论:根据以上的执行计划,可以知道上边的查询使用的是序列扫描,花费的时间是:435,253)"> postgres=# CREATE INDEX ggg ON testip USING gist ((Box(point(startip,startip),point(endip,endip))) Box_ops);
使用如下的查询:
EXPLAIN ANALYZE SELECT * FROM testip WHERE Box(point(startip,endip)) @> Box(point (19999999,19999999),point(19999999,19999999));
结论:执行计划使用的是Bitmap Index Scan on ggg,花费的时间是:2,805 ms。可见相比以前的查询,使用空间索引的查询效率大大的提高了。
2.16进制到10进制的转换
我们已经有了系统函数将10进制转换成16进制:to_hex(11) result: b 下边的函数实现将16进制的数转换成10进制。非常的简单:
create or replace function to_dec(text) returns integer as $$ declare r int; begin execute E'select x''||$1|| E''::integer' into r; return r; end $$ language plpgsql;--测试
select to_dec('ff'); --结果
2553.ALTER TABLE ALTER COLUMN USING 语法
在Postgresql里边,我们不能将varchar类型直接转换到bool,但是我们可以使用Using语法加判断后进行转换。
CREATE TABLE foo(a varchar); INSERT INTO foo VALUES ('ano');--更改数据类型,会报错误信息
ALTER TABLE foo ALTER COLUMN a TYPE boolean; ERROR: column "a" cannot be cast to type "pg_catalog.bool"--使用Using语法更改数据类型
ALTER TABLE foo ALTER COLUMN a TYPE boolean USING CASE a WHEN 'ano' THEN true ELSE false END;--更改成功
SELECT * FROM foo;4.Quote_ident 的使用
使用双引号是一种防止sql注入的方法,quote_ident 可以检查参数,如果参数中包含任何非法的字符,它会在参数两边加上""
非常简单和有效,但是问题是schema.name,因为中间有点分割。问题如下:
select quote_ident('public.foo'); 他不能在schema和name两边加上双引号。
我们可以通过使用函数来按点分割上边的对象名称,在每个单独的对象上使用quote_ident来完成我们的目的:
--对数组进行表转换,针对每一列来使用quote_ident
CREATE OR REPLACE FUNCTION quote_array(text[]) RETURNS text AS $$ SELECT array_to_string(array(SELECT quote_ident($1[i]) FROM generate_series(1,array_upper($1,1)) g(i)), '.') $$ LANGUAGE sql IMMUTABLE;--创建函数按点进行拆分字符串
CREATE OR REPLACE FUNCTION quote_schema_ident(text) RETURNS text AS $$ SELECT quote_array(string_to_array($1,'.')) $$ LANGUAGE sql IMMUTABLE;--测试
select quote_schema_ident('public.foo tab');5.我们已经习惯使用Postgresql的exception来捕捉错误,但是错误信息一直不知道如何取得,sqlERRM变量可以给我们详细的信息
以下是一个具体的示例:
CREATE OR REPLACE FUNCTION fn_log_error(p_function varchar,p_location int,p_error varchar) RETURNS void AS $$ DECLARE v_sql varchar; v_return varchar; v_error varchar; BEGIN--连接数据库
PERFORM dblink_connect('connection_name','dbname=...');--拼凑插入的字符串
v_sql:= 'INSERT INTO error_log (function_name,location,error_message,error_time) ' || 'VALUES (''' || p_function_name || ''',' || p_location || ',''' || p_error || ''',clock_timestamp())';--远程执行
SELECT INTO v_return * FROM dblink_exec('connection_name',v_sql,false);--获取远程的错误信息
SELECT INTO v_error * FROM dblink_error_message('connection_name');--如果出现错误则抛出异常
IF position('ERROR' in v_error) > 0 OR position('WARNING' in v_error) > 0 THEN RAISE EXCEPTION '%',v_error; END IF; PERFORM dblink_disconnect('connection_name'); EXCEPTION WHEN others THEN--使用sqlERRM 来显示错误信息
PERFORM dblink_disconnect('connection_name'); RAISE EXCEPTION '(%)',sqlERRM; END; $$ LANGUAGE plpgsql SECURITY DEFINER;
sqlERRM是一个非常有用的变量,可以详细记录错误的具体信息,帮助我们分析执行中发现的错误。
6.循环优化技巧
plpgsql对于非sql操作效率不是特别高。Plpgsql 不喜欢字符或者字符数组的累计操作,当我们也不能用Perl,因此我们只能用sql
--使用循环,结果会比较慢的函数
CREATE OR REPLACE FUNCTION SlowList(int) -- slow function,usable for N <= 100 RETURNS varchar AS $$ DECLARE s varchar = ''; BEGIN FOR i IN 1..$1 LOOP s:= '<item>' || i || '</item>'; -- slow is s:= s || .. END LOOP; RETURN s; END; $$ LANGUAGE plpgsql IMMUTABLE;--使用sql,结果会比较快的函数
CREATE OR REPLACE FUNCTON FastList(int) -- fast function RETURNS varchar AS $$ BEGIN RETURN array_to_string(ARRAY(SELECT '<item>' || i || '</item>' FROM generate_series(1,$1) g(i)), ''); END; $$ LANGUAGE plpgsql IMMUTABLE;--结果:在循环100以下的时候差别并不是很大,当循环更多的时候,差距就非常明显,都来试试吧!
7.查询一组之中的头n条记录
我们一般的做法是使用子查询如下:
SELECT * FROM people WHERE id IN ( SELECT id FROM people s WHERE people.category = s.category ORDER BY age LIMIT 2) ORDER BY category,age; 使用连接我们也可以达到同样的效果如下:
SELECT s1.* FROM people s1 LEFT JOIN people s2 ON s1.category = s2.category AND s1.age < s2.age GROUP BY s1.id,s1.category HAVING COUNT(s2.id) <= 1 ORDER BY s1.category,COUNT(s2.id);
说明:这个sql语句的含义是找到同一类比自己的age大的记录,最后判断比自己大的记录的个数,如果是0,那么应该排名第一,
如果是1,那么排名第二(HAVING COUNT(s2.id) <= 1)
原文链接:https://www.f2er.com/postgresql/196706.html