postgresql - postgresql 中的交叉表函数导致无效的内存分配请求大小

Question

PostgreSQL 9.5.10，内存 = 8GB

我有一个包含三列(ID、类别、anzahl (=Count))的表格。该表有大约 1.32 亿行。类别列中有 58 个唯一值，即:58 个不同的类别。

类似于此处演示的示例 PostgreSQL Crosstab Query我想创建一个数据透视表，其中我有 ID 和 58 个类别作为列(所以在所有 59 列中)和行填充了各自的计数值。下面是查询:

select * into sde.demographie100m_transposed

from crosstab(
'select gitter_id_100m, category, anzahl
from sde.demographie100m_3col
order by 1,2',

'select distinct category from sde.demographie100m_3col order by 1'
) 


AS ct
("gitter_id_100m" text,
"INSGESAMT_Einheiten insgesamt" integer, 
"ALTER_10JG_10 - 19" integer, 
"ALTER_10JG_20 - 29" integer, 
"ALTER_10JG_30 - 39" integer, 
"ALTER_10JG_40 - 49" integer, 
"ALTER_10JG_50 - 59" integer, 
"ALTER_10JG_60 - 69" integer, 
"ALTER_10JG_70 - 79" integer, 
"ALTER_10JG_80 und älter" integer, 
"ALTER_10JG_Unter 10" integer, 
"ALTER_KURZ_18 - 29" integer, 
"ALTER_KURZ_30 - 49" integer, 
"ALTER_KURZ_50 - 64" integer, 
"ALTER_KURZ_65 und älter" integer, 
"ALTER_KURZ_Unter 18" integer, 
"FAMSTND_AUSF_Eingetr. Lebenspartner/-in verstorben" integer, 
"FAMSTND_AUSF_Eingetr. Lebenspartnerschaft" integer, 
"FAMSTND_AUSF_Eingetr. Lebenspartnerschaft aufgehoben" integer, 
"FAMSTND_AUSF_Geschieden" integer, 
"FAMSTND_AUSF_Ledig" integer, 
"FAMSTND_AUSF_Ohne Angabe" integer, 
"FAMSTND_AUSF_Verheiratet" integer, 
"FAMSTND_AUSF_Verwitwet" integer, 
"GEBURTLAND_GRP_Deutschland" integer, 
"GEBURTLAND_GRP_EU27-Land" integer, 
"GEBURTLAND_GRP_Sonstige" integer, 
"GEBURTLAND_GRP_Sonstige Welt" integer, 
"GEBURTLAND_GRP_Sonstiges Europa" integer, 
"GESCHLECHT_Männlich" integer, 
"GESCHLECHT_Weiblich" integer, 
"RELIGION_KURZ_Evangelische Kirche (öffentlich-rechtlich)" integer, 
"RELIGION_KURZ_Römisch-katholische Kirche (öffentlich-rechtlich)" integer, 
"RELIGION_KURZ_Sonstige, keine, ohne Angabe" integer, 
"STAATSANGE_GRP_Deutschland" integer, 
"STAATSANGE_GRP_EU27-Land" integer, 
"STAATSANGE_GRP_Sonstige" integer, 
"STAATSANGE_GRP_Sonstige Welt" integer, 
"STAATSANGE_GRP_Sonstiges Europa" integer, 
"STAATSANGE_HLND_Bosnien und Herzegowina" integer, 
"STAATSANGE_HLND_Deutschland" integer, 
"STAATSANGE_HLND_Griechenland" integer, 
"STAATSANGE_HLND_Italien" integer, 
"STAATSANGE_HLND_Kasachstan" integer, 
"STAATSANGE_HLND_Kroatien" integer, 
"STAATSANGE_HLND_Niederlande" integer, 
"STAATSANGE_HLND_Österreich" integer, 
"STAATSANGE_HLND_Polen" integer, 
"STAATSANGE_HLND_Rumänien" integer, 
"STAATSANGE_HLND_Russische Föderation" integer, 
"STAATSANGE_HLND_Sonstige" integer, 
"STAATSANGE_HLND_Türkei" integer, 
"STAATSANGE_HLND_Ukraine" integer, 
"STAATSANGE_KURZ_Ausland" integer, 
"STAATSANGE_KURZ_Deutschland" integer, 
"STAATZHL_Eine Staatsangehörigkeit" integer, 
"STAATZHL_Mehrere Staatsangehörigkeiten, deutsch und ausländisch" integer, 
"STAATZHL_Mehrere Staatsangehörigkeiten, nur ausländisch" integer, 
"STAATZHL_Nicht bekannt" integer

);

但它会导致如下错误:

ERROR: invalid memory alloc request size 1073741824
SQL Status:XX000
Kontext:SQL statement "select gitter_id_100m, category, anzahl
from sde.demographie100m_3col
order by 1,2"

Answer 1

尝试使用规范形式:

SELECT gitter_id_100m,
  SUM(CASE when category='INSGESAMT_Einheiten insgesamt' then anzahl END) AS "INSGESAMT_Einheiten insgesamt",
  SUM(CASE when category='ALTER_10JG_10 - 19' then anzahl END) AS "ALTER_10JG_10 - 19",
  ...etc...
 FROM sde.demographie100m_3col
 GROUP BY 1
 ORDER BY 1; -- remove the ORDER BY if you can do without it.

据推测，与在内存中生成整个结果相比，服务器在必要时将这种形式(比交叉表)更容易溢出到磁盘。

您也可以使用 SQL cursor以 block 的形式检索结果。在某些情况下，它可以极大地帮助减少客户端和服务器端的内存消耗。

使用游标的客户端代码:

BEGIN;  -- open transaction
  DECLARE mycursor CURSOR FOR SELECT ... rest of the query;
  FETCH mycursor;   -- retrieve 1 line
  -- FETCH mycursor repeatedly
  CLOSE mycursor;
COMMIT;

还有一个 dynamic_pivot function在 github 上，可用于自动执行上述操作(创建数据透视查询并向其返回一个游标)，但我不确定它的实现在 132M 行时的性能表现如何。