Skip site navigation (1) Skip section navigation (2)

Re: partitioned table and ORDER BY indexed_field DESC LIMIT 1

From: Anton <anton200(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: partitioned table and ORDER BY indexed_field DESC LIMIT 1
Date: 2007-10-27 04:26:21
Message-ID: 8cac8dd0710262126t147f8974rb58a1e7c615e0725@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-performance
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(at)greenplum(dot)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(at)enterprisedb(dot)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

In response to

Responses

pgsql-performance by date

Next:From: Pablo AlcarazDate: 2007-10-27 05:14:21
Subject: Re: Speed difference between select ... union select ... and select from partitioned_table
Previous:From: Joshua D. DrakeDate: 2007-10-27 02:31:11
Subject: Re: Suggestions on an update query

pgsql-hackers by date

Next:From: Tom LaneDate: 2007-10-27 05:37:29
Subject: Re: partitioned table and ORDER BY indexed_field DESC LIMIT 1
Previous:From: Tom LaneDate: 2007-10-27 03:06:22
Subject: Re: URGENT HELP about 'duration' stats

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group