From: Oleg Bartunov <oleg@sai.msu.su>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Alex@Icepick.com
Cc: pgsql-sql@postgreSQL.org
Subject: Re: [SQL] How to get last 10 rows in a table on a large database?
Date: Sat, 6 Nov 1999 22:09:48 +0300 (MSK)
Message-ID: <Pine.GSO.3.96.SK.991106220719.3910O-200000@ra> (raw)
In-Reply-To: <2514.941911947@sss.pgh.pa.us>
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
Attachments:desc2.diff (text/plain, 10.4K) download
*** 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;
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: oleg@sai.msu.su, tgl@sss.pgh.pa.us, Alex@Icepick.com
Subject: Re: [SQL] How to get last 10 rows in a table on a large database?
In-Reply-To: <Pine.GSO.3.96.SK.991106220719.3910O-200000@ra>
References: <2514.941911947@sss.pgh.pa.us> <Pine.GSO.3.96.SK.991106220719.3910O-200000@ra>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox