pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed

From: Anton <anton200@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: partitioned table and ORDER BY indexed_field DESC LIMIT 1
Date: Fri, 24 Aug 2007 16:32:43 +0600
Message-ID: <8cac8dd0708240332p5f213616ldf71290e725f4593@mail.gmail.com> (raw)
In-Reply-To: <2ca799770708240324w155857e8m5911cbd5c6ff2307@mail.gmail.com>
References: <8cac8dd0708240153p4f3ef2d9xae912259926d860@mail.gmail.com>
	<2ca799770708240324w155857e8m5911cbd5c6ff2307@mail.gmail.com>

> > =# 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

view thread (33+ messages)  latest in thread

Message-ID: <8cac8dd0708240332p5f213616ldf71290e725f4593@mail.gmail.com>
Permalink:  ../8cac8dd0708240332p5f213616ldf71290e725f4593@mail.gmail.com/
Also on:    postgresql.org/message-id/8cac8dd0708240332p5f213616ldf71290e725f4593@mail.gmail.com

copy link · copy postgr.es

reply

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: anton200@gmail.com
  Subject: Re: partitioned table and ORDER BY indexed_field DESC LIMIT 1
  In-Reply-To: <8cac8dd0708240332p5f213616ldf71290e725f4593@mail.gmail.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