Descending order Index scan patch

Hi all,

In v6.5

Prevent sorting if result is already sorted

was implemented by Jan Wieck.
His work is for ascending order cases.

Here is a patch to prevent sorting also in descending
order cases.
Because I had already changed _bt_first() to position
backward correctly before v6.5,this patch would work.

This patch needs "make clean" .

Regards.

Hiroshi Inoue
Inoue(at)tpf(dot)co(dot)jp

*** ../../head/pgcurrent/backend/optimizer/plan/planner.c Mon Jul 26
12:44:55 1999
--- backend/optimizer/plan/planner.c Mon Aug 9 11:01:49 1999
***************
*** 39,45 ****
static Plan *make_groupplan(List *group_tlist, bool tuplePerGroup,
List *groupClause, AttrNumber *grpColIdx,
Plan *subplan);
! static bool need_sortplan(List *sortcls, Plan *plan);
static Plan *make_sortplan(List *tlist, List *sortcls, Plan *plannode);

/***************************************************************************
**
--- 39,45 ----
static Plan *make_groupplan(List *group_tlist, bool tuplePerGroup,
List *groupClause, AttrNumber *grpColIdx,
Plan *subplan);
! static ScanDirection get_dir_to_omit_sortplan(List *sortcls, Plan *plan);
static Plan *make_sortplan(List *tlist, List *sortcls, Plan *plannode);

/***************************************************************************
**
***************
*** 303,310 ****
}
else
{
! if (parse->sortClause && need_sortplan(parse->sortClause,
result_plan))
! return (make_sortplan(tlist, parse->sortClause, result_plan));
else
return ((Plan *) result_plan);
}
--- 303,319 ----
}
else
{
! if (parse->sortClause)
! {
! ScanDirection dir = get_dir_to_omit_sortplan(parse->sortClause,
result_plan);
! if (ScanDirectionIsNoMovement(dir))
! return (make_sortplan(tlist, parse->sortClause, result_plan));
! else
!

! ((IndexScan *)result_plan)->indxorderdir = dir;
! return ((Plan *) result_plan);
! }
! }
else
return ((Plan *) result_plan);
}
***************
*** 822,828 ****

/* ----------
! * Support function for need_sortplan
* ----------
*/
static TargetEntry *
--- 831,837 ----

/* ----------
! * Support function for get scan direction to omit sortplan
* ----------
*/
static TargetEntry *
***************
*** 845,855 ****
* Check if a user requested ORDER BY is already satisfied by
* the choosen index scan.
*
! * Returns TRUE if sort is required, FALSE if can be omitted.
* ----------
*/
! static bool
! need_sortplan(List *sortcls, Plan *plan)
{
Relation indexRel;
IndexScan *indexScan;
--- 854,866 ----
* Check if a user requested ORDER BY is already satisfied by
* the choosen index scan.
*
! * Returns the direction of Index scan to omit sort,
! * if sort is required returns NoMovementScanDirection
! *
* ----------
*/
! static ScanDirection
! get_dir_to_omit_sortplan(List *sortcls, Plan *plan)
{
Relation indexRel;
IndexScan *indexScan;
***************
*** 858,870 ****
HeapTuple htup;
Form_pg_index index_tup;
int key_no = 0;

/* ----------
* Must be an IndexScan
* ----------
*/
if (nodeTag(plan) != T_IndexScan)
! return TRUE;

indexScan = (IndexScan *) plan;

--- 869,883 ----
HeapTuple htup;
Form_pg_index index_tup;
int key_no = 0;
+ ScanDirection dir, nodir = NoMovementScanDirection;

+ dir = nodir;
/* ----------
* Must be an IndexScan
* ----------
*/
if (nodeTag(plan) != T_IndexScan)
! return nodir;

indexScan = (IndexScan *) plan;

***************
*** 873,881 ****
* ----------
*/
if (plan->lefttree != NULL)
! return TRUE;
if (plan->righttree != NULL)
! return TRUE;

/* ----------
* Must be a single index scan
--- 886,894 ----
* ----------
*/
if (plan->lefttree != NULL)
! return nodir;
if (plan->righttree != NULL)
! return nodir;

/* ----------
* Must be a single index scan
***************
*** 882,888 ****
* ----------
*/
if (length(indexScan->indxid) != 1)
! return TRUE;

/* ----------
* Indices can only have up to 8 attributes. So an ORDER BY using
--- 895,901 ----
* ----------
*/
if (length(indexScan->indxid) != 1)
! return nodir;

/* ----------
* Indices can only have up to 8 attributes. So an ORDER BY using
***************
*** 890,896 ****
* ----------
*/
if (length(sortcls) > 8)
! return TRUE;

/* ----------
* The choosen Index must be a btree
--- 903,909 ----
* ----------
*/
if (length(sortcls) > 8)
! return nodir;

/* ----------
* The choosen Index must be a btree
***************
*** 902,908 ****
if (strcmp(nameout(&(indexRel->rd_am->amname)), "btree") != 0)
{
heap_close(indexRel);
! return TRUE;
}
heap_close(indexRel);

--- 915,921 ----
if (strcmp(nameout(&(indexRel->rd_am->amname)), "btree") != 0)
{
heap_close(indexRel);
! return nodir;
}
heap_close(indexRel);

***************
*** 937,943 ****
* Could this happen?
* ----------
*/
! return TRUE;
}
if (nodeTag(tle->expr) != T_Var)
{
--- 950,956 ----
* Could this happen?
* ----------
*/
! return nodir;
}
if (nodeTag(tle->expr) != T_Var)
{
***************
*** 946,952 ****
* cannot be the indexed attribute
* ----------
*/
! return TRUE;
}
var = (Var *) (tle->expr);

--- 959,965 ----
* cannot be the indexed attribute
* ----------
*/
! return nodir;
}
var = (Var *) (tle->expr);

