wget http://get.enterprisedb.com/postgresql/postgresql-9.2.1-1-windows-x64-binaries.zip initdb -D C:\Users\db2admin\Downloads\921win64\pgsql\data CREATE DATABASE test_en WITH OWNER =db2admin TEMPLATE template0 ENCODING = 'SQL_ASCII' TABLESPACE = pg_default LC_COLLATE = 'C' LC_CTYPE = 'C' CONNECTION LIMIT = -1; explain select max(a.name)from a where not exists(select 1 from b where b.id<50000 and b.id=a.id); explain analyze select max(a.name)from a where not exists(select 1 from b where b.id<50000 and b.id=a.id); Microsoft Windows [版本 6.1.7601] 版权所有 (c) 2009 Microsoft Corporation。保留所有权利。 C:\Users\db2admin>cd downloads\921win64\ C:\Users\db2admin\Downloads\921win64>cd pgsql\bin C:\Users\db2admin\Downloads\921win64\pgsql\bin>initdb -D C:\Users\db2admin\Downloads\921win64\pgsql\data 属于此数据库系统的文件宿主为用户 "db2admin". 此用户也必须为服务器进程的宿主. The database cluster will be initialized with locale "Chinese (Simplified)_People's Republic of China.936". Encoding "GBK" implied by locale is not allowed as a server-side encoding. The default database encoding will be set to "UTF8" instead. initdb: could not find suitable text search configuration for locale "Chinese (Simplified)_People's Republic of China.936" 缺省的文本搜索配置将会被设置到"simple" 创建目录 C:/Users/db2admin/Downloads/921win64/pgsql/data ... 成功 正在创建子目录 ... 成功 选择默认最大联接数 (max_connections) ... 100 选择默认共享缓冲区大小 (shared_buffers) ... 32MB 创建配置文件 ... 成功 在 C:/Users/db2admin/Downloads/921win64/pgsql/data/base/1 中创建 template1 数据库 ... 成功 初始化 pg_authid ... 成功 初始化dependencies ... 成功 创建系统视图 ... 成功 正在加载系统对象描述 ...成功 creating collations ... not supported on this platform 创建字符集转换 ... 成功 正在创建字典 ... 成功 对内建对象设置权限 ... 成功 创建信息模式 ... 成功 正在装载PL/pgSQL服务器端编程语言...成功 清理数据库 template1 ... 成功 拷贝 template1 到 template0 ... 成功 拷贝 template1 到 template0 ... 成功 WARNING: enabling "trust" authentication for local connections You can change this by editing pg_hba.conf or using the option -A, or --auth-local and --auth-host, the next time you run initdb. 成功. 您现在可以用下面的命令运行数据库服务器: ""postmaster -D "C:/Users/db2admin/Downloads/921win64/pgsql/data" 或者 ""pg_ctl -D "C:/Users/db2admin/Downloads/921win64/pgsql/data" -l logfile start C:\Users\db2admin\Downloads\921win64\pgsql\bin>pg_ctl -D "C:/Users/db2admin/Downloads/921win64/pgsql/data" -l logfile start server starting C:\Users\db2admin\Downloads\921win64\pgsql\bin>psql postgres psql (9.2.1) 输入 "help" 来获取帮助信息. postgres=# CREATE DATABASE test_en postgres-# WITH OWNER =db2admin postgres-# TEMPLATE template0 postgres-# ENCODING = 'SQL_ASCII' postgres-# TABLESPACE = pg_default postgres-# LC_COLLATE = 'C' postgres-# LC_CTYPE = 'C' postgres-# CONNECTION LIMIT = -1; CREATE DATABASE postgres=# \c test_en You are now connected to database "test_en" as user "db2admin". test_en=# create table a(id int primary key,name varchar(10)); 注意: CREATE TABLE / PRIMARY KEY 将要为表 "a" 创建隐含索引 "a_pkey" CREATE TABLE test_en=# create table b(id int not null,name varchar(10)); CREATE TABLE test_en=# insert into a select generate_series(1,1000000),'a'||generate_series(1,1000000); INSERT 0 1000000 test_en=# insert into b select generate_series(1,1000000),'b'||generate_series(1,1000000); INSERT 0 1000000 test_en=# analyze a; ANALYZE test_en=# analyze b; ANALYZE test_en=# explain select max(a.name)from a where a.id not in(select b.id from b where b.id<50000); QUERY PLAN ----------------------------------------------------------------------------- Aggregate (cost=9237419156.00..9237419156.01 rows=1 width=7) -> Seq Scan on a (cost=0.00..9237417906.00 rows=500000 width=7) Filter: (NOT (SubPlan 1)) SubPlan 1 -> Materialize (cost=0.00..18350.20 rows=49840 width=4) -> Seq Scan on b (cost=0.00..17906.00 rows=49840 width=4) Filter: (id < 50000) (7 行记录) test_en=# explain select max(a.name)from a where not exists(select 1 from b where b.id<50000 and b.id=a.id); QUERY PLAN --------------------------------------------------------------------------- Aggregate (cost=59554.42..59554.43 rows=1 width=7) -> Hash Anti Join (cost=18724.00..57179.02 rows=950160 width=7) Hash Cond: (a.id = b.id) -> Seq Scan on a (cost=0.00..15406.00 rows=1000000 width=11) -> Hash (cost=17906.00..17906.00 rows=49840 width=4) -> Seq Scan on b (cost=0.00..17906.00 rows=49840 width=4) Filter: (id < 50000) (7 行记录) test_en=# test_en=# explain analyze select max(a.name)from a where not exists(select 1 from b where b.id<50000 and b.id=a.id); QUERY PLAN --------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=59554.42..59554.43 rows=1 width=7) (actual time=1102.312..1102.312 rows=1 loops=1) -> Hash Anti Join (cost=18724.00..57179.02 rows=950160 width=7) (actual time=205.845..955.513 rows=950001 loops=1) Hash Cond: (a.id = b.id) -> Seq Scan on a (cost=0.00..15406.00 rows=1000000 width=11) (actual time=0.010..227.189 rows=1000000 loops=1) -> Hash (cost=17906.00..17906.00 rows=49840 width=4) (actual time=178.182..178.182 rows=49999 loops=1) Buckets: 4096 Batches: 2 Memory Usage: 883kB -> Seq Scan on b (cost=0.00..17906.00 rows=49840 width=4) (actual time=0.043..165.345 rows=49999 loops=1) Filter: (id < 50000) Rows Removed by Filter: 950001 Total runtime: 1102.617 ms (10 行记录) test_en=# create index idx_b_id on b(id); CREATE INDEX test_en=# analyze b; ANALYZE test_en=# explain select max(a.name)from a where a.id not in(select b.id from b where b.id<50000); QUERY PLAN -------------------------------------------------------------------------------------------------- Aggregate (cost=1139330656.00..1139330656.01 rows=1 width=7) -> Seq Scan on a (cost=0.00..1139329406.00 rows=500000 width=7) Filter: (NOT (SubPlan 1)) SubPlan 1 -> Materialize (cost=0.00..2153.20 rows=50171 width=4) -> Index Only Scan using idx_b_id on b (cost=0.00..1706.34 rows=50171 width=4) Index Cond: (id < 50000) (7 行记录)