Обсуждение: runaway backend process

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?

Redhat 5.2
PostgreSQL 6.5.1
196MB RAM
database has been vacuum analyzed
table contains ~ 10000 rows

Peter Andrews

> 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');

We have problems processing a large number of OR's. 6.6 will be better
in this area.

--  Bruce Momjian                        |  http://www.op.net/~candle maillist@candle.pha.pa.us            |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 

Peter Andrews <pete@piedmontdata.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