Skip site navigation (1) Skip section navigation (2)

Re: any way to use indexscan to get last X values

From: Tomaz Borstnar <tomaz(dot)borstnar(at)over(dot)net>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: any way to use indexscan to get last X values
Date: 2003-06-15 23:21:34
Message-ID: 5.2.1.1.0.20030616012108.02a9c1d8@127.0.0.1 (view raw or flat)
Thread:
Lists: pgsql-performance
Here is the 7.3.3 patch as it might help others too...

diff -rcN postgresql-7.3.3/src/backend/parser/analyze.c 
postgresql-7.3.3-grouporderby/src/backend/parser/analyze.c
*** postgresql-7.3.3/src/backend/parser/analyze.c	Thu Feb 13 23:50:09 2003
--- postgresql-7.3.3-grouporderby/src/backend/parser/analyze.c	Mon Jun 16 
00:13:05 2003
***************
*** 1667,1679 ****
   	 */
   	qry->havingQual = transformWhereClause(pstate, stmt->havingClause);

! 	qry->groupClause = transformGroupClause(pstate,
! 											stmt->groupClause,
! 											qry->targetList);

   	qry->sortClause = transformSortClause(pstate,
   										  stmt->sortClause,
   										  qry->targetList);

   	qry->distinctClause = transformDistinctClause(pstate,
   												  stmt->distinctClause,
--- 1667,1682 ----
   	 */
   	qry->havingQual = transformWhereClause(pstate, stmt->havingClause);

!        /*
!         * Transform sorting/grouping stuff.  Do ORDER BY first because both
!         * transformGroupClause and transformDistinctClause need the results.
!         */

   	qry->sortClause = transformSortClause(pstate,
   										  stmt->sortClause,
   										  qry->targetList);
+
+        qry->groupClause = transformGroupClause(pstate, stmt->groupClause, 
qry->targetList, qry->sortClause);

   	qry->distinctClause = transformDistinctClause(pstate,
   												  stmt->distinctClause,
diff -rcN postgresql-7.3.3/src/backend/parser/parse_clause.c 
postgresql-7.3.3-grouporderby/src/backend/parser/parse_clause.c
*** postgresql-7.3.3/src/backend/parser/parse_clause.c	Mon Dec 16 19:39:56 2002
--- postgresql-7.3.3-grouporderby/src/backend/parser/parse_clause.c	Mon Jun 
16 00:24:58 2003
***************
*** 1145,1151 ****
    *
    */
   List *
! transformGroupClause(ParseState *pstate, List *grouplist, List *targetlist)
   {
   	List	   *glist = NIL,
   			   *gl;
--- 1145,1151 ----
    *
    */
   List *
! transformGroupClause(ParseState *pstate, List *grouplist, List 
*targetlist, List *sortClause)
   {
   	List	   *glist = NIL,
   			   *gl;
***************
*** 1153,1173 ****
   	foreach(gl, grouplist)
   	{
   		TargetEntry *tle;

   		tle = findTargetlistEntry(pstate, lfirst(gl),
   								  targetlist, GROUP_CLAUSE);

   		/* avoid making duplicate grouplist entries */
! 		if (!targetIsInSortList(tle, glist))
! 		{
! 			GroupClause *grpcl = makeNode(GroupClause);
!
! 			grpcl->tleSortGroupRef = assignSortGroupRef(tle, targetlist);
!
! 			grpcl->sortop = any_ordering_op(tle->resdom->restype);
!
! 			glist = lappend(glist, grpcl);
! 		}
   	}

   	return glist;
--- 1153,1193 ----
   	foreach(gl, grouplist)
   	{
   		TargetEntry *tle;
+                Oid  ordering_op;
+                GroupClause *grpcl;

   		tle = findTargetlistEntry(pstate, lfirst(gl),
   								  targetlist, GROUP_CLAUSE);

   		/* avoid making duplicate grouplist entries */
!                if (targetIsInSortList(tle, glist))
!                        continue;
!
!                /*
!                 * If the GROUP BY clause matches the ORDER BY clause, we 
want to
!                 * adopt the ordering operators from the latter rather 
than using
!                 * the default ops.  This allows "GROUP BY foo ORDER BY 
foo DESC" to
!                 * be done with only one sort step.  Note we are assuming 
that any
!                 * user-supplied ordering operator will bring equal values 
together,
!                 * which is all that GROUP BY needs.
!                 */
!                if (sortClause &&
!                        ((SortClause *) 
lfirst(sortClause))->tleSortGroupRef ==
!                        tle->resdom->ressortgroupref)
!                {
!                        ordering_op = ((SortClause *) 
lfirst(sortClause))->sortop;
!                        sortClause = lnext(sortClause);
!                 }
!                else
!                {
!                        ordering_op = any_ordering_op(tle->resdom->restype);
!                        sortClause = NIL;       /* disregard ORDER BY once 
match fails */
!                }
!
!                grpcl = makeNode(GroupClause);
!                grpcl->tleSortGroupRef = assignSortGroupRef(tle, targetlist);
!                grpcl->sortop = ordering_op;
!                glist = lappend(glist, grpcl);
   	}

   	return glist;
diff -rcN postgresql-7.3.3/src/include/parser/parse_clause.h 
postgresql-7.3.3-grouporderby/src/include/parser/parse_clause.h
*** postgresql-7.3.3/src/include/parser/parse_clause.h	Thu Jun 20 22:29:51 2002
--- postgresql-7.3.3-grouporderby/src/include/parser/parse_clause.h	Mon Jun 
16 00:08:43 2003
***************
*** 22,28 ****
   extern bool interpretInhOption(InhOption inhOpt);
   extern Node *transformWhereClause(ParseState *pstate, Node *where);
   extern List *transformGroupClause(ParseState *pstate, List *grouplist,
! 					 List *targetlist);
   extern List *transformSortClause(ParseState *pstate, List *orderlist,
   					List *targetlist);
   extern List *transformDistinctClause(ParseState *pstate, List *distinctlist,
--- 22,28 ----
   extern bool interpretInhOption(InhOption inhOpt);
   extern Node *transformWhereClause(ParseState *pstate, Node *where);
   extern List *transformGroupClause(ParseState *pstate, List *grouplist,
! 					 List *targetlist, List *sortClause);
   extern List *transformSortClause(ParseState *pstate, List *orderlist,
   					List *targetlist);
   extern List *transformDistinctClause(ParseState *pstate, List *distinctlist,



In response to

pgsql-performance by date

Next:From: Jim C. NasbyDate: 2003-06-16 05:10:28
Subject: Re: left join performance problem
Previous:From: Tomaz BorstnarDate: 2003-06-15 22:37:30
Subject: Re: any way to use indexscan to get last X values

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group