***************
*** 957,963 ****
* that of the index
* ----------
*/
! return TRUE;
}

if (var->varattno != index_tup->indkey[key_no])
--- 970,976 ----
* that of the index
* ----------
*/
! return nodir;
}

if (var->varattno != index_tup->indkey[key_no])
***************
*** 966,972 ****
* It isn't the indexed attribute.
* ----------
*/
! return TRUE;
}

if (oprid(oper("<", resdom->restype, resdom->restype, FALSE)) !=
sortcl->opoid)
--- 979,985 ----
* It isn't the indexed attribute.
* ----------
*/
! return nodir;
}

if (oprid(oper("<", resdom->restype, resdom->restype, FALSE)) !=
sortcl->opoid)
***************
*** 975,982 ****
* Sort order isn't in ascending order.
* ----------
*/
! return TRUE;
}

key_no++;
}
--- 988,1007 ----
* Sort order isn't in ascending order.
* ----------
*/
! if (ScanDirectionIsForward(dir))
! return nodir;
! dir = BackwardScanDirection;
}
+ else
+ {
+ /* ----------
+ * Sort order is in ascending order.
+ * ----------
+ */
+ if (ScanDirectionIsBackward(dir))
+ return nodir;
+ dir = ForwardScanDirection;
+ }

key_no++;
}
***************
*** 985,989 ****
* Index matches ORDER BY - sort not required
* ----------
*/
! return FALSE;
}
--- 1010,1014 ----
* Index matches ORDER BY - sort not required
* ----------
*/
! return dir;
}
*** ../../head/pgcurrent/backend/executor/nodeIndexscan.c Mon Jul 26
12:44:47 1999
--- backend/executor/nodeIndexscan.c Mon Aug 9 10:54:23 1999
***************
*** 99,104 ****
--- 99,111 ----
*/
estate = node->scan.plan.state;
direction = estate->es_direction;
+ if (ScanDirectionIsBackward(node->indxorderdir))
+ {
+ if (ScanDirectionIsForward(direction))
+ direction = BackwardScanDirection;
+ else if (ScanDirectionIsBackward(direction))
+ direction = ForwardScanDirection;
+ }
snapshot = estate->es_snapshot;
scanstate = node->scan.scanstate;
indexstate = node->indxstate;
***************
*** 316,321 ****
--- 323,330 ----
indxqual = node->indxqual;
numScanKeys = indexstate->iss_NumScanKeys;
indexstate->iss_IndexPtr = -1;
+ if (ScanDirectionIsBackward(node->indxorderdir))
+ indexstate->iss_IndexPtr = numIndices;

