0

We have a table having three indexed columns say column1 of type bigint column2 of type timestamp without time zone column3 of type timestamp without time zone The table is having more than 12 crores of records and we are trying to delete all the records which are greater than current date - 45 days using below query 

delete from tableA
where column2 <= '2019-04-15 00:00:00.00' 
OR column3 <= '2019-04-15 00:00:00.00';

This is executing for ever and never completes.

Is there any way we can improve the performance of this query.

Drop indexes, delete data and recreate indexes. But this is not working as I am not able to delete data even after dropping the indexes.

 delete 
 from tableA 
 where column2 <= '2019-04-15 00:00:00.00' 
 OR column3 <= '2019-04-15 00:00:00.00'

I do not want to change the query but want the Postgres configured through some property so that it is able to delete the records

ScaisEdge
  • 131,976
  • 10
  • 91
  • 107
Anshul Sood
  • 43
  • 8

1 Answers1

0

See also for a good discussion of the issue Best way to delete millions of rows by ID

12 crores == 120 million rows?

Deleting from a large indexed table is slow because the index is rebuilt many times during the process. If you can select the rows you want to keep and use them to create a new table, then drop the old one, the process is much faster. If you do this regularly, use table partitioning and disconnect a partition when required, this can then be dropped.

1) Check the logs, you are probably suffering from deadlocks.

2) Try creating a new table selecting the data you need, then drop and rename. Use all the columns in your index in the query. DROP TABLE is much faster than DELETE .. FROM

    CREATE TABLE new_table AS (
        SELECT * FROM old_table WHERE 
        column1 >= 1 AND column2 >= current_date - 45 AND column3 >= current_date - 45);
    DROP TABLE old_table;
    ALTER TABLE new_table RENAME TO old_table;
    CREATE INDEX ...

3) Create a new table using partitions based on date, with a table for say 15, 30 or 45 days (if you regularly remove data that is 45 days old). See https://www.postgresql.org/docs/10/ddl-partitioning.html for details.

vinh
  • 201
  • 1
  • 6