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

Matt <msubs@philips.org.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);