From: Mikko Partio <mpartio@gmail.com>
To: Anton <anton200@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: partitioned table and ORDER BY indexed_field DESC LIMIT 1
Date: Fri, 24 Aug 2007 13:24:31 +0300
Message-ID: <2ca799770708240324w155857e8m5911cbd5c6ff2307@mail.gmail.com> (raw)
In-Reply-To: <8cac8dd0708240153p4f3ef2d9xae912259926d860@mail.gmail.com>
References: <8cac8dd0708240153p4f3ef2d9xae912259926d860@mail.gmail.com>
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
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: mpartio@gmail.com, anton200@gmail.com
Subject: Re: partitioned table and ORDER BY indexed_field DESC LIMIT 1
In-Reply-To: <2ca799770708240324w155857e8m5911cbd5c6ff2307@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