Skip site navigation (1) Skip section navigation (2)

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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Matt <msubs(at)philips(dot)org(dot)uk>
Cc: pgsql-bugs(at)postgreSQL(dot)org
Subject: Re: Huge query stalls at PARSE/BIND stage (1)
Date: 2005-11-18 23:15:23
Message-ID: 12909.1132355723@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugs
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);
  

In response to

Responses

pgsql-bugs by date

Next:From: Alexey BeschiokovDate: 2005-11-19 14:28:31
Subject: postgresql8.1 crushes on some inserts/updates in KOI8 encoding
Previous:From: Jim C. NasbyDate: 2005-11-18 17:55:33
Subject: Re: Double sequence increase on single insert with RULE on

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group