Re: 8.3devel slower than 8.2 under read-only load

On Sun, 2007-11-25 at 19:35 -0500, Tom Lane wrote:

> The cost of resolving ambiguous operators has been an issue for a long
> time, of course, but it seems particularly bad in this case --- gprof
> blames 37% of the runtime on oper_select_candidate().  It might be time
> to think about caching the results of operator searches somehow.  Too
> late for 8.3 though.

Wow: 37%. 

"varchar_column = const" is a very, very common predicate. 37% is enough
to still be visible for a wide range of queries, not just the very
simple. I think queries with WHERE clauses like ("int4_column = int4_const" AND "varchar_column = const")
will also be noticeably affected this. So even when we have integer
keys, we will still get slowed down by an checks to an additional status
column.

Caching is the right way around this, though as you point out, that is
not an option for 8.3.

But I think there must be an action that we can take for 8.3 and that
much runtime should not be given away easily. ISTM that we can win back
the losses Guillaume has identified, plus gain a little more even.

Can we just hard-code the varchar lookup? Ugly, but it will add almost
nothing to non-varchar paths and yet speed-up the varchar lookup
dramatically. I guess the objection to that will be that it prevents
people from overloading the = operator for varchars to change the
selectivity functions etc.

So how about we have a cache-of-one: we store the best varchar =
operator after the first lookup, then document that if people overload
this then they must reconnect. That's an acceptable pain for the few
people affected and a great benefit for the most people.

--  Simon Riggs 2ndQuadrant  http://www.2ndQuadrant.com