Skip site navigation (1) Skip section navigation (2)

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

From: David Hartwig <daveh(at)insightdist(dot)com>
To: Bruce Momjian <maillist(at)candle(dot)pha(dot)pa(dot)us>
Cc: Taral <taral(at)mail(dot)utexas(dot)edu>, jwieck(at)debis(dot)com, hackers(at)postgreSQL(dot)org
Subject: Re: [HACKERS] RE: [GENERAL] Long update query ? (also Re: [GENERAL] CNF vs. DNF)
Date: 1998-10-05 14:33:37
Message-ID: 3618D8C1.FCA36D21@insightdist.com (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-hackers

Bruce Momjian wrote:

> I have another idea.
>
> When we cnfify, this:
>
>         (A AND B) OR (C AND D)
>
> becomes
>
>         (A OR C) AND (A OR D) AND (B OR C) AND (B OR D)
>
> however if A and C are identical, this could become:
>
>         (A OR A) AND (A OR D) AND (B OR A) AND (B OR D)
>
> and A OR A is A:
>
>         A AND (A OR D) AND (B OR A) AND (B OR D)
>
> and since we are now saying A has to be true, we can remove OR's with A:
>
>         A AND (B OR D)
>
> Much smaller, and a big win for queries like:
>
>         SELECT *
>         FROM tab
>         WHERE   (a=1 AND b=2) OR
>                 (a=1 AND b=3)
>
> This becomes:
>
>                 (a=1) AND (b=2 OR b=3)
>
> which is accurate, and uses our OR indexing.
>
> Seems I could code cnfify() to look for identical qualifications in two
> joined OR clauses and remove the duplicates.
>
> Sound like big win, and fairly easy and inexpensive in processing time.
>
> Comments?

Apologies for not commenting sooner.   I have been incognito.

As to your earlier question, Bruce, the KSQO patch rewrites qualifying
queries as UNIONs.

    (A AND B) OR (C AND D)  ==>    (A AND B) UNION (C AND D)

The rules to qualify are fairly strict.     Must be have ANDs; rectangular in
shape;  all (VAR op CONST) type nodes; minimum of 10 nodes; etc.   I was
targeting the keysets queries generated by ODBC tools.

As for the current direction this thread is going, (factoring) I have one
word of caution.   PREPARE.   If you take this route, you will never be able
to implement a workable PREPARE statement.   I believe that in order for
PostgrerSQL ever become a industrial strength client/server it must implement
a PREPARE statement with parameters.



In response to

Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 1998-10-05 14:36:06
Subject: Re: [HACKERS] Open 6.4 items
Previous:From: Tom LaneDate: 1998-10-05 14:18:47
Subject: man-page install (was Re: pg_dump new -n flag)

pgsql-general by date

Next:From: Doug YoungerDate: 1998-10-05 16:13:51
Subject: Binary number data type?
Previous:From: Greg YoungbloodDate: 1998-10-05 12:15:39
Subject: RE: [GENERAL] Please help with performance tuning on Postgres

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group