Thread: Between Node
Finished the Between patch Christopher started. Implements between (symmetric / asymmetric) as a node. Executes the left or right expression once, makes a Const out of the resulting Datum and executes the >=, <= portions out of the Const sets. Of course, the parser does a fair amount of preparatory work for this to happen.
Attachment
Wonderful! It makes me a very happy little punter to see that finished off! Chris > -----Original Message----- > From: pgsql-patches-owner@postgresql.org > [mailto:pgsql-patches-owner@postgresql.org]On Behalf Of Rod Taylor > Sent: Wednesday, 10 July 2002 9:44 AM > To: pgsql-patches@postgresql.org > Subject: [PATCHES] Between Node > > > Finished the Between patch Christopher started. > > Implements between (symmetric / asymmetric) as a node. > > Executes the left or right expression once, makes a Const out of the > resulting Datum and executes the >=, <= portions out of the Const sets. > > Of course, the parser does a fair amount of preparatory work for this to > happen. >
You made a tiny little type in a comment in parsenodes.h (abotu instead of about). It's humbling to read the patch and see just how much I had to go when I gave up! Chris > -----Original Message----- > From: pgsql-patches-owner@postgresql.org > [mailto:pgsql-patches-owner@postgresql.org]On Behalf Of Rod Taylor > Sent: Wednesday, 10 July 2002 9:44 AM > To: pgsql-patches@postgresql.org > Subject: [PATCHES] Between Node > > > Finished the Between patch Christopher started. > > Implements between (symmetric / asymmetric) as a node. > > Executes the left or right expression once, makes a Const out of the > resulting Datum and executes the >=, <= portions out of the Const sets. > > Of course, the parser does a fair amount of preparatory work for this to > happen. >
Your patch has been added to the PostgreSQL unapplied patches list at:
http://candle.pha.pa.us/cgi-bin/pgpatches
I will try to apply it within the next 48 hours.
---------------------------------------------------------------------------
Rod Taylor wrote:
> Finished the Between patch Christopher started.
>
> Implements between (symmetric / asymmetric) as a node.
>
> Executes the left or right expression once, makes a Const out of the
> resulting Datum and executes the >=, <= portions out of the Const sets.
>
> Of course, the parser does a fair amount of preparatory work for this to
> happen.
[ Attachment, skipping... ]
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
--
Bruce Momjian | http://candle.pha.pa.us
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, Pennsylvania 19026
Patch applied. Thanks. --------------------------------------------------------------------------- Rod Taylor wrote: > Finished the Between patch Christopher started. > > Implements between (symmetric / asymmetric) as a node. > > Executes the left or right expression once, makes a Const out of the > resulting Datum and executes the >=, <= portions out of the Const sets. > > Of course, the parser does a fair amount of preparatory work for this to > happen. [ Attachment, skipping... ] > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org -- Bruce Momjian | http://candle.pha.pa.us 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, Pennsylvania 19026
Patch applied. Thanks. --------------------------------------------------------------------------- Christopher Kings-Lynne wrote: > You made a tiny little type in a comment in parsenodes.h (abotu instead of > about). > > It's humbling to read the patch and see just how much I had to go when I > gave up! > > Chris > > > -----Original Message----- > > From: pgsql-patches-owner@postgresql.org > > [mailto:pgsql-patches-owner@postgresql.org]On Behalf Of Rod Taylor > > Sent: Wednesday, 10 July 2002 9:44 AM > > To: pgsql-patches@postgresql.org > > Subject: [PATCHES] Between Node > > > > > > Finished the Between patch Christopher started. > > > > Implements between (symmetric / asymmetric) as a node. > > > > Executes the left or right expression once, makes a Const out of the > > resulting Datum and executes the >=, <= portions out of the Const sets. > > > > Of course, the parser does a fair amount of preparatory work for this to > > happen. > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster > -- Bruce Momjian | http://candle.pha.pa.us 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, Pennsylvania 19026
I believe Tom mentioned that having BETWEEN as a node would allow him to do some sort of funky optimiser improvement. Is that already done or does he still need to do it? Chris > -----Original Message----- > From: pgsql-patches-owner@postgresql.org > [mailto:pgsql-patches-owner@postgresql.org]On Behalf Of Bruce Momjian > Sent: Thursday, 18 July 2002 12:42 PM > To: Rod Taylor > Cc: pgsql-patches@postgresql.org > Subject: Re: [PATCHES] Between Node > > > > Patch applied. Thanks. > > ------------------------------------------------------------------ > --------- > > > Rod Taylor wrote: > > Finished the Between patch Christopher started. > > > > Implements between (symmetric / asymmetric) as a node. > > > > Executes the left or right expression once, makes a Const out of the > > resulting Datum and executes the >=, <= portions out of the Const sets. > > > > Of course, the parser does a fair amount of preparatory work for this to > > happen. > > [ Attachment, skipping... ] > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 6: Have you searched our list archives? > > > > http://archives.postgresql.org > > -- > Bruce Momjian | http://candle.pha.pa.us > 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, Pennsylvania 19026 > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster >
On Thu, 2002-07-18 at 00:56, Christopher Kings-Lynne wrote: > I believe Tom mentioned that having BETWEEN as a node would allow him to do > some sort of funky optimiser improvement. Is that already done or does he > still need to do it? I don't know what improvement this would be. I only touched the parser and executor. It only executes each segment of the between tree once so the only optimization I can think of would be to try to execute the most likely to fail leaf first. > > -----Original Message----- > > From: pgsql-patches-owner@postgresql.org > > [mailto:pgsql-patches-owner@postgresql.org]On Behalf Of Bruce Momjian > > Sent: Thursday, 18 July 2002 12:42 PM > > To: Rod Taylor > > Cc: pgsql-patches@postgresql.org > > Subject: Re: [PATCHES] Between Node > > > > > > > > Patch applied. Thanks. > > > > ------------------------------------------------------------------ > > --------- > > > > > > Rod Taylor wrote: > > > Finished the Between patch Christopher started. > > > > > > Implements between (symmetric / asymmetric) as a node. > > > > > > Executes the left or right expression once, makes a Const out of the > > > resulting Datum and executes the >=, <= portions out of the Const sets. > > > > > > Of course, the parser does a fair amount of preparatory work for this to > > > happen. > > > > [ Attachment, skipping... ] > > > > > > > > ---------------------------(end of broadcast)--------------------------- > > > TIP 6: Have you searched our list archives? > > > > > > http://archives.postgresql.org > > > > -- > > Bruce Momjian | http://candle.pha.pa.us > > 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, Pennsylvania 19026 > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 4: Don't 'kill -9' the postmaster > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) >
Rod Taylor <rbt@zort.ca> writes:
> On Thu, 2002-07-18 at 00:56, Christopher Kings-Lynne wrote:
>> I believe Tom mentioned that having BETWEEN as a node would allow him to do
>> some sort of funky optimiser improvement. Is that already done or does he
>> still need to do it?
> I don't know what improvement this would be. I only touched the parser
> and executor.
In that case, this code is worse than before as far as developing plans
goes. The selectivity estimator needs to be taught about what BETWEEN
means. Currently it recognizes (x > foo AND x < bar) as a range query
--- a BETWEEN node is obviously much easier to recognize, but you still
have to add code to do it.
regards, tom lane
Rod, I have backed out this patch because initdb was failing. Please retest and resubmit. You may want to get the optimizer recoginizing BETWEEN in the same patch so we don't go backwards in terms of optimization. --------------------------------------------------------------------------- Rod Taylor wrote: > Finished the Between patch Christopher started. > > Implements between (symmetric / asymmetric) as a node. > > Executes the left or right expression once, makes a Const out of the > resulting Datum and executes the >=, <= portions out of the Const sets. > > Of course, the parser does a fair amount of preparatory work for this to > happen. [ Attachment, skipping... ] > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org -- Bruce Momjian | http://candle.pha.pa.us 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, Pennsylvania 19026
Ok, I'll pull a fresh copy of cvs source and see what I can do. My current working area isn't causing any problems. On Thu, 2002-07-18 at 13:04, Bruce Momjian wrote: > > Rod, I have backed out this patch because initdb was failing. Please > retest and resubmit. You may want to get the optimizer recoginizing > BETWEEN in the same patch so we don't go backwards in terms of > optimization. > > --------------------------------------------------------------------------- > > Rod Taylor wrote: > > Finished the Between patch Christopher started. > > > > Implements between (symmetric / asymmetric) as a node. > > > > Executes the left or right expression once, makes a Const out of the > > resulting Datum and executes the >=, <= portions out of the Const sets. > > > > Of course, the parser does a fair amount of preparatory work for this to > > happen. > > [ Attachment, skipping... ] > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 6: Have you searched our list archives? > > > > http://archives.postgresql.org > > -- > Bruce Momjian | http://candle.pha.pa.us > 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, Pennsylvania 19026 > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) >
Rod Taylor wrote: > Ok, I'll pull a fresh copy of cvs source and see what I can do. My > current working area isn't causing any problems. I think the depencency stuff may have affected your patch. -- Bruce Momjian | http://candle.pha.pa.us 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, Pennsylvania 19026
On Thu, 2002-07-18 at 09:39, Tom Lane wrote: > Rod Taylor <rbt@zort.ca> writes: > > On Thu, 2002-07-18 at 00:56, Christopher Kings-Lynne wrote: > >> I believe Tom mentioned that having BETWEEN as a node would allow him to do > >> some sort of funky optimiser improvement. Is that already done or does he > >> still need to do it? > > > I don't know what improvement this would be. I only touched the parser > > and executor. > > In that case, this code is worse than before as far as developing plans > goes. The selectivity estimator needs to be taught about what BETWEEN > means. Currently it recognizes (x > foo AND x < bar) as a range query > --- a BETWEEN node is obviously much easier to recognize, but you still > have to add code to do it. > > regards, tom lane >
> In that case, this code is worse than before as far as developing plans > goes. The selectivity estimator needs to be taught about what BETWEEN > means. Currently it recognizes (x > foo AND x < bar) as a range query > --- a BETWEEN node is obviously much easier to recognize, but you still > have to add code to do it. I simply multiple the results of the left, right and common expression after sending them through clause_selectivity() ?
Rod Taylor <rbt@zort.ca> writes:
>> In that case, this code is worse than before as far as developing plans
>> goes. The selectivity estimator needs to be taught about what BETWEEN
>> means. Currently it recognizes (x > foo AND x < bar) as a range query
>> --- a BETWEEN node is obviously much easier to recognize, but you still
>> have to add code to do it.
> I simply multiple the results of the left, right and common expression
> after sending them through clause_selectivity() ?
Huh? I don't see any "common expression" here. You've got a left
comparison and a right comparison.
Actually, at least for the case of the standard asymmetric BETWEEN
clause, you really really want to be able to generate a bounded
indexscan plan (indexscan with x > foo AND x < bar as indexquals).
That used to happen for free, and now will not happen at all, because
BETWEEN is not in the set of operators recognized as indexscannable
operators. Not sure what the most appropriate fix is --- there are a
number of places that we could try to fix it at, with varying
implications as to the amount of code changed and the generality of
the cases handled.
regards, tom lane
Rod, I have backed out this patch because initdb was failing. Please retest and resubmit. You may want to get the optimizer recoginizing BETWEEN in the same patch so we don't go backwards in terms of optimization. Thanks. --------------------------------------------------------------------------- Rod Taylor wrote: > Finished the Between patch Christopher started. > > Implements between (symmetric / asymmetric) as a node. > > Executes the left or right expression once, makes a Const out of the > resulting Datum and executes the >=, <= portions out of the Const sets. > > Of course, the parser does a fair amount of preparatory work for this to > happen. [ Attachment, skipping... ] > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org -- Bruce Momjian | http://candle.pha.pa.us root@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