To: "Mark Kirkwood" <markir@paradise.net.nz>
Cc: "Luke Lonergan" <llonergan@greenplum.com>,
	"Heikki Linnakangas" <heikki@enterprisedb.com>,
	"Anton" <anton200@gmail.com>,  <pgsql-performance@postgresql.org>
Subject: Re: partitioned table and ORDER BY indexed_field DESC LIMIT 1
In-Reply-To: <4732788A.6050405@paradise.net.nz> (Mark Kirkwood's message of
	"Thu\, 08 Nov 2007 15\:46\:34 +1300")
User-Agent: Gnus/5.11 (Gnus v5.11) Emacs/22.1 (gnu/linux)
References: <C34C09CA.43C59%llonergan@greenplum.com>
	<4726C52E.5020909@paradise.net.nz> <87zly1as4y.fsf@oxford.xeocode.com>
	<472EAEE6.2090000@paradise.net.nz> <87r6j46gd0.fsf@oxford.xeocode.com>
	<4732788A.6050405@paradise.net.nz>
From: Gregory Stark <stark@enterprisedb.com>
Organization: EnterpriseDB
Date: Thu, 08 Nov 2007 06:21:10 +0000
Message-ID: <87ode5uujt.fsf@oxford.xeocode.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

"Mark Kirkwood" <markir@paradise.net.nz> writes:

> Here is a (somewhat hurried) self-contained version of the patch under
> discussion. It applies to 8.2.5 and the resultant code compiles and runs. I've
> left in some unneeded parallel stuff (PathLocus struct), which I can weed out
> in a subsequent version if desired. I also removed the 'cdb ' from  most of the
> function names and (I  hope) any Greenplum copyrights.

Thanks, I'll take a look at it.

> I discovered that the patch solves a slightly different problem... it pulls up
> index scans as a viable path choice, (but not for the DESC case) but does not
> push down the LIMIT to the child tables ... so the actual performance
> improvement is zero - however hopefully the patch provides useful raw material
> to help.


> SET
> 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.