Re: [SQL] runaway backend process

Peter Andrews <pete(at)piedmontdata(dot)com> 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