pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
How 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