Re: Huge query stalls at PARSE/BIND stage (1)

Matt <msubs(at)philips(dot)org(dot)uk> 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);