Thread: Long update query ?
Hello, I have query: UPDATE userd_session_stat SET status =1 WHERE status=0 AND ((uid <>627 AND tty <>'ttyA03') OR (uid <> 425 AND tty <> 'ttyA05') OR (uid <> 8011 AND tty <> 'ttyA09') OR (uid <> 2092 AND tty <> 'ttyA0f') OR (uid <> 249 AND tty <> 'ttyp3') OR (uid <> 249 AND tty <> 'ttyp4') OR (uid <> 249 AND tty <> 'ttyp5') OR (uid <> 249 AND tty <> 'ttyp6')) But, postgres complains that: FATAL 1: palloc failure: memory exhausted I see, the query must be less than 4kB, and this query is less. Long SELECT queries works fine. Have any idea? Maybe, I have to change postmaster's settings ? Query executes from libpg programm. Thanx, --------------------------- Sergei Chernev Internet: ser@nsu.ru Phone: +7-3832-397354
This is caused by a semi-well known weakness in the optimizer. The optimizer rewrites the WHERE clause in conjunctive normal form (CNF): (A and B) or (C and D) ==> (A or C) and (A or D) and (B or C) and (B or D) Try this with your statement and you will see the expression explodes. Foe now, I would suggest that you break this up into multiple statements. Sergei Chernev wrote: > Hello, > I have query: > UPDATE userd_session_stat SET status =1 WHERE status=0 AND ((uid <>627 AND > tty <>'ttyA03') OR (uid <> 425 AND tty <> 'ttyA05') OR (uid <> 8011 AND tty > <> 'ttyA09') OR (uid <> 2092 AND tty <> 'ttyA0f') OR (uid <> 249 AND tty <> > 'ttyp3') OR (uid <> 249 AND tty <> 'ttyp4') OR (uid <> 249 AND tty <> > 'ttyp5') OR (uid <> 249 AND tty <> 'ttyp6')) > > But, postgres complains that: > FATAL 1: palloc failure: memory exhausted > > I see, the query must be less than 4kB, and this query is less. > Long SELECT queries works fine. > Have any idea? Maybe, I have to change postmaster's settings ? Query > executes from libpg programm.
> This is caused by a semi-well known weakness in the optimizer. > The optimizer > rewrites the WHERE clause in conjunctive normal form (CNF): > > (A and B) or (C and D) ==> (A or C) and (A or D) and (B or C) > and (B or D) > Wouldn't disjunctive normal form be better, since it can be implemented as the simple union of a set of small queries? Taral
[Charset iso-8859-1 unsupported, filtering to ASCII...] > > This is caused by a semi-well known weakness in the optimizer. > > The optimizer > > rewrites the WHERE clause in conjunctive normal form (CNF): > > > > (A and B) or (C and D) ==> (A or C) and (A or D) and (B or C) > > and (B or D) > > > > Wouldn't disjunctive normal form be better, since it can be implemented as > the simple union of a set of small queries? Please tell us more. -- 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
> > Wouldn't disjunctive normal form be better, since it can be > implemented as > > the simple union of a set of small queries? > > Please tell us more. Well, I don't know how the backend processes queries, but one can imagine this scenario (for DNF): 1) Analyze query and set up columns in result table 2) Rewrite query into DNF 3) Split query into subqueries 4) For each subquery: a) Process query b) Append matching tuples to result table 5) Do any post-processing (ORDER BY, etc.) 6) Return result How is the processing currently done (with CNF)? Taral
[Charset iso-8859-1 unsupported, filtering to ASCII...] > > > Wouldn't disjunctive normal form be better, since it can be > > implemented as > > > the simple union of a set of small queries? > > > > Please tell us more. > > Well, I don't know how the backend processes queries, but one can imagine > this scenario (for DNF): > > 1) Analyze query and set up columns in result table > 2) Rewrite query into DNF > 3) Split query into subqueries > 4) For each subquery: > a) Process query > b) Append matching tuples to result table > 5) Do any post-processing (ORDER BY, etc.) > 6) Return result > > How is the processing currently done (with CNF)? It currently convert to CNF so it can select the most restrictive restriction and join, and use those first. However, the CNF conversion is a memory exploder for some queries, and we certainly need to have another method to split up those queries into UNIONS. I think we need to code to identify those queries capable of being converted to UNIONS, and do that before the query gets to the CNF section. That would be great, and David Hartwig has implemented a limited capability of doing this, but we really need a general routine to do this with 100% reliability. -- 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
> It currently convert to CNF so it can select the most restrictive > restriction and join, and use those first. However, the CNF conversion > is a memory exploder for some queries, and we certainly need to have > another method to split up those queries into UNIONS. I think we need > to code to identify those queries capable of being converted to UNIONS, > and do that before the query gets to the CNF section. That would be > great, and David Hartwig has implemented a limited capability of doing > this, but we really need a general routine to do this with 100% > reliability. Well, if you're talking about a routine to generate a heuristic for CNF vs. DNF, it is possible to precalculate the query sizes for CNF and DNF rewrites... For conversion to CNF: At every node: if nodeType = AND then f(node) = f(left) + f(right) if nodeType = OR then f(node) = f(left) * f(right) f(root) = a reasonably (but not wonderful) metric For DNF just switch AND and OR in the above. You may want to compute both metrics and compare... take the smaller one and use that path. How to deal with other operators depends on their implementation... Taral