From: Luke Lonergan <llonergan@greenplum.com>
To: Heikki Linnakangas <heikki@enterprisedb.com>
To: Anton <anton200@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: partitioned table and ORDER BY indexed_field DESC LIMIT 1
Date: Fri, 24 Aug 2007 09:20:28 -0700
Message-ID: <C2F4535C.3CA2B%llonergan@greenplum.com> (raw)
In-Reply-To: <46CEB522.9030907@enterprisedb.com>
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.
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: llonergan@greenplum.com, heikki@enterprisedb.com, anton200@gmail.com
Subject: Re: partitioned table and ORDER BY indexed_field DESC LIMIT 1
In-Reply-To: <C2F4535C.3CA2B%llonergan@greenplum.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