1

PostgreSQL 9.5.10, RAM = 8GB

I have a table with three columns (ID, Category, anzahl (=Count)). the table has around 132million rows. There are 58 unique values in category column i.e.: 58 different category.

Similar to example demonstrated here PostgreSQL Crosstab Query i want to create a pivot where i have ID and 58 Categories as columns (so in all 59 columns) and row populated with respective Count values. below is the query: 

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

);

but it results in error as below:

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"
Daniel Vérité
  • 58,074
  • 15
  • 129
  • 156
Jio
  • 578
  • 3
  • 8
  • 27

1 Answers1

1

Try the canonical form instead:

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.

Presumably that form would be much easier (than crosstab) for the server to spill to disk if necessary as opposed to generating the entire result in memory.

You may also use a SQL cursor to retrieve the result in chunks. In some cases it can help a lot with the memory consumption, both client-side and server-side.

Client-side code to use a cursor:

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

There's also a dynamic_pivot function on github that can be used to automate the above (creates the pivot query and returns a cursor to it), but I'm not sure how its implementation would behave performance-wise with 132M rows.

Daniel Vérité
  • 58,074
  • 15
  • 129
  • 156