Postgresql – Postgres 9.3 – deletes terribly slow for 10 minutes after dropping indexes/constraints

constraintdeleteindexperformancepostgresql-9.3postgresql-performance

If I run a script which drops indexes (17 of them, CONCURRENTLY makes no difference except that without it I get the occasional deadlock), then constraints (20 constraints on 18 tables) and then run a function which deletes 100,000 records from a main table and all of its associated data from ancillary tables, the initial delete takes about an hour.

If I wait 10 minutes before running the function, it runs in about 16 – 20 seconds. I run this function 120 times in separate transactions in this script, then rebuild my constraints and indexes at the end (postgres was hanging in the middle if I try to keep them all in the same transaction and even at 500000 per transaction sometimes resulted in a performance hit).

Is there anything which postgres does following index/constraint changes that could account for the need to wait on running my deletes?

Best Answer

First, sorry if I have a bad English.

Even if you dropped the constraints from the table you are trying to delete, the constraints of any children tables will affect the performance of the parent table. This occurs because the database needs to do an select on all the foreing keys pointed to the main table each line of delete to guarantee the integrity of the deletion that you're trying to do.

Create indexes on the foreign keys of the child columns that are pointed to the main table you are trying to delete, this will solve the problem and improve performance for inner join also. Remember PostgreSQL will not create indexes for the foreign keys automatically.

Related Solutions

Sql-server – Creating indexes with t-sql scrips vs rebuild indexes in maintenance plan

Removing the impact of updating your non-clustered indexes during the inserts is a great way to improve bulk insert performance and something we do in my own environments. However, I think a better alternative to you than dropping the table and/or indexes, then recreating them post-insert is to instead disable then rebuild the index. This process removes some over head of the index creation, but accomplishes the same goal of getting the indexes out of the way.

The command to do this would be:

Disable: ALTER INDEX foo ON bar DISABLE
Re-enable/rebuild: ALTER INDEX foo ON bar REBUILD

Depending on how you execute your ETL loads, you can build this as two steps and script out the index disable and rebuild (SQL 2008 syntax):

--Disable Indexes
DECLARE @v_sql nvarchar(max);
SELECT @v_sql = '';
SELECT @v_sql += 'ALTER INDEX '+QUOTE_NAME(name,'[')+' DISABLE;'+char(10)
FROM sys.indexes
WHERE object_id=OBJECT_ID('bar') AND type =2;

EXEC sp_executesql @v_sql;

--Rebuild Indexes
DECLARE @v_sql nvarchar(max);
SELECT @v_sql = '';
SELECT @v_sql += 'ALTER INDEX '+QUOTE_NAME(name,'[')+' REBUILD WITH(ONLINE=ON);'+char(10)
FROM sys.indexes
 WHERE object_id=OBJECT_ID('bar') AND type =2;

EXEC sp_executesql @v_sql;

Note, by declaring type=2, we are only disabling/rebuilding non-clustered indexes. You don't want to disable/rebuild your clustered indexes as part of this process.

Deleting data from some but not all tables

Dropping and then recreating all of the foreign keys shouldn't be a problem because you should have them all in your source control system. If that's not the case then you have much bigger problems.

You can generate DROP/CREATE scripts through SSMS. If you right-click on your database then you can go to Tasks->Generate Scripts... and select the tables that you need through the wizard. You'll have to do a lot of cut and paste to get just the FKs. Alternatively, you can dig down to the FKs in the object tree, right-click each one, and generate a script from that menu.

In either case, it sounds like your deploy process for your production system is in some bad shape.

Best Answer

Related Solutions

Sql-server – Creating indexes with t-sql scrips vs rebuild indexes in maintenance plan

Deleting data from some but not all tables

Related Question