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.

Wonderful! It makes me a very happy little punter to see that finished off!

Chris

> -----Original Message-----
> From: pgsql-patches-owner(at)postgresql(dot)org
> [mailto:pgsql-patches-owner(at)postgresql(dot)org]On Behalf Of Rod Taylor
> Sent: Wednesday, 10 July 2002 9:44 AM
> To: pgsql-patches(at)postgresql(dot)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(at)postgresql(dot)org
> [mailto:pgsql-patches-owner(at)postgresql(dot)org]On Behalf Of Rod Taylor
> Sent: Wednesday, 10 July 2002 9:44 AM
> To: pgsql-patches(at)postgresql(dot)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(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

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(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

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(at)postgresql(dot)org
> > [mailto:pgsql-patches-owner(at)postgresql(dot)org]On Behalf Of Rod Taylor
> > Sent: Wednesday, 10 July 2002 9:44 AM
> > To: pgsql-patches(at)postgresql(dot)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(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

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(at)postgresql(dot)org
> [mailto:pgsql-patches-owner(at)postgresql(dot)org]On Behalf Of Bruce Momjian
> Sent: Thursday, 18 July 2002 12:42 PM
> To: Rod Taylor
> Cc: pgsql-patches(at)postgresql(dot)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(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
>
> ---------------------------(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(at)postgresql(dot)org
> > [mailto:pgsql-patches-owner(at)postgresql(dot)org]On Behalf Of Bruce Momjian
> > Sent: Thursday, 18 July 2002 12:42 PM
> > To: Rod Taylor
> > Cc: pgsql-patches(at)postgresql(dot)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(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
> >
> > ---------------------------(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(at)postgresql(dot)org)
>

Rod Taylor <rbt(at)zort(dot)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.

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(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

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(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

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(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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)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(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

On Thu, 2002-07-18 at 09:39, Tom Lane wrote:
> Rod Taylor <rbt(at)zort(dot)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(at)zort(dot)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