I'm facing a lot of delay trying to make some basic commands at postgresql, such as create an index or delete an unique row from a table:

The table that I was trying to delete a row is "A" that is referenced from other tables "B", "C" and "D".

Queries with SELECT at table "A" are working normally, it returns the data very quickly, it has around 100k rows. But when I try to DELETE even an unique row, I takes more than an hour and then my session expires.

I noticed that table "D" doesn't have a Index to refer to table "A", and when I try to create a index to it, I face again with an hour of delay and then my session expires.

CodePudding user response：

1. Did you enable "ON DELETE CASCADE" to your table? this can make your system very slow. In that case, create indexes for the foreign keys
2. Some time this can happen due to corrupted Indexes. (one time i faced the similar kind of issue due to the corrupted Indexes. It's a very stagnate situation: That happened because VM image backup and DB backup ran at the same time and that process created me the corrupted indexes) try to re create those.