From: Tomas Tamosaitis <tomas.tamosaitis@connecty.lt>
To: pgsql-performance@postgresql.org
Subject: Re: partitioned table and ORDER BY indexed_field DESC LIMIT 1
Date: Mon, 27 Aug 2007 15:57:29 +0300
Message-ID: <1188219449.8210.4.camel@localhost.localdomain> (raw)
In-Reply-To: <8cac8dd0708240153p4f3ef2d9xae912259926d860@mail.gmail.com>
References: <8cac8dd0708240153p4f3ef2d9xae912259926d860@mail.gmail.com>
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
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: tomas.tamosaitis@connecty.lt
Subject: Re: partitioned table and ORDER BY indexed_field DESC LIMIT 1
In-Reply-To: <1188219449.8210.4.camel@localhost.localdomain>
* 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