Re: partitioned table and ORDER BY indexed_field DESC LIMIT 1

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