User-Agent: Microsoft-Entourage/11.3.6.070618
Date: Fri, 24 Aug 2007 09:20:28 -0700
Subject: Re: partitioned table and ORDER BY indexed_field DESC
 LIMIT 1
From: "Luke Lonergan" <llonergan@greenplum.com>
To: "Heikki Linnakangas" <heikki@enterprisedb.com>,
	"Anton" <anton200@gmail.com>
cc: pgsql-performance@postgresql.org
Message-ID: <C2F4535C.3CA2B%llonergan@greenplum.com>
Thread-Topic: [PERFORM] partitioned table and ORDER BY indexed_field
	DESC LIMIT 1
Thread-Index: AcfmOwLg5KgGzyOtRWi3sUHduZNHdwAL6wYE
In-Reply-To: <46CEB522.9030907@enterprisedb.com>
MIME-Version: 1.0
Content-Type: text/plain;
 charset=us-ascii
Content-Transfer-Encoding: 7bit

We just fixed this - I'll post a patch, but I don't have time to verify
against HEAD.

- Luke


On 8/24/07 3:38 AM, "Heikki Linnakangas" <heikki@enterprisedb.com> 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.