Alexandros's questions

我有两张桌子，left2和right2。两个表都很大（1-10M 行）。

CREATE TABLE left2(id INTEGER, t1 INTEGER, d INTEGER);
ALTER TABLE left2 ADD PRIMARY KEY (id,t1);

CREATE TABLE right2( t1 INTEGER, d INTEGER, arr INTEGER[] );
ALTER TABLE right2 ADD PRIMARY KEY(t1,d);

我将执行这种类型的查询：

SELECT l.d + r.d,
       UNIQ(SORT((array_agg_mult(r.arr)))
FROM left2 l,
     right2 r
WHERE l.t1 = r.t1
GROUP BY l.d + r.d
ORDER BY l.d + r.d;

我在哪里聚合数组我使用函数：

CREATE AGGREGATE array_agg_mult(anyarray) (
SFUNC=array_cat,
STYPE=anyarray,
INITCOND='{}');

连接数组后，我使用模块的UNIQ功能intarray。有没有更有效的方法来做到这一点？字段上是否有任何索引arr来加快合并（删除重复项）？聚合函数可以直接删除重复项吗？如果有帮助，原始数组可能被认为是排序的（并且它们是唯一的）。

SQL Fiddle 在这里：

假设我有以下数据：

| f1 | f2 | f3 |
|----|----|----|
|  1 |  1 |  1 |
|  1 |  1 |  5 |
|  1 |  2 |  3 |
|  1 |  2 |  6 |
|  1 |  3 |  4 |
|  1 |  3 |  7 |
|  2 |  1 |  2 |
|  2 |  1 | 22 |
|  2 |  2 |  3 |
|  2 |  2 |  4 |

每个 f1、f2 组合有两个最大的 f3 值。

对于这个特定示例，我想获得每个 f1 和范围（f2 - 每个 f1 的 max(f2)）的前 2 个最小值。

示例输出：

| f1 | RNG| f3 |
|----|----|----|
|  1 |1 -3|  1 |
|  1 |1 -3|  3 |
|  1 |2 -3|  3 |
|  1 |2 -3|  4 |
|  1 |3 -3|  4 |
|  1 |3 -3|  7 |
|  2 |1- 2|  2 |
|  2 |1- 2|  3 |
|  2 |2- 2|  3 |
|  2 |2- 2|  4 |

不需要创建字段 RNG。我添加它只是为了表明 forf1=1有 3 个范围：1-3, 2-3, 3-3由 for 的不同值f2创建f1=1。对于每个这样的范围，我想计算每个 f1 和范围的前 k 个最小值。

SQL小提琴在这里：

http://sqlfiddle.com/#!15/9ddbb/1

构建范围可以通过以下方式完成：

SELECT DISTINCT s1.f1,s1.f2 AS range_from ,s2.f2 AS range_to
FROM dbTable s1,
(SELECT f1,MAX(f2) AS f2 FROM dbTable
GROUP BY f1) s2
WHERE s1.f1=s2.f1
ORDER BY s1.f1,s1.f2;

有没有办法在不构建中间数据库表的情况下实现这一目标？

我相信 PostgreSQL 可以记录缓慢或不成功的查询。可能我也可以设置 PostgreSQL 来记录所有执行的查询。相反，如果我尽可能地禁用了日志记录，我很想知道恶意攻击者是否可以访问在 PostgreSQL 服务器上成功执行的所有查询。这可能吗？或者一旦成功执行查询（可能是 SELECT 或 UPDATE 查询），我可以 100% 确定 DB 服务器没有成功执行查询的内存，因此没有其他人可以访问此信息。我正在使用 PostgreSQL 9.3。

继续我之前的问题PostgreSQL maximize performance SSD，DBA SE 社区为我提供了关于 SSD 上 PostgreSQL 最佳设置的有效建议。问题是我将定义两个单独的表空间，第一个已经在 SATA III Seagate Baracuda 上，另一个我将在 SSD 上设置（一旦 SSD 从商店到达）。在这种情况下，postgresql.conf 应该有两个单独的配置（一个用于 SSD，一个用于 HDD）。有什么方法可以使每个表空间或每个数据库的设置分开，而不必重新启动服务器和重新加载配置？我正在为科学研究进行基准测试，基本上我想在两个表空间上设置两个相同的数据库，并比较我的方法在 SSD 和 HDD 之间的查询读取性能。

假设我有以下包含 3 个整数字段的数据库表。

A | B | C
1 | 2 | 3
1 | 2 | 4
1 | 3 | 1
2 | 4 | 2
2 | 4 | 3

当我做：

SELECT * FROM dbTable ORDER BY A,B LIMIT 1;

我得到

1 | 2 | 3

这是预期的。但是第二条记录：

 1 | 2 | 4

dbFields A 和 B 也具有相同的值。是否有任何有效的方法来实际检索与 top-k 记录具有相同值的所有记录？例如，当我搜索前 100 条记录而不是 102 条记录时，如果后两条记录的值与第 100 条记录相同？是否有任何索引可以加速此类查询？如果实现有效，我不介意是否必须使用 pl/pgsql（而不是纯 SQL）来完成。

使用这个命令，我可以生成 16384 个介于 1 和 200,000 之间的随机整数。

SELECT generate_series (1,16384),(random()*200000)::int AS id

我想生成 10 组这样的整数。每个集合必须有一个整数标识符，如下所示：

1 | 135

1 | 1023

... 第一组 16384 个随机数结束

2 | 15672

2 | 258732

... 第二组 16384 个随机数结束

这可以通过 SQL 命令实现，还是我应该为此编写一个函数？

我将拥有一个庞大的 PostgreSQL 9.3 数据库，其中包含许多表，每个表的条目数超过 1 亿。该数据库基本上是只读的（一旦我填写了所有必要的表并构建索引，就不再对数据库进行写操作）和单用户访问（从本地主机运行和基准测试多个查询），因为将使用数据库仅用于研究目的。查询将始终在整数 DB 字段上使用 JOIN。

为此，我可能会购买 SSD (256-512GB)。我以前没有将 SSD 用于 DB，所以有什么我应该害怕的吗？我可以将整个数据库放在 SSD 上，还是只放在索引上？为 SSD 调整 PostgreSQL 是否需要任何特别的建议/教程？请注意，我有一个配备 i7 和 32Gb RAM 的良好工作站，所以也许您也可以在那里提供一些建议。

我有一个巨大的整数数组列表（300,000,000 条记录）存储在 Postgres 9.2 DB 中。我想有效地搜索这些记录以获得完全匹配（仅相等）。我听说过 intarray 模块和相应的 gist-gin 索引。我想问以下问题：

• PostgreSQL 是否使用哈希函数来检查整数数组的相等性，还是执行暴力算法逐一比较数组的元素？
• 如果 PostgreSQL 使用散列函数，是否有一些 PostgreSQL 函数代码可以实际获取特定数组的散列函数结果？
• 哪个索引最适合这样的任务？B-tree，还是 intarray 模块提供的 gist - gin 索引？数据集将是静态的，即，一旦插入所有记录，就不会再插入了。所以，建立索引/更新索引时间对我来说并不重要。

我有一个包含 3,000,000 行或更多行的数据库表：

|ID|T4|T16|T64|TL

其中 ID =INTEGER (PK) 和 T4,T16,T64,TL 是文本字段。每一个的长度都比前一个大，即LENGTH(T4)

我想根据比较行的 TL 之间的 levenshtein 距离（fuzzystrmatch 扩展）将此表的一行与 SELF JOIN 与所有其他行进行比较，并获得与较​​小的 levenstein 距离相对应的最佳 k 结果。

SELECT n1.*,n2.* 
FROM (SELECT * FROM dbtable WHERE ID =110) n1,
dbtable n2
ORDER BY levenshtein_less_equal(n1.TL,n2.TL,LENGTH(n1.TEXT)/4) LIMIT 100;

当然，这会非常慢，因为它必须进行 3,000,000 次 levenshtein 计算。

为了加速查询，我创建了人工“哈希”（字段 T4、T16、T64）。所以，我想首先检查字段 T4 之间的 levenshtein 距离。如果 T4 之间的距离在一定范围内，那么我会检查 T16 之间的距离，以此类推。这样，在每一步中，我都需要计算比天真的方法更少的 levenshtein 距离。

嵌套查询是这样的：

SELECT n5.*,levenshtein_less_equal(n5.n1_tl,n5.n2_tl,LENGTH(n5.n1_tl)/4)
FROM
(SELECT n4.*
FROM
(SELECT n3.*
FROM
(SELECT 
n1.id AS n1_id,
n1.t4 AS n1_t4,
n1.t16 AS n1_t16,
n1.t64 AS n1_t64,
n1.tl AS n1_tl,

n2.id AS n2_id,
n2.t4 AS n2_t4,
n2.t16 AS n2_t16,
n2.t64 AS n2_t64,
n2.tl AS n2_tl
FROM (SELECT id,t4,t16,t64,tl FROM dbTable WHERE id=110) AS n1,dbTable AS n2
WHERE
n1.id<>n2.id
AND levenshtein_less_equal(n1.t4,n2.t4,LENGTH(n1.t4)) <= LENGTH(n1.t4)/2
) n3

WHERE levenshtein_less_equal(n3.n1_t16,n3.n2_t16,LENGTH(n3.n1_t16)/2) < LENGTH(n3.n1_t16)/2 
) AS n4

WHERE levenshtein_less_equal(n4.n1_t64,n4.n2_t64,LENGTH(n4.n1_t64)/2) < LENGTH(n4.n1_t64)/2)
AS n5
ORDER BY levenshtein_less_equal(n5.n1_tl,n5.n2_tl,LENGTH(n5.n1_tl)/4)
LIMIT 100;

该查询比原来的天真查询要快得多，因为它只需要访问 112851 行 TL，但是：

解释计划：

Limit  (cost=872281.90..872282.15 rows=100 width=1168)
  ->  Sort  (cost=872281.90..872564.03 rows=112851 width=1168)
        Sort Key: (levenshtein_less_equal(dbTable.tl, n2.tl, (length(dbTable.tl) / 4)))
        ->  Nested Loop  (cost=0.00..867968.81 rows=112851 width=1168)
              Join Filter: ((dbTable.id <> n2.id) AND (levenshtein_less_equal(dbTable.t4, n2.t4, length(dbTable.t4)) <= (length(dbTable.t4) / 2)) AND (levenshtein_less_equal(dbTable.t16, n2.t16, (length(dbTable.t16) / 2)) < (length(dbTable.t16) / 2)) AND (levenshtein_less_equal(dbTable.t64, n2.t64, (length(dbTable.t64) / 2)) < (length(dbTable.t64) / 2)))
              ->  Index Scan using dbTable_pkey on dbTable  (cost=0.00..8.60 rows=1 width=584)
                    Index Cond: (id = 110)
              ->  Seq Scan on dbTable n2  (cost=0.00..699529.82 rows=3046982 width=584) 

如您所见，问题在于查询优化器连接/折叠 T4、T16、T64 之间的 levenshtein 距离计算，如果不折叠它可能会更快，因为需要为 T64 制作更少的 levenshtein 距离。我使用了 SET from_collapse_limit=1; 以避免在同一会话中崩溃但没有任何改变。有没有办法在 Postgres 查询中强制执行这种分层功能？请记住，没有一个 T4...TL 被索引，因为我认为没有索引可以加速 levenshtein 距离。有什么建议可以进一步提高性能？

我想针对主键 ID 的各种值对特定复杂查询 JOIN 的性能进行基准测试。大多数以前的方法都说对有问题的查询使用 EXPLAIN ANALYZE 并获取“总运行时间：.... ms”以计算性能。问题是，即使我使用相同的 ID，我也会得到非常不同的结果，例如时间范围从 20 秒到 140 秒不等。请注意，缓存与此无关，因为在不同的时间运行相同的查询时，我会得到这种变化，即使我们谈论的是相同的 ID（我总是得到相同的查询计划）。

所以我的问题是：

1. 总运行时间是否取决于客户端/接口（例如 pgAdminIII 与 psql）。我认为 psql 给出了更一致的值
2. 它是否取决于网络时间（我应该在本地主机上运行还是我什至可以在远程机器上运行）
3. 有没有办法进行更一致的测量，或者这种波动被认为是正常的？

我在具有 2GB 共享缓冲区的 Linux Ubuntu 12.10 64 位机器上使用 PostgreSQL 9.2.6 64 位。