Received: from localhost (unknown [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 4C32A9F9258 for ; Thu, 8 Nov 2007 03:19:07 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 31728-05 for ; Thu, 8 Nov 2007 03:18:49 -0400 (AST) Received: from smtp5.clear.net.nz (smtp5.clear.net.nz [203.97.33.68]) by postgresql.org (Postfix) with ESMTP id B3C4A9F9255 for ; Thu, 8 Nov 2007 03:19:01 -0400 (AST) Received: from zmori.markir.net (121-72-86-151.dsl.telstraclear.net [121.72.86.151]) by smtp5.clear.net.nz (CLEAR Net Mail) with ESMTP id <0JR600J3HEZNK640@smtp5.clear.net.nz> for pgsql-performance@postgresql.org; Thu, 08 Nov 2007 20:19:00 +1300 (NZDT) Date: Thu, 08 Nov 2007 20:18:47 +1300 From: Mark Kirkwood Subject: Re: partitioned table and ORDER BY indexed_field DESC LIMIT 1 In-reply-to: To: Luke Lonergan Cc: Gregory Stark , Heikki Linnakangas , Anton , pgsql-performance@postgresql.org Message-id: <4732B857.6070102@paradise.net.nz> MIME-version: 1.0 Content-type: text/plain; charset=ISO-8859-1; format=flowed Content-transfer-encoding: 7bit References: User-Agent: Thunderbird 2.0.0.6 (X11/20071025) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200711/91 X-Sequence-Number: 27506 Luke Lonergan wrote: > 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. > > Unfortunately our plan in GPDB looks exactly the same in this case - so we have a bit of work to do as well! Initially I wondered if I have got something wrong in the patch... and checked on GPDB - only to see the same behaviour! (see prev comment about LIMIT). Cheers Mark