Re: [SQL] How to get last 10 rows in a table on a large database? - Mailing list pgsql-sql

On Sat, 6 Nov 1999, Tom Lane wrote:

> Date: Sat, 06 Nov 1999 13:12:27 -0500
> From: Tom Lane <tgl@sss.pgh.pa.us>
> To: Alex@Icepick.com
> Cc: pgsql-sql@postgreSQL.org
> Subject: Re: [SQL] How to get last 10 rows in a table on a large database? 
> 
> Alex@Icepick.com (Alex) writes:
> > Does anyone have a good idea to get the last 10 rows from a table? I
> > tried 
> > SELECT * FROM table ORDER BY datetime DESC LIMIT 10
> > But my table has over 1 million rows, so this takes forever.. anyone
> > has a better idea on how to do this? 
> 
> FWIW, that query should work the way you want in 7.0 (assuming you have
> an index on datetime).  6.5.* isn't bright enough to use an index for
> ORDER BY DESC, though.  I think Stoyan's suggestion of declaring a
> cursor is probably the best workaround for now --- or you could consult
> the patches list archives for the patch that will make 6.5 use an index.

I attached patch for 6.5.3 Hiroshi kindly made on my request. 
It applies cleanly and seems works ok.
Oleg




> 
>             regards, tom lane
> 
> ************
> 

_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
*** backend/optimizer/plan/planner.c.org    Mon Aug  2 15:27:02 1999
--- backend/optimizer/plan/planner.c    Wed Nov  3 10:04:41 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,888 ****      * ----------      */     if (plan->lefttree != NULL)
!         return TRUE;     if (plan->righttree != NULL)
!         return TRUE;      /* ----------      * Must be a single index scan      * ----------      */     if
(length(indexScan->indxid)!= 1)
 
!         return TRUE;      /* ----------      * Indices can only have up to 8 attributes. So an ORDER BY using
--- 886,901 ----      * ----------      */     if (plan->lefttree != NULL)
!         return nodir;     if (plan->righttree != NULL)
!         return nodir;      /* ----------      * Must be a single index scan      * ----------      */     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,981 ****              * Sort order isn't in ascending order.              * ----------              */
!             return TRUE;         }          key_no++;
--- 988,1006 ----              * 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; }
*** backend/executor/nodeIndexscan.c.org    Wed May 26 01:08:43 1999
--- backend/executor/nodeIndexscan.c    Wed Nov  3 10:04:41 1999
***************
*** 105,110 ****
--- 105,117 ----      */     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;
***************
*** 322,327 ****
--- 329,336 ----     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 &&
 
***************
*** 972,977 ****
--- 981,988 ----     }      indexstate->iss_NumIndices = numIndices;
+     if (ScanDirectionIsBackward(node->indxorderdir))
+         indexPtr = numIndices;     indexstate->iss_IndexPtr = indexPtr;     indexstate->iss_ScanKeys = scanKeys;
indexstate->iss_NumScanKeys= numScanKeys;
 
*** backend/optimizer/plan/createplan.c.org    Thu Jul 29 12:34:11 1999
--- backend/optimizer/plan/createplan.c    Wed Nov  3 10:04:41 1999
***************
*** 1040,1045 ****
--- 1040,1046 ----     node->indxid = indxid;     node->indxqual = indxqual;     node->indxqualorig = indxqualorig;
+     node->indxorderdir = NoMovementScanDirection;     node->scan.scanstate = (CommonScanState *) NULL;      return
node;
*** backend/nodes/copyfuncs.c.org    Wed May 26 07:41:11 1999
--- backend/nodes/copyfuncs.c    Wed Nov  3 10:04:41 1999
***************
*** 251,256 ****
--- 251,257 ----     newnode->indxid = listCopy(from->indxid);     Node_Copy(from, newnode, indxqual);
Node_Copy(from,newnode, indxqualorig);
 
+     newnode->indxorderdir = from->indxorderdir;      return newnode; }
*** backend/nodes/readfuncs.c.org    Wed May 26 01:09:11 1999
--- backend/nodes/readfuncs.c    Wed Nov  3 10:04:41 1999
***************
*** 546,551 ****
--- 546,556 ----     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; } 
*** backend/nodes/outfuncs.c.org    Wed May 26 07:41:14 1999
--- backend/nodes/outfuncs.c    Wed Nov  3 10:04:41 1999
***************
*** 450,455 ****
--- 450,456 ----     appendStringInfo(str, " :indxqualorig ");     _outNode(str, node->indxqualorig); 
+     appendStringInfo(str, " :indxorderdir %d ", node->indxorderdir); }  /*
*** backend/nodes/equalfuncs.c.org    Thu Jul 29 12:34:11 1999
--- backend/nodes/equalfuncs.c    Wed Nov  3 10:04:41 1999
***************
*** 527,532 ****
--- 527,535 ----     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;
*** backend/commands/explain.c.org    Mon Aug  2 14:56:58 1999
--- backend/commands/explain.c    Wed Nov  3 10:04:41 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)
 
*** include/nodes/plannodes.h.org    Wed May 26 07:42:58 1999
--- include/nodes/plannodes.h    Wed Nov  3 10:04:41 1999
***************
*** 175,180 ****
--- 175,181 ----     List       *indxid;     List       *indxqual;     List       *indxqualorig;
+     ScanDirection    indxorderdir;     IndexScanState *indxstate; } IndexScan;