pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to get last 10 rows in a table on a large database?
5+ messages / 4 participants
[nested] [flat]
* How to get last 10 rows in a table on a large database?
@ 1999-11-06 10:37 Alex <Alex@Icepick.com>
1999-11-06 11:36 ` Re: [SQL] How to get last 10 rows in a table on a large database? Stoyan Genov <genov@digsys.bg>
1999-11-06 18:12 ` Re: [SQL] How to get last 10 rows in a table on a large database? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 5+ messages in thread
From: Alex@Icepick.com @ 1999-11-06 10:37 UTC (permalink / raw)
To: pgsql-sql
Hi,
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?
Alex
--
Alex van Es, P.O. Box 698, 7300 AR Apeldoorn, The Netherlands.
8 Webcams, doorbell, fridge, barcodescanner.. all connected to the internet.
Check http://www.icepick.com for details..
NEW! A Hyperwired cat.. http://www.blackie.com!
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [SQL] How to get last 10 rows in a table on a large database?
1999-11-06 10:37 How to get last 10 rows in a table on a large database? Alex <Alex@Icepick.com>
@ 1999-11-06 11:36 ` Stoyan Genov <genov@digsys.bg>
1999-11-06 18:16 ` Re: [SQL] How to get last 10 rows in a table on a large database? Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 5+ messages in thread
From: Stoyan Genov @ 1999-11-06 11:36 UTC (permalink / raw)
To: Alex@icepick.com; +Cc: pgsql-sql
Hi.
You should try this:
begin transaction;
declare tbl_cur cursor for select * from table order by datetime ASC;
fetch forward 10 in tbl_cur;
end transaction;
This is true if you want THE LAST 10 rows from the selection - you order it
in reverse and get the first 10. You are unable to say:
begin transaction;
declare tbl_cur cursor for .... .....
move forward all in tbl_cur;
fetch backward 10 in tbl_cur;
...........
end transaction;
because when you "move forward all" the result gets lost.
If something is unclear, do write back...
Stoyan Genov
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [SQL] How to get last 10 rows in a table on a large database?
1999-11-06 10:37 How to get last 10 rows in a table on a large database? Alex <Alex@Icepick.com>
1999-11-06 11:36 ` Re: [SQL] How to get last 10 rows in a table on a large database? Stoyan Genov <genov@digsys.bg>
@ 1999-11-06 18:16 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 1999-11-06 18:16 UTC (permalink / raw)
To: Stoyan Genov <genov@digsys.bg>; +Cc: Alex@icepick.com; pgsql-sql
Stoyan Genov <genov@digsys.bg> writes:
> You are unable to say:
> begin transaction;
> declare tbl_cur cursor for .... .....
> move forward all in tbl_cur;
> fetch backward 10 in tbl_cur;
> ...........
> end transaction;
> because when you "move forward all" the result gets lost.
Huh? It seems to work fine for me.
However, the "move" is a tad slow since the silly thing actually scans
over all the tuples in index order :-(. This might not be a useful
solution for Alex's problem after all, because of the time taken by
the "move".
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [SQL] How to get last 10 rows in a table on a large database?
1999-11-06 10:37 How to get last 10 rows in a table on a large database? Alex <Alex@Icepick.com>
@ 1999-11-06 18:12 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-11-06 19:09 ` Re: [SQL] How to get last 10 rows in a table on a large database? Oleg Bartunov <oleg@sai.msu.su>
1 sibling, 1 reply; 5+ messages in thread
From: Tom Lane @ 1999-11-06 18:12 UTC (permalink / raw)
To: Alex@Icepick.com; +Cc: pgsql-sql
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.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [SQL] How to get last 10 rows in a table on a large database?
1999-11-06 10:37 How to get last 10 rows in a table on a large database? Alex <Alex@Icepick.com>
1999-11-06 18:12 ` Re: [SQL] How to get last 10 rows in a table on a large database? Tom Lane <tgl@sss.pgh.pa.us>
@ 1999-11-06 19:09 ` Oleg Bartunov <oleg@sai.msu.su>
0 siblings, 0 replies; 5+ messages in thread
From: Oleg Bartunov @ 1999-11-06 19:09 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Alex@Icepick.com; 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
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;
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~1999-11-06 19:09 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-11-06 10:37 How to get last 10 rows in a table on a large database? Alex <Alex@Icepick.com>
1999-11-06 11:36 ` Stoyan Genov <genov@digsys.bg>
1999-11-06 18:16 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-11-06 18:12 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-11-06 19:09 ` Oleg Bartunov <oleg@sai.msu.su>
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