[Feature] Point-delete fast path for primary key DELETE to skip the target table scan
- Dominant language
- Java
- Stars
- 3.4k
- Forks
- 1.4k
- Avg merge
- 1d 11h
- Merged PRs (30d)
- 396
Description
### Search before asking
- [x] I searched in the [issues](https://github.com/apache/paimon/issues) and found nothing similar.
### Motivation
Currently `DELETE FROM` on a primary-key table always scans the target table to find matching rows, even when the condition is just primary keys, e.g.:
```sql
DELETE FROM t WHERE id IN (1, 2, 3);
DELETE FROM t WHERE id IN (SELECT id FROM keys_to_delete);
```
For these cases the matched keys are already fully described by the condition itself, so we can build the `-D` records directly and skip the target table scan entirely. On large tables (we have a case deleting ~10 million keys from a multi-billion-row table by a key table) this is orders of magnitude cheaper than the current scan / join based path.
### Solution
Add a fast path in `DeleteFromPaimonTableCommand` for the pk-upsert delete:
- If the condition is a conjunction of `pk = literal` / `pk IN (literals)` covering all primary key columns: extract keys from the condition and write `-D` rows directly (with a config to cap the driver-side cartesian expansion, falling back to scan when exceeded).
- If the condition is `pk IN (subquery)` covering all primary key columns: use the subquery result as the key DataFrame, fully distributed.
- Any other condition falls back to the existing scan-based path, so behavior stays unchanged.
Keys absent from the table are harmless since the `-D` records simply merge away in compaction.
I have a working implementation with tests, will submit a PR.
### Anything else?
_No response_
### Are you willing to submit a PR?
- [x] I'm willing to submit a PR!
Contributor guide
No contributing guide indexed for this repository
Research direction
Start with DeleteFromPaimonTableCommand and trace the existing primary-key upsert DELETE path. Compare the literal/IN and subquery cases described in the issue, including the fallback behavior and driver-side expansion cap. Done means qualifying deletes avoid the target-table scan, other conditions retain existing behavior, and the implementation's tests cover these cases.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- java
- Domain
- data-engineering, databases
- Issue type
- Feature
- Difficulty
- 5/5
- Estimated time
- Over a week
- Activity status
- Quiet
- Clarity
- Mostly clear
- Newbie friendliness
- 35/100