Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 6D84D9F9A02 for ; Fri, 24 Aug 2007 07:24:37 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 32365-03 for ; Fri, 24 Aug 2007 07:24:30 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from wx-out-0506.google.com (wx-out-0506.google.com [66.249.82.231]) by postgresql.org (Postfix) with ESMTP id A190E9F9A01 for ; Fri, 24 Aug 2007 07:24:33 -0300 (ADT) Received: by wx-out-0506.google.com with SMTP id r21so703513wxc for ; Fri, 24 Aug 2007 03:24:32 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=IZ/8WrsmTV4tyCkADLddHRfWnDqYYxWJzvn8UQwT32rAgzp62yU3jfWlocNx79/YGY2P+7KWTH7G65qoe3LfTQLyzDl8aDAxJbPRKJGnRke5HqfnEtOwlUahPBNgCrLbEAazqfZ5m90TaF+oX4Y+xFKbWtiYRYH9jXUqJuIemUg= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=HHhUQT4KEtHFIuBoCw7p6j3Vrn1ka6AMUIZoiBicgvjgPqY3pnJ9CIfN2dpXtaJl9lgC7Yq1OgCc94LqKH4ldwS+OU4Sn6cLKBsVGa1Vn6Aiby0iljqFW/UfViuASGBfoZlDO0et2xuB/5pqlEXFdsiBlhzMOkjgFjtQjvAF9Ps= Received: by 10.70.69.2 with SMTP id r2mr4752494wxa.1187951071520; Fri, 24 Aug 2007 03:24:31 -0700 (PDT) Received: by 10.70.56.5 with HTTP; Fri, 24 Aug 2007 03:24:31 -0700 (PDT) Message-ID: <2ca799770708240324w155857e8m5911cbd5c6ff2307@mail.gmail.com> Date: Fri, 24 Aug 2007 13:24:31 +0300 From: "Mikko Partio" To: Anton Subject: Re: partitioned table and ORDER BY indexed_field DESC LIMIT 1 Cc: pgsql-performance@postgresql.org In-Reply-To: <8cac8dd0708240153p4f3ef2d9xae912259926d860@mail.gmail.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_134149_26363018.1187951071471" References: <8cac8dd0708240153p4f3ef2d9xae912259926d860@mail.gmail.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200708/282 X-Sequence-Number: 26412 ------=_Part_134149_26363018.1187951071471 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline On 8/24/07, Anton wrote: > > 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 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_y2007m02 n_traf > (cost=0.00..22.30 rows=1230 width=32) > -> Seq Scan on n_traf_y2007m03 n_traf > (cost=0.00..22.30 rows=1230 width=32) > -> Seq Scan on n_traf_y2007m04 n_traf > (cost=0.00..1.01 rows=1 width=32) > -> Seq Scan on n_traf_y2007m05 n_traf > (cost=0.00..9110.89 rows=509689 width=32) > -> Seq Scan on n_traf_y2007m06 n_traf > (cost=0.00..32003.89 rows=1790489 width=32) > -> Seq Scan on n_traf_y2007m07 n_traf > (cost=0.00..33881.10 rows=1895510 width=32) > -> Seq Scan on n_traf_y2007m08 n_traf > (cost=0.00..25702.70 rows=1437970 width=32) > -> Seq Scan on n_traf_y2007m09 n_traf > (cost=0.00..22.30 rows=1230 width=32) > -> Seq Scan on n_traf_y2007m10 n_traf > (cost=0.00..22.30 rows=1230 width=32) > -> Seq Scan on n_traf_y2007m11 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. Regards MP ------=_Part_134149_26363018.1187951071471 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline

On 8/24/07, Anton <anton200@gmail.com> wrote:
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 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_y2007m02 n_traf
(cost=0.00..22.30 rows=1230 width=32)
                     ->  Seq Scan on n_traf_y2007m03 n_traf
(cost=0.00..22.30 rows=1230 width=32)
                     ->  Seq Scan on n_traf_y2007m04 n_traf
(cost=0.00..1.01 rows=1 width=32)
                     ->  Seq Scan on n_traf_y2007m05 n_traf
(cost=0.00..9110.89 rows=509689 width=32)
                     ->  Seq Scan on n_traf_y2007m06 n_traf
(cost= 0.00..32003.89 rows=1790489 width=32)
                     ->  Seq Scan on n_traf_y2007m07 n_traf
(cost=0.00..33881.10 rows=1895510 width=32)
                     ->  Seq Scan on n_traf_y2007m08 n_traf
(cost= 0.00..25702.70 rows=1437970 width=32)
                     ->  Seq Scan on n_traf_y2007m09 n_traf
(cost=0.00..22.30 rows=1230 width=32)
                     ->  Seq Scan on n_traf_y2007m10 n_traf
(cost=0.00..22.30 rows=1230 width=32)
                     ->  Seq Scan on n_traf_y2007m11 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.

Regards

MP
------=_Part_134149_26363018.1187951071471--