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@tpf.co.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)