PostgreSQL select query with where clause 'COLUMN_1 is null' hangs

Question

Recently we restored PostgreSQL database from the backup which was created without stopping database (I know this was very wrong and now we are paying the price). The backup was simple database directory backup. Now we noticed that when we execute

select *
from table
where COLUMN_1 is null

query in one of our tables the query just hangs (freezes) and never finishes. Other queries on the same table run fine and distinct(COLUMN_1) returns all the values. The same query runs correctly on the other column COLUMN_2 is null. It seems there is something wrong with that one column.

How can I repair such possibly damaged table?

Answer 1

Dump the whole database with pg_dump and restore it to a new cluster. If that works, it will get rid of all data corruption.

If that fails, you should hire a specialist.

If you attach to the hanging backend with a debugger, you can investigate what it is doing (if you are familiar with the source).