From: Anton <anton200@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: partitioned table and ORDER BY indexed_field DESC LIMIT 1
Date: Sat, 27 Oct 2007 10:26:21 +0600
Message-ID: <8cac8dd0710262126t147f8974rb58a1e7c615e0725@mail.gmail.com> (raw)
In-Reply-To: <C2F454A1.3CA32%llonergan@greenplum.com>
References: <46CEB522.9030907@enterprisedb.com>
<C2F454A1.3CA32%llonergan@greenplum.com>
I want ask about problem with partioned tables (it was discussed some
time ago, see below). Is it fixed somehow in 8.2.5 ?
2007/8/24, Luke Lonergan <llonergan@greenplum.com>:
> Below is a patch against 8.2.4 (more or less), Heikki can you take a look at
> it?
>
> This enables the use of index scan of a child table by recognizing sort
> order of the append node. Kurt Harriman did the work.
...
>
> On 8/24/07 3:38 AM, "Heikki Linnakangas" <heikki@enterprisedb.com> wrote:
>
> > Anton wrote:
> >>>> =# 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?
> >
> > The planner isn't smart enough to push the "ORDER BY ... LIMIT ..."
> > below the append node. Therefore it needs to fetch all rows from all the
> > tables, and the fastest way to do that is a seq scan.
--
engineer
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: <8cac8dd0710262126t147f8974rb58a1e7c615e0725@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