From: Marc Cousin <mcousin@sigma.fr>
To: pgsql-performance@postgresql.org
Subject: Re: partitioning
Date: Tue, 13 Dec 2005 14:11:24 +0100
Message-ID: <200512131411.24825.mcousin@sigma.fr> (raw)
In-Reply-To: <5e744e3d0512130350l12d136abx2ad661c6d6b6a21e@mail.gmail.com>
References: <5e744e3d0512130350l12d136abx2ad661c6d6b6a21e@mail.gmail.com>
Yes, that's how I solved it... and I totally agree that it's hard for the
planner to guess what to do on the partitions. But maybe there should be
something in the docs explaining the limitations ...
I'm only asking for the biggest 100 ids from the table, so I thought maybe the
planner would take the 100 biggest from all partitions or something like that
and return me the 100 biggest from those results. It didn't and that's quite
logical.
What I meant is that I understand why the planner chooses this plan, but maybe
it should be written somewhere in the docs that some plans will be worse
after partitionning.
Le Mardi 13 Décembre 2005 12:50, vous avez écrit :
> I just saw that there is no where clause in the query, that you had
> fed to explain plan.
> you need to include a where clause based on id_machine column to see the
> effect.
>
> On 12/13/05, Pandurangan R S <pandurangan.r.s@gmail.com> wrote:
> > Did you set constraint_exclusion = true in postgresql.conf file?
> >
> > On 12/13/05, Marc Cousin <mcousin@sigma.fr> wrote:
> > > Hi,
> > >
> > > I've been working on trying to partition a big table (I've never
> > > partitioned a table in any other database till now).
> > > Everything went ok, except one query that didn't work afterwards.
> > >
> > > I've put the partition description, indexes, etc ..., and the explain
> > > plan attached.
> > >
> > > The query is extremely fast without partition (index scan backards on
> > > the primary key)
> > >
> > > The query is : "select * from logs order by id desc limit 100;"
> > > id is the primary key.
> > >
> > > It is indexed on all partitions.
> > >
> > > But the explain plan does full table scan on all partitions.
> > >
> > > While I think I understand why it is doing this plan right now, is
> > > there something that could be done to optimize this case ? Or put a
> > > warning in the docs about this kind of behaviour. I guess normally
> > > someone would partition to get faster queries :)
> > >
> > > Anyway, I thought I should mention this, as it has been quite a
> > > surprise.
> > >
> > >
> > >
> > > ---------------------------(end of
> > > broadcast)--------------------------- TIP 1: if posting/reading through
> > > Usenet, please send an appropriate subscribe-nomail command to
> > > majordomo@postgresql.org so that your message can get through to the
> > > mailing list cleanly
> >
> > --
> > Regards
> > Pandu
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: mcousin@sigma.fr
Subject: Re: partitioning
In-Reply-To: <200512131411.24825.mcousin@sigma.fr>
* 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