From: Marc Cousin <mcousin@sigma.fr>
Organization: Sigma Informatique
To: pgsql-performance@postgresql.org
Subject: Re: partitioning
Date: Tue, 13 Dec 2005 14:11:24 +0100
User-Agent: KMail/1.9
References: <5e744e3d0512130350l12d136abx2ad661c6d6b6a21e@mail.gmail.com>
In-Reply-To: <5e744e3d0512130350l12d136abx2ad661c6d6b6a21e@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="utf-8"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
Message-Id: <200512131411.24825.mcousin@sigma.fr>

Yes, that's how I solved it... and I totally agree that it's hard for the=20
planner to guess what to do on the partitions. But maybe there should be=20
something in the docs explaining the limitations ...

I'm only asking for the biggest 100 ids from the table, so I thought maybe =
the=20
planner would take the 100 biggest from all partitions or something like th=
at=20
and return me the 100 biggest from those results. It didn't and that's quit=
e=20
logical.

What I meant is that I understand why the planner chooses this plan, but ma=
ybe=20
it should be written somewhere in the docs that some plans will be worse=20
after partitionning.

Le Mardi 13 D=C3=A9cembre 2005 12:50, vous avez =C3=A9crit=C2=A0:
> 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 =3D 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 throu=
gh
> > > 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