PostgreSQL查询优化.pdf

PostgreSQL是一种特性非常齐全的自由软件的对象-关系型数据库管理系统（ORDBMS）支持大部分的SQL标准并且提供了很多其他现代特性，如复杂查询、外键、触发器、视图、事务完整性、多版本并发控制等。现在用户越来越多，对于SQL查询的优化可参考本文。常用的工具☆ PostgreSQL中的 EXPLAIN命令可以显示优化器最终为一个SQL语句生成的执行计划EXPLAIN (FORMAT JSON) SELECT FROM foo;QUERY PLAN执行计划的样子"P1an":{Node Type":Sec Scan",+Relation name foo,+1ias";"f。gStartup Cost":0.00,+"Tota1c。t":155,00,+Plan Rows: 10000Plan width: 4+(1 row)EXPLAIN SELECT sum(i)FROM foo WhERE i c 101QUERY PLANAggregate (cost-23.93.23.93 rows=1 width=4)>工 ndex Scan using fi on foo(cost=0.00··23.92xows=6 width=4)Index Cond: (i <10)3W吕EXPLAIN语法4EXPLAIN[( option Lmm])I statementEXPLAIN I ANALYZE II VERBOSE I statementoption可以是:ANALYZE I boolean IVERBOSE I boolean ICOSTS I boolean IBUFFERS I boolean ITIMING I boolean IFORMAT I TEXT XML JSON YAML J规划器使用的统计信息统计信息的一个部分就是每个表和索引中的项的总数,以及每个表和索引占用的磁盘块数☆保存在pg_das的 reltuple和 Irelpages列中SELECT relname, relkind, reltuples, relpagesFROM pg classWHERE relname like tenk1trelnameelkindreltuples relpages==二+二七enk1000035g七enk1 hundred1000030七enk1 thous tenthous1000030tenkl unique11000030tenkl unique21000030(5 rOWS令 reltuples和 relpages不是实时更新的用于估算选择度的信息6令更多的统计信息存放在 pg_statistic中,可以用于选择度估算等用途☆超级用户才能访问 pg_statistIc,一般用户可使用 pg_stats视图SELECT attname, inherited, n distinct rarray to string(most common vals, E\n)as most common valsFROM pc吕七a七swhere tablenameIiattname inherited n distinctm。g七。 hilToN VE⊥=+nEtTf0.3633881-580Ramp+工—君80Ramp+Sp RailroadT=58日0工=680RamphEt七0。2B4859I-880Ramp+工580Ramp+工=680Ramp+工580State Hwy 13Ramp(2 rows)影响执行代价的因素7◆单个关系的访问方式——不同的访问方式取元组的时间不同顺序访问( Sequential Access索引访问( Index access)TID元组访问◇多个关系间的连接顺序不同的连接顺序中间关系大小不同左连接( Left-handed)外关系是连接关系,内关系是基本关系右连接( Right- handed)—外关系是基本关系,内关系是连接关系。>布希连接( Bushy- handed)内外关系均为连接关系。如对表1,2,34:{1,2,3}和{4}连接即左连接;{1}和{2,3,4}即右连接;{1,2}和{34}连接即布希连接。◇两个关系间的连接方式—不同的连接方式时间连接时间不同嵌套循环连接( Nestloop Join)、归并连接( Merge Join)、哈希连接( Hash join)用显式JoIN子句控制规划器8令在一个简单的连接查询中,例如:SELECT FROM a, b, C WHERE aid b id And bref =cid;规划器可以自由地按照任何顺序连接给定的表:(a,b),C或(a,c),b规划器会对各种可能进行探索并尝试找出最高效的查询计划可能的连接顺序数随着表数目的增加成指数增长遗传搜索用时更少,但是并不一定会找到最好的计划用显式JoIN子句控制规划器9令可以指示 PostgresQL查询规划器将所有JON子句当作有连接顺序约束来对待SELECT FROM a, b, C WhERE a.id =b id AND bref =cid:SELECT★ from a cross join b CROSS JoIn C where a,id=b。 id nd b。ef=c。idselect FRoM a join (b JOIN C oN (b ref =cid))oN (aid =b id)i如果我们告诉规划器遵循JOIN的顺序,那么第二个和第三个还是要比第一个花在规划上的时间少把运行时参数 join_collapse_limi设置为1强制规划器遵循显式JOIN的连接顺序SELECT★ from a cRoSs Jo工Nb,crd, e where可能的连接顺序的数目减少了5倍批量导入数据10令禁用自动提交在使用多个 INSeRT时,关闭自动提交并且只在最后做一次提交避免为每一行的插入做很多额外的事务工作另一个好处:使数据插入原子化,不会只有部分数据被导入使用COPYCOPY命令是为装载大量行而优化过的,它比 INSERT更快无需关闭自动提交,也不会写WAL令使用预备 INSERT避免多次解析 INSERT语句但是无法避免写WAL