/* If this is re-scanning of PlanQual ... */
if (estate->es_evTuple != NULL &&
***************
*** 966,971 ****
--- 975,982 ----
}

indexstate->iss_NumIndices = numIndices;
+ if (ScanDirectionIsBackward(node->indxorderdir))
+ indexPtr = numIndices;
indexstate->iss_IndexPtr = indexPtr;
indexstate->iss_ScanKeys = scanKeys;
indexstate->iss_NumScanKeys = numScanKeys;
*** ../../head/pgcurrent/backend/optimizer/plan/createplan.c Mon Aug 9
11:31:33 1999
--- backend/optimizer/plan/createplan.c Mon Aug 9 11:48:55 1999
***************
*** 1024,1029 ****
--- 1024,1030 ----
node->indxid = indxid;
node->indxqual = indxqual;
node->indxqualorig = indxqualorig;
+ node->indxorderdir = NoMovementScanDirection;
node->scan.scanstate = (CommonScanState *) NULL;

return node;
*** ../../head/pgcurrent/backend/nodes/copyfuncs.c Wed Jul 28 15:25:51 1999
--- backend/nodes/copyfuncs.c Mon Aug 9 10:55:00 1999
***************
*** 238,243 ****
--- 238,244 ----
newnode->indxid = listCopy(from->indxid);
Node_Copy(from, newnode, indxqual);
Node_Copy(from, newnode, indxqualorig);
+ newnode->indxorderdir = from->indxorderdir;

return newnode;
}
*** ../../head/pgcurrent/backend/nodes/readfuncs.c Mon Jul 26 14:45:56 1999
--- backend/nodes/readfuncs.c Mon Aug 9 11:00:47 1999
***************
*** 532,537 ****
--- 532,542 ----
token = lsptok(NULL, &length); /* eat :indxqualorig */
local_node->indxqualorig = nodeRead(true); /* now read it */

+ token = lsptok(NULL, &length); /* eat :indxorderdir */
+ token = lsptok(NULL, &length); /* get indxorderdir */
+
+ local_node->indxorderdir = atoi(token);
+
return local_node;
}

*** ../../head/pgcurrent/backend/nodes/outfuncs.c Mon Jul 26 14:45:56 1999
--- backend/nodes/outfuncs.c Mon Aug 9 10:55:28 1999
***************
*** 445,450 ****
--- 445,451 ----
appendStringInfo(str, " :indxqualorig ");
_outNode(str, node->indxqualorig);

+ appendStringInfo(str, " :indxorderdir %d ", node->indxorderdir);
}

/*
*** ../../head/pgcurrent/backend/nodes/equalfuncs.c Fri Jul 30 17:29:37
1999
--- backend/nodes/equalfuncs.c Mon Aug 9 10:55:08 1999
***************
*** 437,442 ****
--- 437,445 ----
if (a->scan.scanrelid != b->scan.scanrelid)
return false;

+ if (a->indxorderdir != b->indxorderdir)
+ return false;
+
if (!equali(a->indxid, b->indxid))
return false;
return true;
*** ../../head/pgcurrent/include/nodes/plannodes.h Mon Jul 26 12:45:39 1999
--- include/nodes/plannodes.h Mon Aug 9 10:52:54 1999
***************
*** 175,180 ****
--- 175,181 ----
List *indxid;
List *indxqual;
List *indxqualorig;
+ ScanDirection indxorderdir;
IndexScanState *indxstate;
} IndexScan;

*** ../../head/pgcurrent/backend/commands/explain.c Mon Jul 26 12:44:46
1999
--- backend/commands/explain.c Mon Aug 9 10:53:44 1999
***************
*** 200,205 ****
--- 200,207 ----
switch (nodeTag(plan))
{
case T_IndexScan:
+ if (ScanDirectionIsBackward(((IndexScan *)plan)->indxorderdir))
+ appendStringInfo(str, " Backward");
appendStringInfo(str, " using ");
i = 0;
foreach(l, ((IndexScan *) plan)->indxid)