Re: [PATCHES] Descending order Index scan patch

[Charset iso-8859-1 unsupported, filtering to ASCII...]
> 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
> 


This patch is broken.  See the wrapping that happened to the /**** line.
Please resubmit.


> *** ../../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)
> 
> 
> 


--  Bruce Momjian                        |  http://www.op.net/~candle maillist@candle.pha.pa.us            |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026