1. 开启/关闭seq scan
set session enable_seqscan=true;
set session enable_hashjoin=false;
参考:http://www.cnblogs.com/gaojian/archive/2012/11/07/2758750.html
2.hash join
http://www.cnblogs.com/gaojian/archive/2012/11/08/2759874.html
3.merge join
http://momjian.us/main/writings/pgsql/optimizer.pdf
4.PostgreSQL Hardware Performance Tuning
http://momjian.us/main/writings/pgsql/hw_performance/index.html
5.书籍
http://momjian.us/main/writings/pgsql/aw_pgsql_book/
6. analyze 作用
postgres=# create table t1(id int);
CREATE TABLE
postgres=# insert into t1 select generate_series(1,1000);
INSERT 0 1000
postgres=# SELECT relname, relkind, reltuples, relpages FROM pg_class
WHERE relname='t1';
relname | relkind | reltuples | relpages
---------+---------+-----------+----------
t1 | r | 0 | 0
(1 row)
postgres=# analyze;
ANALYZE
postgres=# SELECT relname, relkind, reltuples, relpages FROM pg_class
WHERE relname='t1';
relname | relkind | reltuples | relpages
---------+---------+-----------+----------
t1 | r | 1000 | 4
(1 row)
不管甚么时候,如果你在增加或者更新了大量数据之后, 运行 ANALYZE 或者 VACUUM ANALYZE 都是个好习惯.这样就可以保证规划器有最新的表的数据的统计. 如果没有统计数据或者统计数据太陈旧,那么规划器可能选择很差劲的 查询计划,导致检索你的表的查询性能的恶化。
7. pg_dump 导出数据均分多份
使用 split.. 你可以用下面的方法把输出分解成操作系统可以接受的大小. 比如,让每个块大小为 1 兆字节:
pg_dump dbname | split -b 1m - filename
用下面命令恢复:
createdb dbname cat filename* | psql dbname
参考:http://www.php100.com/manual/pgsql/backup.html
8.单用户模式
postgres --single dbname
退出 ctrl+D
[wln@localhost ~]$ postgres --single postgres
PostgreSQL stand-alone backend 9.3beta2
backend> [wln@localhost ~]$
9. insert 返回打印信息
一个是last oid, 一个是有多少行被插入
postgres=# create table t2(id int) with oids;
CREATE TABLE
postgres=# insert into t1 values(1),(2);
INSERT 0 2
postgres=# insert into t2 values(1),(2);
INSERT 0 2
postgres=# insert into t1 values(1);
INSERT 0 1
postgres=# insert into t2 values(1);
INSERT 16389 1
参考:http://blog.163.com/digoal@126/blog/static/163877040201492452049196/
10.bytea 类型显示
postgres=# \d t4
Table "public.t4"
Column | Type | Modifiers
--------+---------+-----------
id | integer |
id2 | bytea |
postgres=# select * from t4;
id | id2
----+----------------
1 | \x31
2 | \x3232
2 | \x313233343536
(3 rows)
postgres=# set bytea_output = 'escape';
SET
postgres=# select * from t4;
id | id2
----+--------
1 | 1
2 | 22
2 | 123456
(3 rows)
11. 对vacuum, vacuum full的认识
简单说vacuum 只是将标记为删除的标记为可用,以待后来利用;vacuum full 会将标记删除的还给操作系统。下面的文章写的很好
http://www.cnblogs.com/stephen-liu74/archive/2012/05/23/2304155.html
12. 如何避免create index 时表锁定
create index concurrently.... 假如不加 concurrently 创建索引时只会扫描表1次,加了后会扫描2次。
13. create database
CREATE DATABASE db01
WITH OWNER = postgres
ENCODING = 'UTF8'
TABLESPACE = pg_default
LC_COLLATE = 'Chinese_People''s Republic of China.936'
LC_CTYPE = 'Chinese_People''s Republic of China.936'
CONNECTION LIMIT = -1;
14. begin transaction isolation level repeatable read
参考:http://blog.163.com/digoal@126/blog/static/163877040201326829943/
15.txid
select txid_snapshot_xmax(txid_current_snapshot())::text::xid;
Current transaction details:
txid_current(): int8
txid_current_snapshot(): txid_snapshot
Snapshot components:
txid_snapshot_xmin(snap): int8
txid_snapshot_xmax(snap): int8
txid_snapshot_xip(snap): SETOF int8
Visibility check:
txid_visible_in_snapshot(txid, snap): bool
http://www.pgcon.org/2008/schedule/attachments/55_pgq.pdf