Received: from sss.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2]) by hub.org (8.9.3/8.9.3) with ESMTP id KAA07467 for ; Fri, 10 Sep 1999 10:45:50 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.sss.pgh.pa.us (8.9.1/8.9.1) with ESMTP id KAA10213; Fri, 10 Sep 1999 10:45:15 -0400 (EDT) To: Peter Andrews cc: pgsql-sql@postgreSQL.org Subject: Re: [SQL] runaway backend process In-reply-to: Your message of Fri, 10 Sep 1999 04:28:43 -0400 <37D8C13A.53DC5B8B@piedmontdata.com> Date: Fri, 10 Sep 1999 10:45:14 -0400 Message-ID: <10210.936974714@sss.pgh.pa.us> From: Tom Lane Peter Andrews writes: > Any help with this problem would be greatly appreciated. After > executing the following query, the postgres backend server process that > is executing the query grows in size until it consumes well over 200 MB > and takes several minutes to finish: > select mlno from table where > (area='01' and sub_area='01') or > (area='01' and sub_area='02') or > (area='01' and sub_area='03') or > (area='01' and sub_area='04') or > (area='07' and sub_area='01') or > (area='07' and sub_area='02') or > (area='07' and sub_area='03') or > (area='08' and sub_area='01') or > (area='08' and sub_area='03') or > (area='09' and sub_area='01') or > (area='09' and sub_area='02') or > (area='04') or > (area='05') or > (area='06') or > (area='99'); > A similar query with fewer terms does not cause an abnormal increase in > backend process size, and executes in under a second. Does anyone have > any suggestions as to what might be causing this behavior? Do you see the same sort of behavior if you just "explain" the query without actually executing it? If so, it's a planner problem. I recently identified a flaw in cnfify() that causes it to use an amount of space exponential in the number of AND/OR terms. There is a fix in the current CVS sources, but I'm hesitant to commit it into REL6_5 since it's not well tested yet. If you want, you can try dropping src/backend/optimizer/prep/prepqual.c from a current snapshot into the 6.5 code (probably be easiest to work from the latest 6.5.2 beta, rather than 6.5.1). regards, tom lane