From: Gregory Stark <stark@enterprisedb.com>
To: Mark Kirkwood <markir@paradise.net.nz>
Cc: Luke Lonergan <llonergan@greenplum.com>
Cc: Heikki Linnakangas <heikki@enterprisedb.com>
Cc: Anton <anton200@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: partitioned table and ORDER BY indexed_field DESC LIMIT 1
Date: Thu, 08 Nov 2007 06:21:10 +0000
Message-ID: <87ode5uujt.fsf@oxford.xeocode.com> (raw)
In-Reply-To: <4732788A.6050405@paradise.net.nz>
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>
"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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: stark@enterprisedb.com, markir@paradise.net.nz, llonergan@greenplum.com, heikki@enterprisedb.com, anton200@gmail.com
Subject: Re: partitioned table and ORDER BY indexed_field DESC LIMIT 1
In-Reply-To: <87ode5uujt.fsf@oxford.xeocode.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox