Matt <[EMAIL PROTECTED]> writes:
>> If you'd be willing to send me a test case off-list, I'd be willing to
>> take a look. A convenient test case from my point of view would be a
>> SQL script that sets up the database plus another one containing the
>> slow query.
> I've created a standalone test case, sending it privately.
Thanks for the test case. The problem turns out to be sloppy coding in
exprTypmod(): it recurses twice on the first argument of a COALESCE,
making for exponential growth in the time needed to process a deep nest
of COALESCE expressions ... which is exactly what your deeply nested
FULL JOINs produce for the join USING variables. The patch attached
fixes it for 8.0.* --- I've committed equivalent fixes as far back as
7.4, where the problem originated.
Moral: sometimes an apparently trivial inefficiency isn't so trivial.
regards, tom lane
Index: src/backend/parser/parse_expr.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/parser/parse_expr.c,v
retrieving revision 1.179.4.2
diff -c -r1.179.4.2 parse_expr.c
*** src/backend/parser/parse_expr.c 25 May 2005 02:17:55 -0000
1.179.4.2
--- src/backend/parser/parse_expr.c 18 Nov 2005 23:05:21 -0000
***************
*** 1562,1569 ****
int32 typmod;
ListCell *arg;
typmod = exprTypmod((Node *)
linitial(cexpr->args));
! foreach(arg, cexpr->args)
{
Node *e = (Node *) lfirst(arg);
--- 1562,1573 ----
int32 typmod;
ListCell *arg;
+ if (exprType((Node *) linitial(cexpr->args)) !=
coalescetype)
+ return -1;
typmod = exprTypmod((Node *)
linitial(cexpr->args));
! if (typmod < 0)
! return -1; /* no point in trying
harder */
! for_each_cell(arg,
lnext(list_head(cexpr->args)))
{
Node *e = (Node *) lfirst(arg);
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
