Delete Functionality Timing Out

Question

I'm attempting to run the delete functionality in Vertica, and it continues to time-out. It works in just a few minutes if I hard code a date (ex. DELETE WHERE T1.COL_1 < DATE('2022-03-01')) but it seems that the addition of any query of the table itself in order to produce the date value (ex. DELETE WHERE T1.COL_1 < T1.COL_2) times out at 10 minutes. I am unable to increase the query execution time. Can anyone explain why this is happening and what can be done?

Answer 1

It has to do with the way that Vertica, being a database designed for mass data access and manipulation, deals with deletes. A delete does not remove the row, but marks the row as deleted. To be efficiently marked as deleted, it needs the deletion mark, which is designed as a delete vector , pointing to a column that is in the sort order of, ideally, all projections of the table in question, or even better, the table's primary key.

So find a trick to delete with the primary key as the where condition.

One way could be, if your table is named todel and its primary key column is id:

DELETE FROM todel
WHERE id IN( 
  SELECT id FROM todel WHERE col_1 < col_2
);

I currently don't have a means to verify it, but it should do the trick. Should it not, then go:

CREATE LOCAL TEMPORARY TABLE dellist
ON COMMIT PRESERVE ROWS AS
SELECT id FROM todel WHERE col_1 < col_2
;
DELETE FROM todel WHERE id IN (SELECT id FROM dellist);
COMMIT;