On Mon, 2007-11-26 at 09:55 -0500, Tom Lane wrote:
> Simon Riggs <[EMAIL PROTECTED]> 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
Index: src/backend/parser/parse_oper.c
===================================================================
RCS file: /home/sriggs/pg/REPOSITORY/pgsql/src/backend/parser/parse_oper.c,v
retrieving revision 1.98
diff -c -r1.98 parse_oper.c
*** src/backend/parser/parse_oper.c 22 Nov 2007 19:40:25 -0000 1.98
--- src/backend/parser/parse_oper.c 26 Nov 2007 16:28:42 -0000
***************
*** 28,33 ****
--- 28,34 ----
#include "utils/syscache.h"
#include "utils/typcache.h"
+ static Oid operOidCache = InvalidOid;
static Oid binary_oper_exact(List *opname, Oid arg1, Oid arg2);
static FuncDetailCode oper_select_candidate(int nargs,
***************
*** 516,534 ****
/* No operators found? Then fail... */
if (clist != NULL)
{
! /*
! * Unspecified type for one of the arguments? then use the other
! * (XXX this is probably dead code?)
! */
! Oid inputOids[2];
! if (rtypeId == InvalidOid)
! rtypeId = ltypeId;
! else if (ltypeId == InvalidOid)
! ltypeId = rtypeId;
! inputOids[0] = ltypeId;
! inputOids[1] = rtypeId;
! fdresult = oper_select_candidate(2, inputOids, clist, &operOid);
}
}
--- 517,543 ----
/* No operators found? Then fail... */
if (clist != NULL)
{
! if (OidIsValid(operOidCache))
! operOid = operOidCache;
! else
! {
! /*
! * Unspecified type for one of the arguments? then use the other
! * (XXX this is probably dead code?)
! */
! Oid inputOids[2];
!
! if (rtypeId == InvalidOid)
! rtypeId = ltypeId;
! else if (ltypeId == InvalidOid)
! ltypeId = rtypeId;
! inputOids[0] = ltypeId;
! inputOids[1] = rtypeId;
! fdresult = oper_select_candidate(2, inputOids, clist, &operOid);
! if (ltypeId == 1043 && rtypeId == 705)
! operOidCache = operOid;
! }
}
}
\set naccounts 100000 * :scale
\setrandom aid 1 :naccounts
SELECT abalance FROM accounts WHERE aid = :aid;
\set naccounts 100000 * :scale
\setrandom aid 1 :naccounts
SELECT abalance FROM av WHERE aid = ':aid';
Index: src/backend/parser/parse_oper.c
===================================================================
RCS file: /home/sriggs/pg/REPOSITORY/pgsql/src/backend/parser/parse_oper.c,v
retrieving revision 1.98
diff -c -r1.98 parse_oper.c
*** src/backend/parser/parse_oper.c 22 Nov 2007 19:40:25 -0000 1.98
--- src/backend/parser/parse_oper.c 26 Nov 2007 23:08:09 -0000
***************
*** 28,33 ****
--- 28,34 ----
#include "utils/syscache.h"
#include "utils/typcache.h"
+ oper_select_hook_type oper_select_hook = NULL;
static Oid binary_oper_exact(List *opname, Oid arg1, Oid arg2);
static FuncDetailCode oper_select_candidate(int nargs,
***************
*** 528,534 ****
ltypeId = rtypeId;
inputOids[0] = ltypeId;
inputOids[1] = rtypeId;
! fdresult = oper_select_candidate(2, inputOids, clist, &operOid);
}
}
--- 529,544 ----
ltypeId = rtypeId;
inputOids[0] = ltypeId;
inputOids[1] = rtypeId;
!
! /*
! * Allow user defined operator selection, allowing hard-coding
! * cacheing or other mechanisms of operator selection for
! * improved performance in certain circumstances
! */
! if (oper_select_hook)
! fdresult = (* oper_select_hook) (2, inputOids, clist, &operOid);
! else
! fdresult = oper_select_candidate(2, inputOids, clist, &operOid);
}
}
Index: src/include/parser/parse_oper.h
===================================================================
RCS file: /home/sriggs/pg/REPOSITORY/pgsql/src/include/parser/parse_oper.h,v
retrieving revision 1.41
diff -c -r1.41 parse_oper.h
*** src/include/parser/parse_oper.h 5 Jan 2007 22:19:57 -0000 1.41
--- src/include/parser/parse_oper.h 26 Nov 2007 23:03:57 -0000
***************
*** 16,21 ****
--- 16,22 ----
#include "access/htup.h"
#include "parser/parse_node.h"
+ #include "parser/parse_func.h"
typedef HeapTuple Operator;
***************
*** 37,42 ****
--- 38,49 ----
extern Operator left_oper(ParseState *pstate, List *op, Oid arg,
bool noError, int location);
+ typedef FuncDetailCode (*oper_select_hook_type) (int nargs,
+ Oid *input_typeids,
+ FuncCandidateList candidates,
+ Oid *operOid);
+ extern PGDLLIMPORT oper_select_hook_type oper_select_hook;
+
/* Routines to find operators that DO NOT require coercion --- ie, their */
/* input types are either exactly as given, or binary-compatible */
extern Operator compatible_oper(ParseState *pstate, List *op,
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
