Skip site navigation (1) Skip section navigation (2)

Re: partitioned table and ORDER BY indexed_field DESC LIMIT 1

From: Anton <anton200(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: partitioned table and ORDER BY indexed_field DESC LIMIT 1
Date: 2007-08-24 10:32:43
Message-ID: 8cac8dd0708240332p5f213616ldf71290e725f4593@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-performance
> > =# 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?

-- 
engineer

In response to

Responses

pgsql-performance by date

Next:From: Heikki LinnakangasDate: 2007-08-24 10:38:26
Subject: Re: partitioned table and ORDER BY indexed_field DESC LIMIT 1
Previous:From: Mikko PartioDate: 2007-08-24 10:24:31
Subject: Re: partitioned table and ORDER BY indexed_field DESC LIMIT 1

pgsql-hackers by date

Next:From: Heikki LinnakangasDate: 2007-08-24 10:38:26
Subject: Re: partitioned table and ORDER BY indexed_field DESC LIMIT 1
Previous:From: Mikko PartioDate: 2007-08-24 10:24:31
Subject: Re: partitioned table and ORDER BY indexed_field DESC LIMIT 1

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group