Between Node

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

Chris

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

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

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.

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.

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

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.

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

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