Re: [HACKERS] RE: [GENERAL] Long update query ? (also Re: [GENERAL] CNF vs. DNF)

> >
> > > > Create a temporary oid hash? (for each table selected on, I guess)
> > >
> > > What I did with indexes was to run the previous OR clause index
> > > restrictions through the qualification code, and make sure it failed,
> > > but I am not sure how that is going to work with a more complex WHERE
> > > clause. Perhaps I need to restrict this to just simple cases of
> > > constants, which are easy to pick out an run through. Doing this with
> > > joins would be very hard, I think.
> >
> > Actually, I was thinking more of an index of returned rows... After each
> > subquery, the backend would check each row to see if it was already in the
> > index... Simple duplicate check, in other words. Of course, I don't know how
> > well this would behave with large tables being returned...
> >
> > Anyone else have some ideas they want to throw in?
> >
> > Taral
> >
>
> But what about unions of join queries? Which OID's then should
> be checked against which? And unions from view selects? There
> are no OID's at all after rewriting.

Yep, you can't just use oid's, I think. Joins and specifiying a table
multiple times using a table alias would break this anyway.

CNF'ify only goes through the tables once, so we somehow need to
simulate this. Perhaps we can restrict the kinds of queries used for
DNF so we can do this easily.

Another idea is that we rewrite queries such as:

SELECT *
FROM tab
WHERE (a=1 AND b=2 AND c=3) OR
(a=1 AND b=2 AND c=4) OR
(a=1 AND b=2 AND c=5) OR
(a=1 AND b=2 AND c=6)

into:

SELECT *
FROM tab
WHERE (a=1 AND b=2) AND (c=3 OR c=4 OR c=5 OR c=6)

and we do this BEFORE calling cnfify(). How much does this do for us?

Seems this would not be too hard, and would be a good performer.

You could even convert:

SELECT *
FROM tab
WHERE (a=1 AND b=2 AND c=3) OR
(a=1 AND b=2 AND c=4) OR
(a=1 AND b=52 AND c=5) OR
(a=1 AND b=52 AND c=6)

into:

SELECT *
FROM tab
WHERE ((a=1 AND b=2) AND (c=3 OR c=4)) OR
WHERE ((a=1 AND b=52) AND (c=5 OR c=6))

This should work OK too. Someone want to try this? David, is this what
your code does?

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