如果我在同一列上创建两个（或更多）不同类型的索引，PostgreSQL 会如何表现？
在我的例子中，我想将 B-Tree 索引与tsvector列上的 GIN 进行比较。我知道 GIN 专门用于tsvector. 奇怪的是，如果我创建 B-Tree 索引，PostgreSQL 不会抱怨，但我发现查询计划器没有使用它。我还可以创建一个 GIN 索引（无需删除 B-Tree 索引），现在规划器使用新创建的索引。该列现在有两个索引，但只使用了其中一个。
即使存在两个以上的索引，用于选择一个索引类型与另一个索引类型的标准是什么？为什么 PostgreSQL 不告诉我 a 上的 B-Tree 索引tsvector是无用的并且永远不会被规划器使用？
更新
GIN 索引仅用于某些检查条件，my_tsvector IS NOT NULL但（显然）不用于my_tsvector @@ '...'::tsquery.
我有许多相同长度（行数）的表，这些表以前在写入磁盘时按某些列排序。
我SELECT的形式是：
SELECT t1.id, t1.value, t2.value, t3.value, ...
FROM t1 LEFT JOIN t2 ON t1.id=t2.id
LEFT JOIN t3 ON t1.id=t3.id
...
我知道这些表是如何填充的，这个结果完全对应于简单的列绑定，因此ON t1.id=t2.id可以ON t1.id=t3.id省略。问题是，据我所知，RDBMS 只能操作投影和行绑定。列绑定不是典型的操作。
所以问题如下：是否可以在不指定任何连接条件的情况下“按原样”绑定列？例如，以下语法错误的查询应该解释我的意思：
SELECT t1.id, t1.value, t2.value, t3.value, ...
FROM t1 COLUMN_BIND t2 COLUMN_BIND t3 ...
我正在使用 PostgreSQL 9.4.5。
输入表：
t1.id | t1.value
----------------
1 | a
2 | a
3 | b
4 | a
5 | c
6 | a
t2.id | t2.value
----------------
1 | g
2 | g
3 | h
4 | g
5 | o
6 | l
t3.id | t3.value
----------------
1 | e
2 | e
3 | e
4 | e
5 | q
6 | e
预期结果：
t1.id | t1.value | t2.value | t3.value
--------------------------------------
1 | a | g | e
2 | a | g | e
3 | b | h | e
4 | a | g | e
5 | c | o | q
6 | a | l | e
我在 PostgreSQL 9.4.5 上遇到锁定问题。我同时运行两个或多个事务（通常有 2 个同时运行，但有时会发生 2 个以上）从相同的表读取但在不同的输出表上写入。在我的交易中，我有几个声明某些功能的语句DROP FUNCTION IF EXISTS。CREATE OR REPLACE FUNCTION
我的猜测是，DROP FUNCTION IF EXISTS并且CREATE OR REPLACE FUNCTION正在锁定。我对吗？我可以通过将语句移到事务之外来解决问题。
我观察到以下情况：两个事务 A 和 B 包含一些DROP FUNCTIONand CREATE OR REPLACE FUNCTION；如果我在事务 A 仍在运行时执行事务 B，则事务 B 会等待DROP FUNCTION它遇到的第一条语句。
我正在运行 PostgreSQL 9.4.5，我刚刚从这个查询中得到了一个意外的结果：
select upper(('[2005-12-01,2005-12-04]')::daterange)
它返回2005-12-05而不是2005-12-04.
)以下查询通过指定而不是来排除上限]：
select upper(('[2005-12-01,2005-12-04)')::daterange)
它返回2005-12-04。
该upper()函数不应该返回给定范围的上限吗？如果是这样，通过指定[2005-12-01,2005-12-04]为范围，它应该返回上限，即2005-12-04. 它2005-12-05反而返回，这对我来说很奇怪。
关于为什么会发生这种情况的任何解释？我只知道它'[2005-12-01,2005-12-04]'::daterange被 PostgreSQL 翻译成'[2005-12-01,2005-12-05)'::daterange，但这并不能解释为什么2005-12-05当我明确指定2005-12-04为上限时应该是上限。
我目前正在运行一个 OS X Lion Server 系统，该系统附带一个内置且不可升级的 PostgreSQL 版本。经过多年的使用，我最终决定离开内置版本并安装一个独立版本。我禁用了内置安装并从 EDB 下载了安装程序并按照向导进行操作。在许多关于编码和语言环境的问题之后，我终于设法设置一个没有语言环境和 UTF8 编码的数据库。我发出了以下命令：
initdb -D /path/to/data --no-locale --encoding=UTF8
如果我使用 pgAdminIII 连接，我不会遇到任何问题。该命令show client_encoding;显示UNICODE为 pgAdminIII 使用的编码（默认安装给了我一个SQL_ASCII编码，这就是我运行该initdb命令的原因）。
问题是我无法使用psql. 无论我传递给它，我都会收到以下错误：
psql: invalid connection option "client_encoding"
我在 Internet 上进行了搜索，但没有找到任何可以解决我的问题的方法（例如发布env PGCLIENTENCODING=UTF8和添加client_encoding=UTF8到postgresql.conf）。
otool -L /Library/PostgreSQL/9.4/bin/psql返回：
/Library/PostgreSQL/9.4/bin/psql:
@loader_path/../lib/libpq.5.dylib (compatibility version 5.0.0, current version 5.7.0)
@loader_path/../lib/libssl.1.0.0.dylib (compatibility version 1.0.0, current version 1.0.0)
@loader_path/../lib/libedit.0.dylib (compatibility version 1.0.0, current version 1.48.0)
/usr/lib/libSystem.B.dylib (compatibility version 1.0.0, current version 169.3.0)
任何人都可以帮我弄清楚吗？
非常感谢所有 Pietro
我在编辑 bash 配置文件后忘记注销然后登录。Daniel Vérité 的建议是正确的。我只是编辑了DYLD_LIBRARY_PATHenv 变量，/etc/profile以使其在全局级别可见，而不仅仅是从交互式 shell 中可见。
我将以下行添加到/etc/profile：
export DYLD_LIBRARY_PATH='/Library/PostgreSQL/9.4/lib'
我希望这对其他人有帮助。
丹尼尔，非常感谢
几个月来我一直在尝试解决 PostgreSQL 的性能问题。
我们的部署机器是 Dell PowerEdge T420，配备 Perc H710 RAID 控制器，配置如下：
该系统具有以下配置：
我个人的低成本低配置开发机器是这样配置的 MacMini：
到目前为止，我已经对两台机器进行了大量调优调整，包括官方 Postgres 文档站点上的内核推荐调整。
部署机器运行一个 web 平台，该平台指示 Postgres 进行超过十亿条记录的大交易。这是一个为一个用户设计的平台，因为由于数据大小，系统资源必须尽可能多地专用于一个单一的工作（我不喜欢称它为大数据，因为大数据是十亿的数量级）。
我发现部署机器比开发机器慢很多。这是矛盾的，因为两台机器在很多方面确实不同。我运行了很多查询来调查这种奇怪的行为，并做了很多调优调整。
在过去的两个月里，我准备并执行了两种类型的查询集：
SELECT ... INTO、CREATE INDEX和。CLUSTERVACUUM ANALYZESELECT集合 A 创建的表。A 和 B 在 T420 上总是比较慢。唯一更快的操作类型是VACUUM ANALYZE.
A型套装：
postgresql.conf）到 195 秒，对 RAID、内核和进行调优调整postgresql.conf；B型套装：
我不得不提一下，我们还调整了 T420 上的 BIOS，将所有可能的参数设置为“性能”并禁用低能耗配置文件。这将 A 类集的执行时间从 240 秒减少到 211 秒。
我们还将所有固件和 BIOS 升级到最新可用版本。
以下是使用生成的两个基准pg_test_fsync：
T420pg_test_fsync
60 seconds per test
O_DIRECT supported on this platform for open_datasync and open_sync.
Compare file sync methods using one 8kB write:
(in wal_sync_method preference order, except fdatasync
is Linux's default)
open_datasync 23358.758 ops/sec 43 usecs/op
fdatasync 21417.018 ops/sec 47 usecs/op
fsync 21112.662 ops/sec 47 usecs/op
fsync_writethrough n/a
open_sync 23082.764 ops/sec 43 usecs/op
Compare file sync methods using two 8kB writes:
(in wal_sync_method preference order, except fdatasync
is Linux's default)
open_datasync 11737.746 ops/sec 85 usecs/op
fdatasync 19222.074 ops/sec 52 usecs/op
fsync 18608.405 ops/sec 54 usecs/op
fsync_writethrough n/a
open_sync 11510.074 ops/sec 87 usecs/op
Compare open_sync with different write sizes:
(This is designed to compare the cost of writing 16kB
in different write open_sync sizes.)
1 * 16kB open_sync write 21484.546 ops/sec 47 usecs/op
2 * 8kB open_sync writes 11478.119 ops/sec 87 usecs/op
4 * 4kB open_sync writes 5885.149 ops/sec 170 usecs/op
8 * 2kB open_sync writes 3027.676 ops/sec 330 usecs/op
16 * 1kB open_sync writes 1512.922 ops/sec 661 usecs/op
Test if fsync on non-write file descriptor is honored:
(If the times are similar, fsync() can sync data written
on a different descriptor.)
write, fsync, close 17946.690 ops/sec 56 usecs/op
write, close, fsync 17976.202 ops/sec 56 usecs/op
Non-Sync'ed 8kB writes:
write 343202.937 ops/sec 3 usecs/op
苹果机pg_test_fsync
60 seconds per test
Direct I/O is not supported on this platform.
Compare file sync methods using one 8kB write:
(in wal_sync_method preference order, except fdatasync
is Linux's default)
open_datasync 3780.341 ops/sec 265 usecs/op
fdatasync 3117.094 ops/sec 321 usecs/op
fsync 3156.298 ops/sec 317 usecs/op
fsync_writethrough 110.300 ops/sec 9066 usecs/op
open_sync 3077.932 ops/sec 325 usecs/op
Compare file sync methods using two 8kB writes:
(in wal_sync_method preference order, except fdatasync
is Linux's default)
open_datasync 1522.400 ops/sec 657 usecs/op
fdatasync 2700.055 ops/sec 370 usecs/op
fsync 2670.652 ops/sec 374 usecs/op
fsync_writethrough 98.462 ops/sec 10156 usecs/op
open_sync 1532.235 ops/sec 653 usecs/op
Compare open_sync with different write sizes:
(This is designed to compare the cost of writing 16kB
in different write open_sync sizes.)
1 * 16kB open_sync write 2634.754 ops/sec 380 usecs/op
2 * 8kB open_sync writes 1547.801 ops/sec 646 usecs/op
4 * 4kB open_sync writes 801.542 ops/sec 1248 usecs/op
8 * 2kB open_sync writes 405.515 ops/sec 2466 usecs/op
16 * 1kB open_sync writes 204.095 ops/sec 4900 usecs/op
Test if fsync on non-write file descriptor is honored:
(If the times are similar, fsync() can sync data written
on a different descriptor.)
write, fsync, close 2747.345 ops/sec 364 usecs/op
write, close, fsync 3070.877 ops/sec 326 usecs/op
Non-Sync'ed 8kB writes:
write 3275.716 ops/sec 305 usecs/op
这证实了 T420 的硬件 IO 功能，但不能解释为什么 MacMini 更快。
有任何想法吗？
B 型集是交易，所以我不可能发布EXPLAIN ANALYZE结果。我从单个事务中提取了两个查询，并在两个系统上执行了两个查询。以下是结果：
T420
查询 B_1 [55999.649 毫秒 + 0.639 毫秒] http://explain.depesz.com/s/LbM
查询 B_2 [95664.832 毫秒 + 0.523 毫秒] http://explain.depesz.com/s/v06
苹果机
查询 B_1 [56315.614 毫秒] http://explain.depesz.com/s/uZTx
查询 B_2 [44890.813 毫秒] http://explain.depesz.com/s/y7Dk
我使用 gcc-4.9.1 和 Postgres 的不同参数组合编译了原始的 Postgres 9.4.1 源代码。我关注了这篇文章，但-flto由于make. 经过两天的测试，我在 T420 上从 195 秒下降到 189 秒，而 MacMini 是 40 秒（一套）；从 141 到 129 秒，而 MacMini 是 101 秒（B 组）。我使用了以下编译选项：
./configure CFLAGS="-O3 -fno-inline-functions -march=native" --with-openssl --with-libxml --with-libxslt --with-wal-blocksize=64 --with-blocksize=32 --with-wal-segsize=64 --with-segsize=1
我还尝试使用第 N 个逻辑 CPU禁用超线程，echo 0 > /sys/devices/system/cpu/cpuN/online但B 集查询没有任何改变。cpuN我们有 2 个 8 核 CPU，总共 16 个物理核和 16 个逻辑核。
这是之前的查询计划加上使用与 MacMini 相同配置的 T420 计划（但不同的 Postgres 版本）；这样计划是相同的，除了性能。
T420 与 MacMini postgresql.conf
查询 B_1 [51280.208ms + 0.699ms] http://explain.depesz.com/s/wlb
查询 B_2 [177278.205ms + 0.428ms] http://explain.depesz.com/s/rzr
具有最佳 postgresql.conf 的 T420
查询 B_1 [55999.649 毫秒 + 0.639 毫秒] http://explain.depesz.com/s/LbM
查询 B_2 [95664.832 毫秒 + 0.523 毫秒] http://explain.depesz.com/s/v06
苹果机
查询 B_1 [56315.614 毫秒] http://explain.depesz.com/s/uZTx
查询 B_2 [44890.813 毫秒] http://explain.depesz.com/s/y7Dk
以下是用于所有测试的 T420 和 MacMini 的 postgresql.conf。
T420postgresql.conf
正常操作
autovacuum = on
maintenance_work_mem = 512MB
work_mem = 512MB
wal_buffers = 64MB
effective_cache_size = 64GB # this helps A LOT in disk write speed when creating indexes
shared_buffers = 32GB
checkpoint_segments = 2000
checkpoint_completion_target = 1.0
effective_io_concurrency = 0 # 1 doesn’t make any substantial difference
max_connections = 10 # 20 doesn’t make any difference
数据加载（与上面相同，但有以下更改）：
autovacuum = off
maintenance_work_mem = 64GB
苹果机postgresql.conf
正常操作
autovacuum = on
maintenance_work_mem = 128MB
work_mem = 32MB
wal_buffers = 32MB
effective_cache_size = 800MB
shared_buffers = 512MB
checkpoint_segments = 32
checkpoint_completion_target = 1.0
effective_io_concurrency = 1
max_connections = 20
数据加载（与上面相同，但有以下更改）：
autovacuum = off
maintenance_work_mem = 6GB
在阅读9.0 版的官方 PostgreSQL 文档时，我读到了一个有趣的escamotage，它比CLUSTER大表表现得更好：
CLUSTER 命令通过使用您指定的索引扫描原始表来重新排序。这在大型表上可能会很慢，因为行是按索引顺序从表中获取的，如果表是无序的，则条目位于随机页面上，因此每移动一行都会检索一个磁盘页面。（PostgreSQL 有一个缓存，但大表的大部分内容不适合缓存。）集群表的另一种方法是使用：
CREATE TABLE newtable AS SELECT * FROM table ORDER BY columnlist;它使用 PostgreSQL 排序代码来生成所需的顺序；这通常比无序数据的索引扫描快得多。然后删除旧表，使用 ALTER TABLE ... RENAME 将新表重命名为旧名称，并重新创建表的索引。这种方法的最大缺点是它不保留 OID、约束、外键关系、授予的权限和表的其他辅助属性——所有这些项目都必须手动重新创建。另一个缺点是这种方式需要一个与表本身大小相同的排序临时文件，因此峰值磁盘使用量大约是表大小的三倍而不是表大小的两倍。
问题是这个建议没有出现在 > 9.0 版本的官方文档中。
我的问题是这个 escamotage 是否仍然对 9.1、9.2、9.3 和 9.4 有效，因为我被困CLUSTER在两个大表上的操作（一个有 ~750M 行，另一个有 ~1650M 行）和平均磁盘写入/读取由于CLUSTER官方文档中解释的算法，速度为 3MB/s 。对于大表来说，这是一个缓慢的过程，所以我想避免它执行“在索引关联列上创建有序表”的技巧。这将节省我几天的数据库处理时间。