Descending order Index scan patch - Mailing list pgsql-hackers

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)