Re: Inefficient filter order in query plan

On Thu, Feb 27, 2014 at 3:47 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> A bit of consultation of pg_proc.procost will show you that just about
> the only internal functions with costs different from 1X cpu_operator_cost
> are those that do some sort of database access (and, in consequence, have
> true costs a couple orders of magnitude higher than a simple comparison).
> We may eventually get around to refining the cost model so that it can
> tell the difference between = and LIKE, but nobody's yet done the work
> to decide which functions ought to get assigned what costs.  I'm
> disinclined to single out LIKE for special treatment in the absence of
> some sort of framework for deciding which functions are worth penalizing.
>
>                         regards, tom lane

I agree that LIKE should not be singled out for special treatment
since the cost values should be determined with respect to all other
operators and not just one.

My original question still remained though.  The clause order of my
predicate is being re-arranged due to factors unrelated to cost:

Predicate: model = 'User' AND model_id = 304 AND change_set ILIKE '%test%'
-->
Plan filter: (change_set ~~* '%test%'::text) AND ((model)::text =
'User'::text) AND (model_id = 304)

For anyone looking for an answer to a similar question in the future,
the following is summary of why this appears to be happening.

Using PostgreSQL 9.1.12 source:

/src/backend/optimizer/plan/planmain.c:193
    -  A call to deconstruct_jointree(PlannerInfo *root) is made.  The
jointree inside of root still has the clause order preserved.
/src/backend/optimizer/plan/initsplan.c:259
    -  A call to deconstruct_recurse(PlannerInfo *root, Node *jtnode,
....) is made.
/src/backend/optimizer/plan/initsplan.c:353
    -  The quals are looped over and a call to
distribute_qual_to_rels(PlannerInfo *root, Node *clause, ....) is made
for each.
/src/backend/optimizer/plan/initsplan.c:1099
    -  LIKE and equivalence clauses are handled differently.
        -  Equivalence clauses are encountered first in my scenario
and get processed by process_equivalence() on line 1104. return is
then immediately called.
            -  A comment explains that equivalence clauses will be
added to the restriction list at a later time:
                "If it is a true equivalence clause, send it to the
EquivalenceClass
                machinery.  We do *not* attach it directly to any
restriction or join
                lists.  The EC code will propagate it to the
appropriate places later."
        -  The LIKE clause is last and gets processed by
distribute_restrictinfo_to_rels on line 1152.
            -  Unlike equivalences, this results in the LIKE clause
getting added to the restriction list immediately and therefore is
first in the list.
/src/backend/optimizer/plan/planmain.c:207
    -  Equivalence clauses finally get added to the restriction list
via a call to generate_base_implied_equalities(PlannerInfo *root)

I don't understand the reason for delaying the addition of equivalence
clauses to the restriction list but, for whatever reason, it appears
to be by design.