Subject: Re: partitioned table and ORDER BY indexed_field DESC
	LIMIT 1
From: Tomas Tamosaitis <tomas.tamosaitis@connecty.lt>
To: pgsql-performance@postgresql.org
In-Reply-To: <8cac8dd0708240153p4f3ef2d9xae912259926d860@mail.gmail.com>
References: <8cac8dd0708240153p4f3ef2d9xae912259926d860@mail.gmail.com>
Content-Type: text/plain; charset=utf-8
Date: Mon, 27 Aug 2007 15:57:29 +0300
Message-Id: <1188219449.8210.4.camel@localhost.localdomain>
Mime-Version: 1.0
Content-Transfer-Encoding: 8bit

Pn, 2007 08 24 14:53 +0600, Anton rašė:
> Hi.
> 
> I just created partitioned table, n_traf, sliced by month
> (n_traf_y2007m01, n_traf_y2007m02... and so on, see below). They are
> indexed by 'date_time' column.
> Then I populate it (last value have date 2007-08-...) and do VACUUM
> ANALYZE ON n_traf_y2007... all of it.
> 
> Now I try to select latest value (ORDER BY date_time LIMIT 1), but
> Postgres produced the ugly plan:
> 
> =# explain SELECT * FROM n_traf ORDER BY date_time DESC LIMIT 1;
>                                                QUERY PL 

can you test performance and send explain results of select like this :
select * from n_traf where date_time = (select max(date_time) from
n_traf);

i have similar problem with ~70M rows table (then using ordering), but
my table not partitioned. 
I`m interesting how this select will works on partitioned table.  


-- 
Pagarbiai, 
Tomas Tamošaitis
Projektų Vadovas
Connecty 
Skype://mazgis1009?add 
Mob: +370 652 86127 
e-pastas: tomas.tamosaitis@connecty.lt
web: www.connecty.lt