Re: Query planner unaware of possibly best plan

Simon Riggs <simon(at)2ndquadrant(dot)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(at)2ndquadrant(dot)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