From: 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 11:38:26 +0100
Message-ID: <46CEB522.9030907@enterprisedb.com> (raw)
In-Reply-To: <8cac8dd0708240332p5f213616ldf71290e725f4593@mail.gmail.com>
References: <8cac8dd0708240153p4f3ef2d9xae912259926d860@mail.gmail.com>
<2ca799770708240324w155857e8m5911cbd5c6ff2307@mail.gmail.com>
<8cac8dd0708240332p5f213616ldf71290e725f4593@mail.gmail.com>
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.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
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: heikki@enterprisedb.com, anton200@gmail.com
Subject: Re: partitioned table and ORDER BY indexed_field DESC LIMIT 1
In-Reply-To: <46CEB522.9030907@enterprisedb.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