I wrote:
> Marc Cousin <cousinmarc@gmail.com> writes:
>> The example below is of course extremely simplified, and obviously not
>> what we are really doing in the database, but it exhibits the slowdown
>> between 9.1.9 and 9.2.4.
> Hm. Some experimentation shows that the plan cache is failing to switch
> to a generic plan, but I'm not sure why the cast would have anything to
> do with that ...
Hah, I see why:
(gdb) s
1009 if (plansource->generic_cost < avg_custom_cost * 1.1)
(gdb) p plansource->generic_cost
$18 = 0.012500000000000001
(gdb) p avg_custom_cost
$19 = 0.01
(gdb) p avg_custom_cost * 1.1
$20 = 0.011000000000000001
That is, the estimated cost of the custom plan is just the evaluation
time for a simple constant, while the estimated cost of the generic plan
includes a charge for evaluation of int4_numeric(). That makes the
latter more than ten percent more expensive, and since this logic isn't
considering anything else at all (particularly not the cost of
planning), it thinks that makes the custom plan worth picking.
We've been around on this before, but not yet thought of a reasonable
way to estimate planning cost, let alone compare it to estimated
execution costs. Up to now I hadn't thought that was a particularly
urgent issue, but this example shows it's worth worrying about.
One thing that was suggested in the previous discussion is to base the
planning cost estimate on the length of the rangetable. We could
do something trivial like add "10 * (length(plan->rangetable) + 1)"
in this comparison.
Another thing that would fix this particular issue, though perhaps not
related ones, is to start charging something nonzero for ModifyTable
nodes, say along the lines of one seq_page_cost per inserted/modified
row. That would knock the total estimated cost for an INSERT up enough
that the ten percent threshold wouldn't be exceeded.
Thoughts?
regards, tom lane