Message-ID: <47BADE1C.10903@archonet.com>
Date: Tue, 19 Feb 2008 13:48:12 +0000
From: Richard Huxton <dev@archonet.com>
User-Agent: Thunderbird 2.0.0.9 (X11/20071031)
MIME-Version: 1.0
To: Stuart Brooks <stuartb@cat.co.za>
CC: pgsql-sql@postgresql.org
Subject: Re: Am I wasting my time with partitions?
References: <47BAB556.7060506@cat.co.za>
In-Reply-To: <47BAB556.7060506@cat.co.za>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Stuart Brooks wrote:
> It seems to me that postgresql doesn't use indexes when being asked for 
> an ordered result sets from a partitioned table. I have an application 
> where this is critical, but I was hoping to use partitions because of 
> the ease of rotating out old rows.

> metadb=> explain select * from l order by amount,lineitem_key limit 10;
>                                     QUERY PLAN
>                     ->  Seq Scan on l  (cost=0.00..16.90 rows=690 width=88)
>                     ->  Seq Scan on l1 l  (cost=0.00..4951.00 
> rows=250000 width=49)
>                     ->  Seq Scan on l2 l  (cost=0.00..5653.66 
> rows=285466 width=49)

> NB. Just addressing one of the inherited tables works fine.
> 
> metadb=> explain select * from l1 order by amount,lineitem_key limit 10;

Well, you don't have an index it can use to find the smallest 
(amount,lineitem) across all of lX. If PG was smart enough to figure out 
that it only needed to check l1, then you do. Unfortunately it isn't.

If you add the constraint you use to partition by, does that help you?

-- 
   Richard Huxton
   Archonet Ltd