Presto DELETE Statement Resource Consumption
DELETE queries issue jobs and consume resources.Because of the storage and indexing methods used in Treasure Data, a DELETE query can be resource intensive.
To prevent a DELETE query from performing a full table scan, you should use:
- a time expression, like TD_TIME_RANGE or TD_INTERVAL, if the table uses default time-based partitioning
- an equality predicate on all partition keys, if the table uses user-defined partitioning
For example, if your table is partitioned by time, and you are trying to delete a set of users that were created in June 2018, include the time restriction even if that’s not a critical parameter for your DELETE statement.
For example, you should:
DELETEFROM logtable WHERE userid IN (1234, 1235, 1236) AND TD_TIME_RANGE(time, '2017-06-01','2017-07-01')
Including the time restriction significantly improves the speed of your query, and reduces the resources this query consumes.