From: Richard Huxton <dev@archonet.com>
To: Stuart Brooks <stuartb@cat.co.za>
Cc: pgsql-sql@postgresql.org
Subject: Re: Am I wasting my time with partitions?
Date: Tue, 19 Feb 2008 13:48:12 +0000
Message-ID: <47BADE1C.10903@archonet.com> (raw)
In-Reply-To: <47BAB556.7060506@cat.co.za>
References: <47BAB556.7060506@cat.co.za>
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
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-sql@postgresql.org
Cc: dev@archonet.com, stuartb@cat.co.za
Subject: Re: Am I wasting my time with partitions?
In-Reply-To: <47BADE1C.10903@archonet.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