Simon Riggs <simon@xxxxxxxxxxxxxxx> 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@xxxxxxxxxxxxxxx>: > > 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 ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
