Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 0A9A99FB2B0 for ; Fri, 24 Aug 2007 13:26:10 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 34586-01 for ; Fri, 24 Aug 2007 13:26:06 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from exprod6og54.obsmtp.com (exprod6og54.obsmtp.com [64.18.1.189]) by postgresql.org (Postfix) with SMTP id 1792F9FB2AD for ; Fri, 24 Aug 2007 13:26:05 -0300 (ADT) Received: from source ([63.240.6.46]) (using TLSv1) by exprod6ob54.postini.com ([64.18.5.12]) with SMTP; Fri, 24 Aug 2007 09:26:02 PDT Received: from 172.16.1.114 by mail.mi8.com with ESMTP (- Welcome to Mi8 Corporation www.Mi8.com (D2)); Fri, 24 Aug 2007 12:26:30 -0400 X-Server-Uuid: 7829E76E-BB9E-4995-8473-3C0929DF7DD1 Received: from MI8NYCMAIL08.Mi8.com ([172.16.1.164]) by d01smtp07.Mi8.com with Microsoft SMTPSVC(6.0.3790.1830); Fri, 24 Aug 2007 12:26:29 -0400 Received: from 209.131.62.113 ([209.131.62.113]) by MI8NYCMAIL08.Mi8.com ([172.16.1.190]) via Exchange Front-End Server mi8owa.mi8.com ( [172.16.1.134]) with Microsoft Exchange Server HTTP-DAV ; Fri, 24 Aug 2007 16:26:28 +0000 User-Agent: Microsoft-Entourage/11.3.6.070618 Date: Fri, 24 Aug 2007 09:25:53 -0700 Subject: Re: partitioned table and ORDER BY indexed_field DESC LIMIT 1 From: "Luke Lonergan" To: "Heikki Linnakangas" , "Anton" cc: pgsql-performance@postgresql.org Message-ID: Thread-Topic: [PERFORM] partitioned table and ORDER BY indexed_field DESC LIMIT 1 Thread-Index: AcfmOwLg5KgGzyOtRWi3sUHduZNHdwAMG3Qw In-Reply-To: <46CEB522.9030907@enterprisedb.com> MIME-Version: 1.0 X-OriginalArrivalTime: 24 Aug 2007 16:26:29.0136 (UTC) FILETIME=[863CA900:01C7E66B] X-WSS-ID: 6AD1D93F1NW21635700-02-01 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200708/288 X-Sequence-Number: 26418 Below is a patch against 8.2.4 (more or less), Heikki can you take a look at it? This enables the use of index scan of a child table by recognizing sort order of the append node. Kurt Harriman did the work. - Luke Index: cdb-pg/src/backend/optimizer/path/indxpath.c =================================================================== RCS file: /data/FISHEYE_REPOSITORIES/greenplum/cvsroot/cdb2/cdb-pg/src/backend/optimiz er/path/indxpath.c,v diff -u -N -r1.22 -r1.22.2.1 --- cdb-pg/src/backend/optimizer/path/indxpath.c 25 Apr 2007 22:07:21 -0000 1.22 +++ cdb-pg/src/backend/optimizer/path/indxpath.c 10 Aug 2007 03:41:15 -0000 1.22.2.1 @@ -379,8 +379,51 @@ index_pathkeys = build_index_pathkeys(root, index, ForwardScanDirection, true); - useful_pathkeys = truncate_useless_pathkeys(root, rel, - index_pathkeys); + /* + * CDB: For appendrel child, pathkeys contain Var nodes in terms + * of the child's baserel. Transform the pathkey list to refer to + * columns of the appendrel. + */ + if (rel->reloptkind == RELOPT_OTHER_MEMBER_REL) + { + AppendRelInfo *appinfo = NULL; + RelOptInfo *appendrel = NULL; + ListCell *appcell; + CdbPathLocus notalocus; + + /* Find the appendrel of which this baserel is a child. */ + foreach(appcell, root->append_rel_list) + { + appinfo = (AppendRelInfo *)lfirst(appcell); + if (appinfo->child_relid == rel->relid) + break; + } + Assert(appinfo); + appendrel = find_base_rel(root, appinfo->parent_relid); + + /* + * The pathkey list happens to have the same format as the + * partitioning key of a Hashed locus, so by disguising it + * we can use cdbpathlocus_pull_above_projection() to do the + * transformation. + */ + CdbPathLocus_MakeHashed(¬alocus, index_pathkeys); + notalocus = + cdbpathlocus_pull_above_projection(root, + notalocus, + rel->relids, + rel->reltargetlist, + appendrel->reltargetlist, + appendrel->relid); + if (CdbPathLocus_IsHashed(notalocus)) + index_pathkeys = truncate_useless_pathkeys(root, appendrel, + notalocus.partkey); + else + index_pathkeys = NULL; + } + + useful_pathkeys = truncate_useless_pathkeys(root, rel, + index_pathkeys); } else useful_pathkeys = NIL; Index: cdb-pg/src/backend/optimizer/path/pathkeys.c =================================================================== RCS file: /data/FISHEYE_REPOSITORIES/greenplum/cvsroot/cdb2/cdb-pg/src/backend/optimiz er/path/pathkeys.c,v diff -u -N -r1.18 -r1.18.2.1 --- cdb-pg/src/backend/optimizer/path/pathkeys.c 30 Apr 2007 05:44:07 -0000 1.18 +++ cdb-pg/src/backend/optimizer/path/pathkeys.c 10 Aug 2007 03:41:15 -0000 1.18.2.1 @@ -1403,55 +1403,53 @@ { PathKeyItem *item; Expr *newexpr; + AttrNumber targetindex; Assert(pathkey); - /* Use constant expr if available. Will be at head of list. */ - if (CdbPathkeyEqualsConstant(pathkey)) + /* Find an expr that we can rewrite to use the projected columns. */ + item = cdbpullup_findPathKeyItemInTargetList(pathkey, + relids, + targetlist, + &targetindex); // OUT + + /* If not found, see if the equiv class contains a constant expr. */ + if (!item && + CdbPathkeyEqualsConstant(pathkey)) { item = (PathKeyItem *)linitial(pathkey); newexpr = (Expr *)copyObject(item->key); } - /* New vars for old! */ - else - { - AttrNumber targetindex; + /* Fail if no usable expr. */ + else if (!item) + return NULL; - /* Find an expr that we can rewrite to use the projected columns. */ - item = cdbpullup_findPathKeyItemInTargetList(pathkey, - relids, - targetlist, - &targetindex); // OUT - if (!item) - return NULL; + /* If found matching targetlist item, make a Var that references it. */ + else if (targetindex > 0) + newexpr = (Expr *)cdbpullup_makeVar(newrelid, + targetindex, + newvarlist, + (Expr *)item->key); - /* If found matching targetlist item, make a Var that references it. */ - if (targetindex > 0) - newexpr = (Expr *)cdbpullup_makeVar(newrelid, - targetindex, - newvarlist, - (Expr *)item->key); + /* Replace expr's Var nodes with new ones referencing the targetlist. */ + else + newexpr = cdbpullup_expr((Expr *)item->key, + targetlist, + newvarlist, + newrelid); - /* Replace expr's Var nodes with new ones referencing the targetlist. */ - else - newexpr = cdbpullup_expr((Expr *)item->key, - targetlist, - newvarlist, - newrelid); + /* Pull up RelabelType node too, unless tlist expr has right type. */ + if (IsA(item->key, RelabelType)) + { + RelabelType *oldrelabel = (RelabelType *)item->key; - /* Pull up RelabelType node too, unless tlist expr has right type. */ - if (IsA(item->key, RelabelType)) - { - RelabelType *oldrelabel = (RelabelType *)item->key; - - if (oldrelabel->resulttype != exprType((Node *)newexpr) || - oldrelabel->resulttypmod != exprTypmod((Node *)newexpr)) - newexpr = (Expr *)makeRelabelType(newexpr, - oldrelabel->resulttype, - oldrelabel->resulttypmod, - oldrelabel->relabelformat); - } + if (oldrelabel->resulttype != exprType((Node *)newexpr) || + oldrelabel->resulttypmod != exprTypmod((Node *)newexpr)) + newexpr = (Expr *)makeRelabelType(newexpr, + oldrelabel->resulttype, + oldrelabel->resulttypmod, + oldrelabel->relabelformat); } Insist(newexpr); Index: cdb-pg/src/backend/optimizer/util/pathnode.c =================================================================== RCS file: /data/FISHEYE_REPOSITORIES/greenplum/cvsroot/cdb2/cdb-pg/src/backend/optimiz er/util/pathnode.c,v diff -u -N -r1.52.2.4 -r1.52.2.5 --- cdb-pg/src/backend/optimizer/util/pathnode.c 5 Aug 2007 23:06:44 -0000 1.52.2.4 +++ cdb-pg/src/backend/optimizer/util/pathnode.c 10 Aug 2007 03:41:15 -0000 1.52.2.5 @@ -1563,7 +1563,15 @@ pathnode->path.rescannable = false; } - return pathnode; + /* + * CDB: If there is exactly one subpath, its ordering is preserved. + * Child rel's pathkey exprs are already expressed in terms of the + * columns of the parent appendrel. See find_usable_indexes(). + */ + if (list_length(subpaths) == 1) + pathnode->path.pathkeys = ((Path *)linitial(subpaths))->pathkeys; + + return pathnode; } /* On 8/24/07 3:38 AM, "Heikki Linnakangas" wrote: > Anton wrote: >>>> =# explain SELECT * FROM n_traf ORDER BY date_time DESC LIMIT 1; >>>> QUERY PLAN >>> ---------------------------------------------------------------------------- >>> ----------------------------- >>>> Limit (cost=824637.69..824637.69 rows=1 width=32) >>>> -> Sort (cost=824637.69..838746.44 rows=5643499 width=32) >>>> Sort Key: public.n_traf.date_time >>>> -> Result (cost=0.00..100877.99 rows=5643499 width=32) >>>> -> Append (cost= 0.00..100877.99 rows=5643499 width=32) >>>> -> Seq Scan on n_traf (cost=0.00..22.30 >>>> rows=1230 width=32) >>>> -> Seq Scan on n_traf_y2007m01 n_traf >>>> (cost=0.00..22.30 rows=1230 width=32) >> ... >>>> -> Seq Scan on n_traf_y2007m12 n_traf >>>> (cost=0.00..22.30 rows=1230 width=32) >>>> (18 rows) >>>> >>>> Why it no uses indexes at all? >>>> ------------------------------------------- >>> I'm no expert but I'd guess that the the planner doesn't know which >>> partition holds the latest time so it has to read them all. >> >> Agree. But why it not uses indexes when it reading them? > > The planner isn't smart enough to push the "ORDER BY ... LIMIT ..." > below the append node. Therefore it needs to fetch all rows from all the > tables, and the fastest way to do that is a seq scan.