連接多個子查詢

我需要一個返回這種格式的查詢：

pk  id  v1 v2 v3 v4 v5
----------------------
...
pk1 id1 A1 A2 A3 A4 A5
pk6 id2 B1 B2 B3 B4 B5
...

我目前的數據看起來像這樣

CREATE TABLE foo(pk,id,value)
AS VALUES
   ( 'pk1' , 'id1',  'A1' ),
   ( 'pk2' , 'id1',  'A2' ),
   ( 'pk3' , 'id1',  'A3' ),
   ( 'pk4' , 'id1',  'A4' ),
   ( 'pk5' , 'id1',  'A5' ),
   ( 'pk6' , 'id2',  'B1' ),
   ( 'pk7' , 'id2',  'B2' ),
   ( 'pk8' , 'id2',  'B3' ),
   ( 'pk9' , 'id2',  'B4' ),
   ( 'pk10', 'id2',  'B5' )
;

源表有 ~160,000,000 行。所有列都被索引（btree）。

目前我正在做 5 個（實際數字是動態的，可能在 30 個左右，但例如上面是 5 個）要提取的不同查詢：

1:

P1 id1 A1
P6 id2 B1

2:

P2 id1 A2
P7 id2 B2

3:

P3 id1 A3
P8 id2 B4

ETC

然後加入這些查詢。像這樣的東西：

SELECT q1.pk,q1.id,q1.v1,q2.v2,q3.v3,q4.v4,q5.v5 FROM (SELECT pk, id, value FROM table WHERE id=1) AS q1, (SELECT pk, id, value FROM table WHERE id=2) AS q2, (SELECT pk, id, value FROM table WHERE id=3) AS q3, (SELECT pk, id, value FROM table WHERE id=4) AS q4, (SELECT pk, id, value FROM table WHERE id=5) AS q5 WHERE q1.id=q2.id and q2.id=q3.id and q3.id=q4.id and q4.id=q5.id;

但正如我上面提到的，連接數量要多得多（~30），而且在 Postgres 上執行速度非常慢。我檢查了計劃並禁用nestloop了它，它變得更快了，但它仍然太慢了。如果我將所有這些查詢提取到記憶體並以程式方式加入它們（例如使用 python），它會在大約 1 秒內執行。但是 Postgres 耗時太長（加入 110 行約 30 秒）。有任何想法嗎？我認為必須有比製作 30 個子查詢並加入它們更好的方法。另一種方法是根據id列對行進行分組並做一些魔術（例如使用RowNumber()）來生成所需的表。

PS 不適用於預生成結果表。一切都必須在執行時發生。

對於您的範例，您想要的只是

SELECT
 min(pk),
 id,
 array_agg(value ORDER BY value)
FROM foo
GROUP BY id;

min  | id  |    array_agg     
------+-----+------------------
pk1  | id1 | {A1,A2,A3,A4,A5}
pk10 | id2 | {B1,B2,B3,B4,B5}
(2 rows)

如果您需要array_agg打開包裝，您可以這樣做，

SELECT pk, id, a[1] AS v1, a[2] AS v2, a[3] AS v3, a[4] AS v4, a[5] AS v5
FROM (
 SELECT
   min(pk),
   id,
   array_agg(value ORDER BY value)
 FROM foo
 GROUP BY id
) AS t(pk, id, a);

 pk  | id  | v1 | v2 | v3 | v4 | v5 
------+-----+----+----+----+----+----
pk1  | id1 | A1 | A2 | A3 | A4 | A5
pk10 | id2 | B1 | B2 | B3 | B4 | B5

請注意，在以後的所有範例中，請不要使用id1and pk1。您的樣本值最好是1和2。正如您在此處看到的，我們正在努力獲取pk10而不是，pk6這只是因為排序順序的問題。

引用自：https://dba.stackexchange.com/questions/202197