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

view thread (5+ messages)

Message-ID: <Pine.GSO.3.96.SK.991106220719.3910O-200000@ra>
Permalink:  ../Pine.GSO.3.96.SK.991106220719.3910O-200000@ra/
Also on:    postgresql.org/message-id/Pine.GSO.3.96.SK.991106220719.3910O-200000@ra

 ·  · 

reply

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