I complained about how:
> The query is
> SELECT p1.opcname, p1.opcfamily
> FROM pg_opclass AS p1
> WHERE NOT EXISTS(SELECT 1 FROM pg_amop AS p2
> WHERE p2.amopfamily = p1.opcfamily
> AND binary_coercible(p1.opcintype, p2.amoplefttype));
> and investigation showed that the plan changed from (8.2 and before)
> Seq Scan on pg_opclass p1 (cost=0.00..393.94 rows=51 width=68)
> Filter: (NOT (subplan))
> SubPlan
> -> Seq Scan on pg_amop p2 (cost=0.00..7.66 rows=2 width=0)
> Filter: ((amopfamily = $0) AND binary_coercible($1, amoplefttype))
> to
> Seq Scan on pg_opclass p1 (cost=0.00..393.94 rows=51 width=68)
> Filter: (NOT (subplan))
> SubPlan
> -> Seq Scan on pg_amop p2 (cost=0.00..7.66 rows=2 width=0)
> Filter: (binary_coercible($1, amoplefttype) AND (amopfamily = $0))
Now that some function-cost smarts are in there, I expected to see the
plan go back to the first case, but what I actually see in CVS HEAD is
Seq Scan on pg_opclass p1 (cost=0.00..660.35 rows=51 width=68)
Filter: (NOT (subplan))
SubPlan
-> Bitmap Heap Scan on pg_amop p2 (cost=4.29..8.60 rows=2 width=0)
Recheck Cond: (amopfamily = $0)
Filter: binary_coercible($1, amoplefttype)
-> Bitmap Index Scan on pg_amop_fam_strat_index (cost=0.00..4.29
rows=5 width=0)
Index Cond: (amopfamily = $0)
The reason this happens is that cost_qual_eval charges the entire cost of
evaluating all the arms of an AND, even though we'll drop out as soon as
something returns FALSE; and so the planner is led to avoid the seqscan
because it now appears to have a high filter-condition evaluation cost,
in favor of a plan that will evaluate the filter condition many fewer
times. In reality those two plans will call binary_coercible() exactly
the same number of times, and so this is a bogus reason to switch.
I'm kind of inclined to leave it alone though, because the second plan
seems a bit more "failsafe". To do anything differently, we'd have to
order the qual conditions the way we expect to execute them before
any use of cost_qual_eval, which sounds expensive; and as noted in
an upthread discussion with Greg, relying on the correctness of *both*
cost and selectivity estimates seems a tad fragile.
Comments?
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
