I have a table where the natural identifier is a text like this:
AB-C123456E-F, which is my primary key.
The table is approaching 200 million rows, and so to get better performance on the unique constraint I've split it up into partitions by range:
CREATE TABLE documents ( doc_number text PRIMARY KEY, doc_data bytea, doc_data_sha256 bytea ) PARTITION BY RANGE (doc_number text_pattern_ops); CREATE TABLE documents_a_abc123z PARTITION OF documents FOR VALUES FROM (MINVALUE) TO ('AB-C123z'); CREATE TABLE documents_abc124_def456z PARTITION OF documents FOR VALUES FROM ('AB-C124') TO ('DE-F456z'); ...
These rows are queried by either equality or prefix match of the document number, and while partition pruning works as expected with an equality match, I can't get Postgres to do pruning for a prefix match. I've tried using
SELECT * FROM documents WHERE doc_number LIKE 'AB-C12345%' and
SELECT * FROM documents WHERE starts_with(doc_number, 'AB-C12345').
Is there a way I can get partition pruning over range partitions with a text prefix?