Thread

Re: Question about partial index WHERE clause predicate ordering

Tom Lane <tgl@sss.pgh.pa.us> — 2025-12-27T04:15:11Z

Arik Schimmel <arik.schimmel@wiz.io> writes:
> Is there a reason partial index predicates aren't reordered by cost?

It hasn't come up AFAIR.  I'm dubious that it'd be worth the trouble,
because order_qual_clauses is really quite crude when dealing with
simple expressions.  We don't have accurate costing data for most
functions/operators --- they're all just labeled with procost 1 ---
so that the "cost-based ordering" reduces to just counting the
functions.  That gets the right answer in your example, but only
accidentally IMO; it has no idea that jsonb_extract_path_text()
is particularly expensive.  order_qual_clauses exists mostly to
ensure that subplans get pushed to the end, and that's not relevant
in this context because we don't support those in indexes.

			regards, tom lane