PostgreSQL性能优化.doc

PostgreSQL性能优化1. 硬件数据库最重要的就是 I/O了。所以一切从I/O开始。RAID: 这个基本不用说，数据库放RAID10上面，只读的备份数据库可以放RAID0，反正挂了没关系。谨记：数据库是Random ReadRAID卡的选择：RAID卡一定要带电池的才可以（BBU）有电源的才能做到东西写进 CACHE，RAID就返回硬盘写成功（不用等）1. Areca2. LSI (真正的LSI，re-brand不要）3. HP P400 以上系列硬盘选择：首选是SAS: 15K RPM 每个SAS大约能提供25MB/s的Random Write。也就是说在RAID10的设定下，如果需要50MB/s的Random Write就需要个硬盘节俭选择是： SATA 可以多用几个硬盘（SAS一倍数量）达到在RAID10中接近SAS的速度。就算SATA买SAS一倍的数量，价格仍然比SAS便宜。也可以买 产品： 例如 Compaq的 MSA 70 (P800 Battery backed RAID control)CPU：64位Cache：越大越好 （现在个人电脑都3M的cache了）CORE：越多 越好 （postgresql毕竟是跑cpu的）建议最少4个coreRAM： 最少4G。通常根据具体需求，用16-64G的RAM2. OS （系统）可用系统：1. Debian Stable2. CentOS3. Ubuntu LTS4. Red Hat5. SUSE Enterprise如果准备付费（服务），那么就是 Canonical, Novell 跟 Redhat这三家选择而已如果准备不买任何服务，可以用Debian, CentOS, Ubuntu LTS这里还是觉得系统用Red Hat (不付费就CentOS）毕竟人家是企业级的老大哥，错不了。* 现在CentOS也可以买到服务了。不可用系统： 例如 fedora (redhat QA) ubuntu (non-LTS)Scheduler:Grub 增加： elevator=deadlineredhat 的图标可以看出，deadline是数据库的最佳选择文件系统 （Filesystem）这里的选择是：ext2，ext3 跟 ext4。 为什么只考虑这几个呢？因为数据库还是稳定第一，内核开发人员所做的文件系统，理论上说出问题的情况会少点。WAL： 放ext2 因为WAL本身自己有Journal了，不需要用ext3 （ext2快很多）data： ext3Block Size： postgres自己是8k的block size。所以文件系统也用8k的 block size。这样才能最佳的提高系统的效能。ext4：出来时间还 不够长，不考虑。分区 （Partitioning）Postgres 跟系统 OS 应该在不同分区系统（OS）：系统应该放独立的RAID1数据库 （Postgres Data）：数据库应该放独立的RAID10上。 如果RAID是带电池的，mount 的时候给 data=writeback的选项独立的数据库分区，就不许要记录文件时间了（都是放数据的）所以mount的时候要给noatime的 选项，这样可以节约更新时间(timestamp)的I/O了。WAL日志（xlogs）： 独立的RAID1上 （EXT2 系统）日志是 Sequential write，所以普通的硬盘（SATA）速度就足够了，没有必要浪费SAS在log上Postgresql 日志（logs）：直接丢给syslog就可以。最好在syslog.conf中设定单独的文件名. 这里 例如用local2来做postgresqllocal2.* -/var/log/postgres/postgres.log记得log要给Async，这样才不会等卡在log的I/O上， 同时记得设定logrotate以及创建路径（path）ext2 VS ext3 性能测试：HP DL5854 Dual Core 8222 processors64GB RAM(2) MSA70 direct attached storage arrays.25 spindles in each array (RAID 10)HP P800 Controller6 Disk in RAID 10 on embedded controllerxlog with ext3: avg = 87418.44 KB/secxlog with ext2: avg = 115375.34 KB/sec3. Postgres 内存 （Memory Usage)Shared Buffer CacheWorking MemoryMaintenance MemoryShared BuffersPostgres 启动时要到的固定内存。每个allocation是8k。 Postgres不直接做硬盘读写，而是把硬盘中的东西放入Shared Buffers，然后更改Shared Buffers，在flush 到硬盘去。通常 Shared Buffers设定为内存（available memory）的25%-40%左右。在系统（OS）中，记得设置 kernel.shmmax的值（/etc/sysctl.conf)kernel.shmmax决定了进程可调用的最大共享内存数量。简单的计 算方法是kernel.shmmax=postgres shared_buffers + 32 MB要保留足够的空间（不然会out of memory）postgresql除了shared buffer还会用到一些其他的内存，例如max_connections, max_locks_pre_transactionWorking Memory这个是postgres运行作业中 （task）需要的内存，例如内存内的hashed （aggregates， hash joins）sort （order by, distinct 等等）合理的设定，可以保证postgres在做这些东西的时候可以完全在内存内完成，而不需要把数据吐回到硬盘上去作swap。但是设定太大的话，会造 成postgres使用的内存大于实际机器的内存，这个时候就会去硬盘swap了。（效能下降）working memory是per connection and per sort的设定。所以设定一定要非常小心。举例来说，如果设定working memory为32MB，那么以下例子：select * from lines, lineitemswhere lines.lineid = lineitems.lineidand lineid=6order by baz;这里就可 能用到64MB的内存。hashjoin between lines and lineitems (32MB)order by baz (32MB)要注意自己有多少query是用到了order by或者join如果同时有100个链接，那么就是 100 connection X 64MB = 6400MB (6G) 内存通常来说，working mem不要给太大，2-4MB足够在postgres 8.3之后的版本，working mem可以在query中设定Query:begin;set work_mem to 128MB;select * from foo order by bar;insert into foo values (bar);reset work_mem;commit;Function:create function return_foo() returns setof text as$ select * from foo order by bar; $SET work_mem to 128MBLANGUAGE sqlpostgres官方不建议（但是支持）在 postgresql.conf文件中更改work_mem然后HUP （数据库应该没有任何中断）利用 explain analyze可以检查是否有足够的work_memsort (cost=0.02.0.03 rows=1 width=0) (actual time=2270.744.22588.341 rows=1000000 loops=1)Sort Key: (generate_series(1, 1000000)Sort Method: external merge Disk:13696kb- Result (cost=0.00.0.01 rows=1 width=0) (actual time=0.006.144.720 rows=1000000 loops=1)Total runtime: 3009.218 ms(5 rows)以上的 query分析显示，这里需要从硬盘走13MB的东西。所以这个query应给set work_mem到16MB才能确保性能。Maintenance Memory （维护内存）maintenance_work_mem 决定系统作维护时可以调用的内存大小。这个也是同样可以在query中随时设定。这个内存只有在VACUUM, CREATE INDEX 以及 REINDEX 等等系统维护指令的时候才会用到。系统维护是，调用硬盘swap会大大降低系统效能。通常maintenance_work_mem超过1G的时候并没有 什么实际的效能增加（如果内存够， 设定在1G足以）Background Writer (bgwriter)功能：负责定时写 shared buffer cache 中的 dirty shared buffers好处：a. 减少系统flush shared buffers到硬盘（已经被bgwriter做了）b. 在checkpoint中，不会看到I/O的突然性暴增，因为dirty buffers在背景中已经被flush进硬盘坏处：因为一直定时在背后flush disk，会看到平均硬盘I/O怎加（好过checkpoint时I/O暴增）设定：bgwriter_delay：sleep between rounds。 default 200（根据机器，数据而调整）bgwriter_lru_maxpages:决 定每次bgwriter写多少数据。如果实际数据大于这里的设定，那么剩余数据将会被postgres的进程（server process）来完成。server porcess自己写的数据会造成一定的性能下降。如果想确定所有的数据都由bgwriter来写，可以设定这里的值为-1bgwriter_lru_multiplier:采 用计算的方式来决定多少数据应该被bgwriter来写。这里保持内置的2.0就可以。计算bgwriter的I/O:1000 / bgwriter_delay * bgwriter_lru_maxpages * 8192 = 实际I/O(8192是 postgres的8k block）例如：1000/200 * 100 * 8192 = 4096000 = 4000 kbbgwrater 可以用 pg_stat_bgwriter 来监测。如果想要观察bgwrater 的运行状况，记得首先清理旧的stat信息。bgwriter如果设定的太大（做太多事情）那么就会影响到前台的效能 （server）但是如果由系统（server）来做buffer flush同样会影响效能。所以这里的最好设定就是通过观察 pg_stat_bgwriter 来找到一个最佳的平衡点。WAL （write ahead log)postgres中的所有写动作都是首先写入WAL，然后才执行的。这样可以确保数据的准确跟 完整。当中途数据库崩溃的时候，postgres可以通过WAL恢复到崩溃前的状况而不会出现数据错误等等问题。WAL 会在两种情况下被回写硬盘。1. commit。 当commit数据的时候，WAL会被强制写回硬盘（flush）并且所有这个commit之前的东西如果在WAL中，也会一同被flush。2. WAL writer进程自己会定时回写。FSYNC vs ASYNCpostgres 的 default 是做 fsync，也就是说postgres会等待数据被写入硬盘，才会给query返回成功的信号。如果设定sync=no关闭fsync的 话，postgres不会等待WAL会写硬盘，就直接返回query成功。通常这个会带来15-25%的性能提升。但是缺点就是，如果系统崩溃 （断电，postgres挂掉）的时候，你将有可能丢失最后那个transcation. 不过这个并不会造成你系统的数据结构问题。（no data corrupt）如果说在系统出问 题的时候丢失1-2笔数据是可以接受的，那么25%的性能提升是很可观的。WAL设定：fsync 可以选择on或者offwal_sync_method：linux中是使用fdatasync。其他的。不知道，应该是看系统的文 件参数了full_page_writes:开启的时候，在checkpoint之后的第一次对page的更改，postgres会将每 个disk page写入WAL。这样可以防止系统当机（断电）的时候，page刚好只有被写一半。打开这个选项可以保证page image的完整性。关 闭的时候会有一定的性能增加。尤其使用带电池的 RAID卡的时候，危险更低。这个选项属于底风险换取性能的选项，可以关闭wal_buffers:WAL 的储存大小。default 是 64 kb。 实验证明， 设定这个值在 256 kb 到 1 MB 之间会提升效能。wal_writer_delayWAL 检查WAL数据（回写）的间隔时间。值是毫秒（milliseconds)Checkpoints确保数据回写硬盘。dirty data page会被 flushed回硬盘。checkpoint 由以下3中条件激发 （bgwriter如果设定，会帮忙在后台写入，所以就不会有checkpoint时候的短期高I/O出现）1. 到达设定的WAL segments2. 到达设定的timeout3. 用户下达checkpoint指令如果 checkpoint运行频率高于checkpint_warning值。postgres会在日志（log）中记录出来，通过观察log，可以来决定 checkpoint_segments的设定。增加cehckpoint_segments或者checkpoint_timeout可以有一 定的效能提升。而唯一的坏处就是如果系统挂了，在重启的时需要多一点时间来回复（系统启动回复期间数据库是不能用的）鉴于postgres很少挂掉，这个 其实可以设定的很长（1天都可以）设定：checkpoint_segments 最多的wal log数量，到达后会激发checkpoint，通常设定在30就好checkpoint_timeout 一般设置15-20分钟，常的可以设定1天也没关系checkpoint_completion_target 这个保持不动就好。内建是0.5，意思就是每个checkpoint预计在下个checkpoint完成前的一半时间内完成（听起来有点绕嘴，呵呵）checkpoint_warning 如果checkpint速度快于这个时间，在log中记录。内建是30秒理论中的完美设定，就是你的backend从来不用回写硬盘。 东西都是由background来写入的。这个就要靠调整bgwriter, checkpoints跟wal到一个最佳平衡状态。当然这个是理想中的完美，想真的做到。继续想吧。呵呵4. 维护 保持postgres的笑容维护数据库是必 须的。基本维护vacuumdelete数据的 时候，数据库只是记录这笔数据是不要的并不是真的删除数据。所以这个时候就要vacuum了，vacuum会把标记为不要的数据清除掉。这里要注 意的是，vacuum不会清理index。当数据更改超过75%的时候，需要重新建立index。postgres 8.4 index可以用cluster重建速度快很多。在postgres 9.x中，vacuum=cluster，没有任何区别了（保留cluster只是为了兼容旧版指令）Full Vacuum这个会做exclusive lock。vacuum跟full vacuum的区别是vacuum会把标志为不要的空间标志成可以再次使用（回收）而 full vacuum则会把这个空间删除（返还给系统OS）所以vacuum之后你的postgres在硬盘上看到的占用空间不会减少，但是full vacuum会减小硬盘占用空间。不建议使用full vacuum，第一没必要，第二exclusive lock不好玩。ANALYZEAnalyze 会更新统计信息（statistics）所有的query的最佳方案，以及sql prepared statement都是靠这统计信息而决定的。所以当数据库中的一定量数据变动后（例如超过10%），要作analyze，严格的说，这个是应该常做的东 西，属于数据库正常维护的一部分。另外一个很重要的就是，如果是 upload数据（restore那种）做完之后要记得作analyze（restore自动不给你作的）当 建立新的table的时候，或者给table增加index，或者对table作reindex，或者restore数据进数据库，需要手动跑 analyze才可以。analyze直接影响default_statistics_target数据。Autovacuum根 据postgres的官方资料，autovacuum在8.3之后才变得比较真的实用（8.1推出的）因为在8.3之前，autovacuum一次只能同 时做一个数据库中的一个table。 8.3之后的版本，可以作多数据库多table。设定log_autovacuum_min_duration:-1 为关闭。0是log全部。0就是说超过这个时间的就log下来。例如设定为30，那么所有超过30ms的都会被日志记录。autovacuum_max_workers:同 时启用的autovacuum进程。通常不要设定太高，3个就可以。autovacuum_naptime:检查数据库的时 间，default是1分钟，不用改动autovacuum_vacuum_threshold:最低n行记录才会引发 autovacuum。也就是数据改变说低于这个值，autovacuum不会运行。default是50autovacuum_analyze_threshold:运 行analyze的最低值，跟上面的一样autovacuum_vacuum_scale_factor:table中的百分比的计算方 式（超过一定百分比作vacuum)内建是20% (0.2)autovacuum_analyze_scale_factor：同上， 不过是analyze的设定autovacuum_freeze_max_age:最大XID出发autovacuumautovacuum_vacuum_cost_delay:延 迟。如果系统负荷其他东西，可以让vacuum慢点，保证其他东西的运行.这里是通过延迟来限制autovacuum_vacuum_cost_limit:同 上，也是作限制的，这里是通过cost限制limitClusterCluster 类似于vacuum full。建议使用cluster而不是vacuum full。cluster跟vacuum full一样会重写table，移除所有的dead row。同样也是要做exclusive lock。TruncateTurncat 会删除一个table中的所有数据， 并且不会造成任何的dead row（delete则会造成dead row）同样的，turncate也可以用来重建tablebegin;lock foo in access exclusive mode;create table bar as select * from foo;turncate foo;insert into foo (select * from bar);commit;这样就重新清理了 foo这个table了。REINDEX重 新建立index5. 其他planner:statistics直接决定planner的结 果。使用planner，那么要记得确保statistics的准确（analyze）default_statistics_target:设定analyze分析的值。这个可以在 query中随时设定更改set default_statistics_target to 100;analyze verbose mytable;INFO: analyzing “aweber_shoggoth.mytable”INFO: “mytable”: scanned 30000 of 1448084 pages, containing 1355449 live rows and 0 dead rows; 30000 rows in sample, 65426800 estimated total rowsANALYZEset default_statistics_target to 300;analyze verbose mytable;INFO: analyzing “aweber_shoggoth.mytable”INFO: “mytable”: scanned 90000 of 1448084 pages, containing 4066431 live rows and 137 dead rows; 90000 rows in sample, 65428152 estimated total rowsANALYZESet statistics per column 给不同的column设定不同的 statisticsalter table foo alter column bar set statistics 120查找何时需要增加statistics跑 个query作expain analyze这个就会看到例如：- Seq Scan on bar (cost=0.00-52.00 rows=52 width=2 (actual time=0.007.1.894 rows=3600 loops=1)这里的rows应该跟真正的rows数量差不多才 是正确的。seq_page_costplanner 作sequential scan时候的cost。default是1，如果内存，cache，shared buffer设定正确。那么这个default的值太低了，可以增加random_page_costplanner 作random page fetch的值。default是4.0 如果内存，cache，shared buffer设定正确，那么这个值太高了，可以降低seq_page_cost跟random_page_cost的值可以设定成一样 的。然后测试效能，可以适当降低random_page_cost的值cpu_operator_costdefault 是0.0025，测试为，通常设定在0.5比较好set cpu_operator_cost to 0.5;explain analyze select .cpu_tuple_costdefault 是0.01 测试为，通常设定在0.5比较好set cpu_tuple_cost to 0.5;explain analyze select effective_cache应 该跟尽可能的给到系统free能接受的大小（越大越好）total used free shared buffer cachedmem: xxxx yyyyy zzz aaaa bbbb cccc设定的计算方法为：effective_cache=cached X 50% + shared这里的50%可以根据服务器的繁忙程度 在40%-70%之间调整。监测方法：explain analyze ;set effective_cache_size=新的值;explain analyze ;reset effective_cache_size;尝试出一个最适合的值，就可以改postgresql.conf文件设定成固定了。Natural vs Primary KeyPrimary Key 基本因为要做join，跟Natural相比多消耗20%左右的效能。所以尽力primary做在Natural key上。Btree vs hashbtree 比 hash 快，不管什么情况，所以不要用hashgin vs gist1，SQL查询方面检查数据检索的索引是否建立，凡是需要查找的字段尽量建立索引，甚至是联合索引；创建索引，包括表达式和部分索引；使用COPY语句代替多个Insert语句；将多个SQL语句组成一个事务以减少提交事务的开销；从一个索引中提取多条记录时使用CLUSTER；从一个查询结果中取出部分记录时使用LIMIT；使用预编译式查询（Prepared Query)；使用ANALYZE以保持精确的优化统计；定期使用 VACUUM 或 pg_autovacuum进行大量数据更改时先删除索引（然后重建索引）2，程序经验方面检查程序，是否使用了连接池，如果没有使用，尽快使用吧；继续检查程序，连接使用后，是否交还给了连接池；3，服务器参数配置配置文件postgres.conf中的很多设置都会影响性能，shared_buffers：这是最重要的参数，postgresql通过shared_buffers和内核/磁盘打交道。因此应该尽量大，让更多的数据缓存在shared_buffers中，通常设置为实际RAM的10是合理的，比如50000(400M)work_mem：在pgsql 8.0之前叫做sort_mem。postgresql在执行排序操作时，会根据work_mem的大小决定是否将一个大的结果集拆分为几个小的和work_mem查不多大小的临时文件。显然拆分的结果是降低了排序的速度。因此增加work_mem有助于提高排序的速度。通常设置为实际RAM的2%-4%，根据需要排序结果集的大小而定，比如81920(80M)effective_cache_size：是postgresql能够使用的最大缓存，这个数字对于独立的pgsql服务器而言应该足够大，比如4G的内存，可以设置为3.5G(437500)maintence_work_mem：这里定义的内存只是在CREATE INDEX, VACUUM等时用到，因此用到的频率不高，但是往往这些指令消耗比较多的资源，因此应该尽快让这些指令快速执行完毕：给maintence_work_mem大的内存，比如512M(524288)max_connections：通常，max_connections的目的是防止max_connections * work_mem超出了实际内存大小。比如，如果将work_mem设置为实际内存的2%大小，则在极端情况下，如果有50个查询都有排序要求，而且都使用2的内存，则会导致swap的产生，系统性能就会大大降低。当然，如果有4G的内存，同时出现50个如此大的查询的几率应该是很小的。不过，要清楚max_connections和work_mem的关系。4，硬件的选择由于计算机硬件大多数是兼容的，人们总是倾向于相信所有计算机硬件质量也是相同的。事实上不是， ECC RAM（带奇偶校验的内存），SCSI （硬盘）和优质的主板比一些便宜货要更加可靠且具有更好的性能。PostgreSQL几乎可以运行在任何硬件上，但如果可靠性和性能对你的系统很重要，你就需要全面的研究一下你的硬件配置了。计算机硬件对性能的影响可浏览http:/candle.pha.pa.us/main/writings/pgsql/hw_performance/index.html和http:/www.powerpostgresql.com/PerfList/。配置文件是数据库目录下的postgresql.conf文件，8.0以后的版本可支持K，M，G这样的参数，只要修改相应参数后重新启动PG服务就OK了。查询条件优化在SQL中，查询条件在查询优化阶段需要被分成三种类型，三类条件有不同的作用，在某些情况下，可以相互转化。首先说明一下SQL语句的执行步骤，可以分为三步：一，读取表中的元组；二，如果有JOIN，则开始做JOIN；三，针对WHERE条件作过滤。我们以简单的SQL为例：表TBL(c1 int, c2 int, c3 int);有SQL语句 ：SELECT A.c1, B.c3 FROM TBL A , TBL B WHERE A.c1 = B.c3 AND A.c2 = 4;针对上面的SQL语句，执行过程为:一，读取A，B表中的每一条元组；二，将A，B的各个元组作笛卡尔积；三，使用WHERE条件对连接的结果作过滤。针对上述执行的三个阶段，我们将SQL中的条件也分为三类：A类，表的过滤条件，对基表读取出来的元组进行过滤；B类，表的连接条件，两个表作连接，以它为连接条件；C类，连接的过滤条件，连接完成后，对连接的结果按该条件过滤。对上述SQL语句而言，它与下面的SQL语句等价：SELECT A.c1, B.c3 FROM (SELECT * FROM TBL WHERE A.c2 = 4) A INNER JOIN TBL B ON (A.c1 = B.c3) WHERE true;则将原SQL语句的WHERE中的两个条件，一个下推到基表上作为表的过滤条件(A.c2 = 4)，另一个下推到两个表，作为它们的连接条件(A.c1 = B.c3)。而此时，连接的过滤条件为WHERE子句上的true，也即没有连接的过滤条件。在针对表之间的连接为内连接时，WHERE条件上的各个以AND连接的表达式可以随便地下推到基表作基表的过滤条件或者是连接上作连接条件，但当连接不为内连接时，该下推是不正确的， 参看下面的示例：表TBL_1 (C1 int, c2 int); 有元组(1, 1), (2, 2)表TBL_2 (c1 int, c2 int); 有元组(1, 1), (null, null)则此时SELECT * FROM TBL_1 LEFT JOIN TBL_2 ON true WHRER TBL_1.c1 = TBL_2.c2;它的查询结果为：(1, 1, 1, 1)而将WHERE条件下推到连接条件时，即SQL语句为SELECT * FROM TBL_1 LEFT JOIN TBL_2 ON (TBL_1.c1 = TBL_2.c2) WHRER true;则它的查询结果为(1, 1, 1, 1), (2, 2, null, null)。它们结果的不同是由于(TBL_1.c1 = TBL_2.c2)作为连接条件时，代表的意义是：对TBL_1中的一条元组，如果能与TBL_2中的任何一条元组使得(TBL_1.c1 = TBL_2.c2)值为TRUE，则将其连接，返回；如果没有，则将TBL_1的元组输出，右端补空。而当这个条件作为连接的过滤条件时，它会将(2, 2, null, null)过滤掉，因为它不满足该连接的过滤条件。表的过滤条件，连接条件，连接过滤条件。在SQL优化时，尽可能地将条件下推，以减少参与连接的表的大小或者连接的结果集大小。 下面先介绍SQL语句执行时整个SQL语句的执行层次：TABLE LEVEL | JOIN_LEVEL | AGGREGATE_LEVEL- - -TABLE A (Filter_1) = JOIN (Filter_2) = (Filter_3) AggregateTABLE B (Filter_1) WHERE条件在优化前，不管它的每个条件是否涉及到表，也不管是否涉及到哪个表，都应该作为一个整体，处于Filter_3的位置上。 在说明了WHERE条件在优化前所处的位置后，开始对WHERE中各个条件进行优化：假设：WHERE子句中的各个表达式都是由AND连接的，即有形如：Expr_1 AND Expr_2 AND . AND Expr_n 考虑对各个条件表达式Expr_i的下推。 说明：表达式Expr涉及到表A，即在Expr中存在表A中的列。1. JOIN为内连接 JOIN类型为内连接时，如：SELECT * FROM TBL_1, TBL_2 WHERE . 或者是形如SELECT * FROM TBL_1 INNER JOIN TBL_2 ON TURE WHERE . 对一个条件表达式Expr_i，如果只涉及到一个表，则下推到Filter_1的位置作为表的过滤条件；如果涉及到多个表，则下推到Filter_2的位置上作为所涉及到的表的连接条件；2.JOIN为外连接 当JOIN为外连接中的FULL JOIN，则无法将任何WHERE中的条件下推，即不能优化。 当JOIN为左外连接或者右外连接时，首先说明一下左外连接，右外连接的可空端(nullable side)，非可空端(nonnullable side)。 以R LEFT JOIN T ON (quals)为例，对R中的每一条元组r，如果T中有元组t使quals值为TRUE，则连接r与t，如果T中没有，则返回r，并在T的列上补NULL，因而对左外连接来说，LEFT JOIN的右端(Right Hand Side， RHS，同样，LEFT JOIN的左端记为LHS)为可空端，LEFT JOIN的LHS为不可空端。同样，对RIGHT JOIN而言，RHS为不可空端，LHS为可空端。 下面以LEFT JOIN为例，说明此时WHERE中各表达式Expr_i的下推。 a)Expr_i涉及到外连接的nullable side,既不能下推到Filter_2的位置作为连接条件，也不能下推到Filter_1的位置作为表的过滤条件。 b)Expr_i涉及到外连接的nonnullable side,则可以将该条件下推到Filter_1上作为该左外连接的LHS的表的过滤条件，但不能下推到Filter_2位置作为表的连接条件。 当左外连接或者右外连接存在连接条件时，对连接条件的各个Expr_i的下推满足下面的规律： a)Expr_i涉及到nullable side，可以将该条件下推到nullable side的表上作为表的过滤条件，但不能下推到nonnullable side。 b)Expr_i涉及到nonnullable side，该条件不能下推。补充：上述所有条件的下推，都是在如下前提下，即对下推的Expr_i，该Expr_i必须是Strict(严格的)的。所谓Strict的是指，一个表达式Expr_i，如果输入的列的值存在NULL值，则该表达式的返回值必定为NULL，比如有表达式 A != B，如果A的值为3，B的值为NULL，则该表达式返回值为NULL，而不是FALSE。 索引可以增加查询效率，而该参数的设置会影响数据库是否积极地使用索引。 默认的索引是树状结构，根据索引来检索数据的时候，是根据索引的匹配结果再到实际表中的对应位置读取数据。从磁盘的角度来讲，这种读取数据的方式叫做乱序读取（正式叫法不清楚.）。 相反，不利用索引，直接扫描表的方式叫做顺序读取。 乱序读取当然比顺序读取要慢。而postgresql用random_page_cost参数来设置乱序读取要比顺序读取慢多少（倍）。 现在我们使用的新服务器的内存比数据库要大3倍，所以理论上所有的数据都能放入内存。这样的话，乱序读取和顺序读取的效率不会差多少。而默认的4倍就不合适了。这次我们把它设置为1，也就是说和顺序读取一样的效率，让数据库积极的使用索引来检索数据。 另外，effective_cache_size参数是告诉数据库，OS的缓存大小。越大，数据库使用索引的积极性就越高。因为数据很可能在os的缓存里，乱序读取的效率也不差。这个值理论上等于OS可以使用的缓存大小。 验证方法： 一个很简单的方法就是查看是否使用了Bitmap位图扫描规划。 位图扫描规划是Bitmap Index Scan和Bitmap Heap Scan的组合。先用Bitmap Index Scan通过索引，把匹配结果放到位图表（这个表会使用work_mem设置的大小，超过就要写入到临时文件，影响效率），在通过这个位图表循序读取数据库表（Bitmap Heap Scan），返回最终结果。 从理论上来说，性能按照 索引扫描 位图扫描 顺序扫描 的顺序由高到低排列。 如果你的服务器的内存没这么富裕，可以通过pg_statio_user_tables和pg_statio_user_indexes表查看每个表和索引的缓存程度。 根据表的缓存程度，调节相关的SQL语句查询时的设置值。读数据时：数据库先在共享内存里查找，找不到的话，再检索磁盘。检索磁盘时先查OS的缓存，找不到的话才会实际扫描磁盘。然后把数据放到OS的缓存，再返回给数据库，也就是放到共享内存里面。 按照这个逻辑，同一份数据，同时存在于OS的缓存和数据库的共享内存里面。 如果OS的缓存太小的化，就会放生swap，把数据放到磁盘里，当然效率也就会降低了。写数据时： 和读数据相反，先更新共享内存里面的内容，攒到一定数量或者到了一定时间段，再把更新的数据反应给OS。OS也是先更新到缓存，再实际更新到磁盘里。 如果需要更新的数据超过了OS的缓存，更新处理会等OS整理缓存以得到可利用的空间。 更新处理比较多的数据库，或者vacuum处理的时候，需要占用大量OS缓存。一定时间内无法确保到需要的空间时，数据库服务也有可能会停止。综上所述，共享内存的值与OS的缓存一致是比较安全的。如果以检索为主的话，共享内存大一些也没什么问题吧。当然前提是数据库基本都能放到共享内存里。查询类的几个参数可以用以下的关系表示。物理内存 work_mem 最大同时连接数 平均使用work_mem数 shared_buffers effective_cache_size以上公式只适用于数据库专用服务器的场合。 向磁盘强制更新 WAL 数据的方法。在linux环境里可以使用fsync，fdatasync，open_sync三种方式。那种方式最快呢？可以用postgresql代码里面的test_fsync这个工具来测试。8.3版本以后才有这个工具好像。$ cd (源代码)postgresq-8.4.5/src/tools/fsync$make$./test_fsync然后设置为其中最快的一种方式就行了。例子：open o_sync, write 8.xxxwrite, fdatasync 4.xxxxwrite, fsync 4.xxxx默认的fsync方式最快。 这个结果随着硬件和OS的不同会有很大的变化，系统有什么变更的时候，最好执行以下看看更新类：checkpoints相关参数checkpoint就是把共有内存里面更新过的数据写道磁盘（磁盘缓存）里面的处理。有两个参数控制写入的时机和频率，两个参数同时有效，满足任何一个条件都会执行写入处理。（应该是。_;）checkpoint_segments：定量，写入大小。 一个是16MB，默认是3，所以就是48MB。到了这个量就会调用写入处理。checkpoint_timeout：定时，写入间隔。默认是5min（五分种）。过了5分钟就调入写入处理。这两个参数很容易理解，而判断设置的合适不合适，就要看实际的运用了。如果日志文件里面频繁出现下列的提示，就要考虑调整上述两个参数了。HINT: Consider increasing the configuration parameter checkpoint_segments.LOG: checkpoints are occurring too frequently (25 seconds apart)这个提示说明，共享内存里面的数据更新的很频繁，就算写入磁盘之后，马上又会被更新。这时就可以加大一下写入间隔和写入大小，再看看效果。但是如果在vacuum或者在定时处理的时候出现上述提示的话，基本可以不理会。另外还有一个参数，checkpoint_completion_target，是控制写入处理的分步程度的。默认是0.5，也就是说，一次需要写入的内容，实际上不是一次全部完成，而是在写入间隔中分步完成的。例如：checkpoint_timeout是5分钟，而checkpoint_completion_target是0.5的话，那么实际上是用了2分30秒来分步写入变更过的数据。每次写入不需要大量的缓存，可以加快写入效率。当然是越分步越好了，所以推荐设置成0.9。 当然不能超过1啦，会跟下一次的写入发生冲突。更新类：wal_buffers参数WAL数据用到的内存大小。将使用OS的内存，而不是postgresql的空闲内存。更新大量数据的时候，写入处理会使用这个空间，加快写入速度。 更改之后需要重新启动数据库，所以事先设置得大一点比较方便。比如5MB。1.log_min_duration_statement从log找出执行超过一定时间的SQL。postgresql.conf配置文件设置log_min_duration_statement参数的值。这个参数是设置执行最小多长时间的SQL输出到log。例如输出执行超过3秒的SQL:log_min_duration_statement = 3s这个参数设置为-1是无效。设置为0是输出所有的SQL，但这样会增加服务器负担，一般不要设置太低的值。这样设置后输出的SQL例子如下：LOG: duration: 3016.724 ms statement: SELECT count(*)FROM pg_class2.contrib/auto_explain功能。Postgres8.4后增加的功能。默认这个功能不能使用的，需要在postgresql.conf 配置文件中设置以下参数。shared_preload_libraries = auto_explaincustom_variable_classes = auto_explainauto_explain.log_min_duration = 4s这样系统在执行的时候如果遇到超过4秒的SQL的话，会自动把执行计划输出到log。这样就直接看log就更加容易找到问题点。 执行计划例子： LOG:duration: 4016.724 msplan:Aggregate(cost=14.90.14.91 rows=1 width=0)- Hash Join(cost=3.91.14.70 rows=81 width=0)Hash Cond: (pg_class.oid = pg_index.indrelid)- Seq Scan on pg_class(cost=0.00.8.27 rows=227 width=4)- Hash(cost=2.90.2.90 rows=81 width=4)- Seq Scan on pg_index(cost=0.00.2.90 rows=81 width=4)Filter: indisuniqueSTATEMENT:SELECT count(*)FROM pg_class, pg_indexWHERE oid = indrelid AND indisunique;3.log统计分析工具（PostgreSQL log analyzer）比较有名是pgFouine。这个工具是自动分析指定的log，然后生成HTML报表。把SQL log图像化后更加直观。可以统计分析最慢的SQL，调用最多的SQL，花费时间最多的SQL等等分类。这样我们就很容易找到速度慢的SQL。再加以改善。PostgreSQL：运用管理篇由于很多人对PostgreSQL运用管理中要做什么不是很清楚。本篇大体介绍一下运用管