PostgreSQL Using Stale Statistics Due to Non-Responsive Stats Collector

Size of the table is 53Gb without indexes. Obviously you have indexes, one of them a GIN index, which is typically multiple times the size of a plain table storing only the indexed column. I expect you get around 180 GB for

SELECT pg_total_relation_size(osmm.topographicarea);

Details on measuring sizes:

• Measure the size of a PostgreSQL table row

As has been commented, due to the MVCC model of Postgres, an UPDATE in place effectively writes a new row version of every changed row (and also for affected indexes), which roughly duplicates the size of the table in your operation.

If you don't have to worry about concurrent access, you could go a different route:

• Optimizing bulk update performance in PostgreSQL

60 GB (size of table, rounded up) will be too much for a temp table in memory. It would spill to disk, which voids the intended effect. You could just create a new regular table, drop the old one and rename the new - if you don't have concurrent access or depending objects.

To reclaim space you need

VACUUM FULL osmm.topographicarea;

While being at it, make that:

VACUUM FULL ANALYZE osmm.topographicarea;

Or run CLUSTER or use pg_repack or pg_squeeze if you cannot afford an exclusive lock on the big table.

Details:

• How to reclaim disk space after delete without rebuilding table?
• VACUUM returning disk space to operating system

Optimize UPDATE

Indexes are not helping for this UPDATE, on the contrary. Since you are updating every row and all involved columns are in the same row, indexes are of no use whatsoever here. They still have to be kept up to date at all times, though. It will be much cheaper to delete all indexes and add them back later - especially the comparatively expensive GIN index. And more ... Details:

• How to speed up a slow UPDATE query

Also, you are updating every row unconditionally. If some of the rows already have the right value in fcode, it would be cheaper not to touch those at all. Details (last chapter):

• How do I (or can I) SELECT DISTINCT on multiple columns?

But it sounds like you are adding a new column. In this case there is nothing to gain here.

Of course, all the usual advice for performance optimization applies as well.

Our final solution was to split the query so we did pivoting and denormalization in separate steps. This is probably a cleaner solution anyway since it follows the "just do one thing" principle so will probably be easier to maintain.

Thanks for all of the help!