apache / apache/paimon

[Feature] Point-delete fast path for primary key DELETE to skip the target table scan

Open
#8,836 0 comments 0 reactions 0 assignees View on GitHub
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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.