Received: from localhost (unknown [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 887C29FCCBE for ; Thu, 8 Nov 2007 02:41:21 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 42616-01 for ; Thu, 8 Nov 2007 02:41:21 -0400 (AST) Received: from chip2og50.obsmtp.com (chip2og50.obsmtp.com [64.18.13.37]) by postgresql.org (Postfix) with SMTP id 473DD9FCCE4 for ; Thu, 8 Nov 2007 02:41:20 -0400 (AST) Received: from source ([63.240.6.47]) by chip2ob50.postini.com ([64.18.5.12]) with SMTP; Wed, 07 Nov 2007 22:41:16 PST Received: from 172.16.1.25 by mail.Mi8.com with ESMTP (- Welcome to Mi8 Corporation www.Mi8.com (D1)); Thu, 08 Nov 2007 01:42:23 -0500 X-Server-Uuid: 0764DD54-627B-4C45-B37D-789FBDEDA467 Received: from MI8NYCMAIL08.Mi8.com ([172.16.1.164]) by D01HOST03.Mi8.com with Microsoft SMTPSVC(6.0.3790.1830); Thu, 8 Nov 2007 01:42:22 -0500 Received: from 69.181.101.67 ([69.181.101.67]) by MI8NYCMAIL08.Mi8.com ( [172.16.1.190]) via Exchange Front-End Server mi8owa.mi8.com ( [172.16.1.106]) with Microsoft Exchange Server HTTP-DAV ; Thu, 8 Nov 2007 06:41:36 +0000 User-Agent: Microsoft-Entourage/11.3.6.070618 Date: Wed, 07 Nov 2007 22:40:20 -0800 Subject: Re: partitioned table and ORDER BY indexed_field DESC LIMIT 1 From: "Luke Lonergan" To: "Gregory Stark" , "Mark Kirkwood" cc: "Heikki Linnakangas" , "Anton" , pgsql-performance@postgresql.org Message-ID: Thread-Topic: [PERFORM] partitioned table and ORDER BY indexed_field DESC LIMIT 1 Thread-Index: Acgh0jsxebwEso3FEdy/MQAbY6InMA== In-Reply-To: <87ode5uujt.fsf@oxford.xeocode.com> MIME-Version: 1.0 X-OriginalArrivalTime: 08 Nov 2007 06:42:22.0113 (UTC) FILETIME=[83FA5910:01C821D2] X-WSS-ID: 6B2C70441KO615951-01-01 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200711/89 X-Sequence-Number: 27504 On 11/7/07 10:21 PM, "Gregory Stark" wrote: >> part=# explain SELECT * FROM n_traf ORDER BY date_time LIMIT 1; >> QUERY PLAN >> ----------------------------------------------------------------------------- >> -------------------------------------------------------------------- >> Limit (cost=198367.14..198367.15 rows=1 width=20) >> -> Sort (cost=198367.14..200870.92 rows=1001510 width=20) >> Sort Key: public.n_traf.date_time >> -> Result (cost=0.00..57464.92 rows=1001510 width=20) >> -> Append (cost=0.00..57464.92 rows=1001510 width=20) >> -> Index Scan using n_traf_date_time_login_id on n_traf >> (cost=0.00..66.90 rows=1510 width=20) > > That looks suspicious. There's likely no good reason to be using the index > scan unless it avoids the sort node above the Append node. That's what I hope > to do by having the Append executor code do what's necessary to maintain the > order. Yah - the way it works in GPDB is that you get a non-sorting plan with an index scan below the parent - that was the point of the fix. Hmm. - Luke