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

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

From: Bruce Momjian <maillist(at)candle(dot)pha(dot)pa(dot)us>
To: jwieck(at)debis(dot)com
Cc: taral(at)mail(dot)utexas(dot)edu, hackers(at)postgreSQL(dot)org
Subject: Re: [HACKERS] RE: [GENERAL] Long update query ? (also Re: [GENERAL] CNF vs. DNF)
Date: 1998-10-02 17:58:25
Message-ID: 199810021758.NAA15524@candle.pha.pa.us (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-hackers
> > 
> > > > 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


In response to

Responses

pgsql-hackers by date

Next:From: Bruce MomjianDate: 1998-10-02 18:31:53
Subject: documentation changes
Previous:From: Bruce MomjianDate: 1998-10-02 17:40:39
Subject: Re: [HACKERS] RE: [GENERAL] Long update query ? (also Re: [GENERAL] CNF vs. DNF)

pgsql-general by date

Next:From: Greg YoungbloodDate: 1998-10-02 19:09:06
Subject: Please help with performance tuning on Postgres
Previous:From: Martin SchulzeDate: 1998-10-02 17:56:18
Subject: Re: [GENERAL] Re: More PostgreSQL stuff

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