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 по дате отправления: