I wrote: > Marc Cousin <cousinm...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers