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

Поиск
Список
Период
Сортировка
От Tomaz Borstnar
Тема Re: any way to use indexscan to get last X values
Дата
Msg-id 5.2.1.1.0.20030616012108.02a9c1d8@127.0.0.1
обсуждение исходный текст
Ответ на Re: any way to use indexscan to get last X values  (Tomaz Borstnar <tomaz.borstnar@over.net>)
Список 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,



В списке pgsql-performance по дате отправления:

Предыдущее
От: Tomaz Borstnar
Дата:
Сообщение: Re: any way to use indexscan to get last X values
Следующее
От: "Jim C. Nasby"
Дата:
Сообщение: Re: left join performance problem