From: Denes Daniel <panther-d@freemail.hu>
To: pgsql-performance@postgresql.org
Subject: Re: Query planner unaware of possibly best plan
Date: Sat, 22 Sep 2007 02:08:43 +0200 (CEST)
Message-ID: <freemail.20070822020843.13431@fm03.freemail.hu> (raw)
In-Reply-To: <1190405239.4202.131.camel@ebony.site>
Simon Riggs <simon@2ndquadrant.com> wrote:
> On Fri, 2007-09-21 at 21:20 +0200, Dániel Dénes wrote:
>
> > The costs may be different because I've tuned the query planner's
> > parameters.
>
> OK, understood.
>
> > > Ordering by parent, child is fairly common but the variation you've
> > > got here isn't that common.
> > How else can you order by parent, child other than first ordering by
> > a unique key of parent, then something in child? (Except for
> > child.parent_id, child.something because this has all the
> > information in child and can rely on a single multicolumn index.)
>
> Why "except"? Whats wrong with ordering that way?
>
> Make the case. **I** want it is not sufficient...
>
> --
> Simon Riggs
> 2ndQuadrant http://www.2ndQuadrant.com
In reply to Simon Riggs <simon@2ndquadrant.com>:
> > How else can you order by parent, child other than first ordering by
> > a unique key of parent, then something in child? (Except for
> > child.parent_id, child.something because this has all the
> > information in child and can rely on a single multicolumn index.)
>
> Why "except"? Whats wrong with ordering that way?
Well, nothing, but what if I have to order by some other unique key? Of
course I could do that by redundantly storing the parent's data in child
and then creating a multicolumn index, but...
Just to see clear: when I found this, I was trying to make a slightly
different query. It was like:
SELECT *
FROM tparent JOIN tchild ON tchild.par_id = tparent.id
WHERE tparent.uniqcol1 = 123
ORDER BY tparent.uniqcol2, tchild.ord;
where there was a unique index on (tparent.uniqcol1, tparent.uniqcol2)
and the columns are marked NOT NULL.
I expected a plan like doing an index scan on parent.uniqcol2 where
uniqcol1 = 123, and (using a nestloop and child's pkey) joining in the
children in the correct order (without a sort). But I got something else,
so I tried everything to get what I wanted -- just to see the costs why
the planner chose something else. After some time I found out that
there is no such plan, so no matter what I do it will sort...
So that's how I got here. But since the original problem isn't that clean
& simple, I thought I'd make a test case, that's easy to follow, and
illustrates the problem: that the planner doesn't even consider my
plan. If it did, I think that'd be the one that gets executed. But tell me if
I'm wrong somewhere.
> Make the case. **I** want it is not sufficient...
Sorry, I can't understand that... I'm far from perfect in english. Please
clarify so I can do what you ask me to.
Denes Daniel
-----------------------------------------------------
Olvasd az [origo]-t a mobilodon: mini magazinok a Mobizin-en
___________________________________________________
www.t-mobile.hu/mobizin
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: panther-d@freemail.hu
Subject: Re: Query planner unaware of possibly best plan
In-Reply-To: <freemail.20070822020843.13431@fm03.freemail.hu>
References: <1190405239.4202.131.camel@ebony.site> <freemail.20070822020843.13431@fm03.freemail.hu>
* 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