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

On Mon, 2007-11-26 at 09:55 -0500, Tom Lane wrote:
> Simon Riggs <simon@2ndquadrant.com> writes:
> > 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.
>
> Perhaps some sanity could be restored to this discussion by pointing out
> that the 2007-01-01 code *also* clocks in at 37% spent in
> oper_select_candidate.  IOW it's been like this for a very long time.

[I'm replying to the wrong message, I know.]

Here's where I am:

Basic test was to replace call to oper_select_candidate with a single
item that was fed by a hardcoded value for varchar equality operator.
This is the oper_cache.v1.patch enclosed; the 5 line patch.

Test results were
- w/o patch    ~10,500 tps with pgbench_varchar.sql
- with patch    ~15,500 tps with pgbench_varchar.sql (**big gain**)
- w/o patch    ~16,250 tps with pgbench_integer.sql
- with patch    ~16,250 tps with pgbench_integer.sql

Tables are standard pgbench, varchar test table created using:
create table av as select aid::varchar, bid, abalance, filler from
accounts;
create unique index av_pkey on av (aid);

The impact of calling oper_select_candidate() is big enough that it will
affect any query that is read only and has 1 or 2 predicates when at
least one of them is a VARCHAR_col = const query.

What I'm actually proposing is a patch implementing a oper_select_hook
function pointer, which allows the user to do anything they want. I'm
just re-writing that as a plugin now, but the backend patch is included
here for discussion. oper_select_hook.v1.patch

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