Thread: OR-of-ANDs dragon slain ... or at least seriously wounded ...

I have just committed fixes that make use of an idea suggested by
Taral (see TODO.detail/cnfify, his message of 2-Oct-98).  The code
now makes a simple heuristic estimate of the size that the WHERE
clause will be after conversion to CNF or DNF format, and refrains
from attempting to canonicalize the clause if the cost looks too
high.

I was able to run this query plan:

explain
select unique1 from tenk1 where
(unique1 = 1 and unique2 =2) or
(unique1 = 2 and unique2 =3) or
(unique1 = 3 and unique2 =4) or
... ten thousand OR clauses ...
(unique1 = 9998 and unique2 =9999) or
(unique1 = 9999 and unique2 =10000) or
(unique1 = 10000 and unique2 =10001);

in about 15 seconds and 30MB memory consumption.  Which is still
more than I'd like, but the old code couldn't cope with twenty
clauses without exhausting one's swap space and/or patience.

I am probably not going to remove the KSQO hack just yet, because
it still seems to have a performance advantage for queries with
a few hundred or thousand OR clauses.  But KSQO is no longer
absolutely essential, AFAICT.

I'd be interested to get some feedback on this code from people
who are using KSQO currently.  The patch is in current CVS and
should be available in Friday morning's snapshot tarball.
        regards, tom lane

> I have just committed fixes that make use of an idea suggested by
> Taral (see TODO.detail/cnfify, his message of 2-Oct-98).  The code
> now makes a simple heuristic estimate of the size that the WHERE
> clause will be after conversion to CNF or DNF format, and refrains
> from attempting to canonicalize the clause if the cost looks too
> high.

Wow, this is great.  Seems that TODO mailbox archive came in handy here.

--  Bruce Momjian                        |  http://www.op.net/~candle pgman@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