samedi 29 novembre 2014

PostgreSQL, UPDATE CASE statement uses huge amount of space


I am running a CASE statement on one of my tables and it consumes a huge amount of hard drive space that is never freed up even when i run VACUUM ANALYZE. Autovacuum is ON as well.


I am using PostgreSQL 9.3 64Bit on a windows 2012 server. I have tweaked the postgresql.conf using advice on the wiki. So it is as optimised as I can make it.


The table is large (>150million rows) which I need to add an extra column and populate it based on the contents of 3 other columns. The size of this table is 53Gb without indexes.


Having tested lots of approaches I am using a CASE statement. Two of the columns are arrays so I have used GIN indexes and a standard index.


A sample of my CASE statement looks like this, the rules go up to 38



UPDATE osmm.topographicarea
SET fcode = (CASE
--building CASE statements
WHEN (descriptivegroup @> '{Building}' and descriptiveterm @> '{Archway}') then 1
WHEN (descriptivegroup @> '{Building}') then 2

WHEN (descriptiveterm @> '{Step}') then 3
WHEN (descriptivegroup @> '{Glasshouse}') then 4
WHEN (descriptivegroup @> '{Historic Interest}') then 5
WHEN (descriptivegroup @> '{Inland Water}') then 6
ELSE 99
END);
COMMIT;


The process takes over 5hrs but adds a huge 180Gb to the table!!


Adding an integer column to this table surely should not do this?


How can I fix this please?





Aucun commentaire:

Enregistrer un commentaire