pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
runaway backend process
3+ messages / 3 participants
[nested] [flat]

* runaway backend process
@ 1999-09-10 08:28 Peter Andrews <pete@piedmontdata.com>
  1999-09-10 13:31 ` Re: [SQL] runaway backend process Bruce Momjian <maillist@candle.pha.pa.us>
  1999-09-10 14:45 ` Re: [SQL] runaway backend process Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 2 replies; 3+ messages in thread

From: Peter Andrews @ 1999-09-10 08:28 UTC (permalink / raw)
  To: pgsql-sql

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




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: [SQL] runaway backend process
  1999-09-10 08:28 runaway backend process Peter Andrews <pete@piedmontdata.com>
@ 1999-09-10 13:31 ` Bruce Momjian <maillist@candle.pha.pa.us>
  1 sibling, 0 replies; 3+ messages in thread

From: Bruce Momjian @ 1999-09-10 13:31 UTC (permalink / raw)
  To: Peter Andrews <pete@piedmontdata.com>; +Cc: pgsql-sql

> 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, Pennsylvania 19026



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: [SQL] runaway backend process
  1999-09-10 08:28 runaway backend process Peter Andrews <pete@piedmontdata.com>
@ 1999-09-10 14:45 ` Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 0 replies; 3+ messages in thread

From: Tom Lane @ 1999-09-10 14:45 UTC (permalink / raw)
  To: Peter Andrews <pete@piedmontdata.com>; +Cc: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~1999-09-10 14:45 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-09-10 08:28 runaway backend process Peter Andrews <pete@piedmontdata.com>
1999-09-10 13:31 ` Bruce Momjian <maillist@candle.pha.pa.us>
1999-09-10 14:45 ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox