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(at)tpf(dot)co(dot)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(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026