| From: | Arik Schimmel <arik(dot)schimmel(at)wiz(dot)io> |
|---|---|
| To: | pgsql-hackers(at)lists(dot)postgresql(dot)org |
| Subject: | Question about partial index WHERE clause predicate ordering |
| Date: | 2025-12-24 12:48:22 |
| Message-ID: | CABmTnzfvhrahbFHCYFLXqK7uiHjB8hDn2+Y5TFr_U0ZYS6LzFw@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
Hi all,
I noticed that when creating a partial index with multiple predicates in
the WHERE clause, the order in which I write the predicates appears to
affect the index build time (specifically the index validation phase).
I created an index with this WHERE clause:
CREATE INDEX CONCURRENTLY idx_v1
ON my_table (tenant_id, jsonb_extract_path_text(data, 'field1'),
object_type, id)
WHERE deleted IS NULL
AND jsonb_extract_path_text(data, 'field1') <> ''
AND object_type = 'SpecificType';
Then I tried reordering the predicates to put expensive operations last:
CREATE INDEX CONCURRENTLY idx_v2
ON my_table (tenant_id, jsonb_extract_path_text(data, 'field1'),
object_type, id)
WHERE deleted IS NULL
AND object_type = 'SpecificType'
AND jsonb_extract_path_text(data, 'field1') <> '';
The second version (idx_v2) was significantly faster to build
Looking at the PostgreSQL source, it appears that:
- Regular query WHERE clauses go through the planner's
order_qual_clauses()
function (in createplan.c), which sorts predicates by cost
- Partial index predicates appear to go through ExecPrepareQual() (in
execExpr.c),
which processes predicates in the given order without reordering
This seems to be a difference between how the planner handles query
predicates versus how the executor handles index predicates.
Is this expected/intended behavior?
Is there a reason partial index predicates aren't reordered by cost?
I'm using Postgres 16.9
Thanks for any insights!
Arik Schimmel
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Jelte Fennema-Nio | 2025-12-24 13:28:19 | cleanup: Split long Makefile lists across lines and sort them |
| Previous Message | vignesh C | 2025-12-24 11:59:15 | Re: Proposal: Conflict log history table for Logical Replication |