Received: from localhost (unknown [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 26CB99FB378 for ; Fri, 21 Sep 2007 21:08:53 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 48529-02 for ; Fri, 21 Sep 2007 21:08:41 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from fmx25.freemail.hu (fmx25.freemail.hu [195.228.245.75]) by postgresql.org (Postfix) with SMTP id DC3DB9FB35F for ; Fri, 21 Sep 2007 21:08:45 -0300 (ADT) Received: (qmail 88676 invoked from network); 22 Sep 2007 02:08:43 +0200 Received: from fm03.freemail.hu (195.228.245.103) by fmx25.freemail.hu with SMTP; 22 Sep 2007 02:08:43 +0200 Received: (qmail 13437 invoked by uid 1909201); 22 Sep 2007 02:08:43 +0200 Date: Sat, 22 Sep 2007 02:08:43 +0200 (CEST) From: Denes Daniel Subject: Re: Query planner unaware of possibly best plan To: pgsql-performance@postgresql.org In-Reply-To: <1190405239.4202.131.camel@ebony.site> Message-ID: X-Originating-IP: [193.226.224.189] X-HTTP-User-Agent: Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; .NET CLR 1.1.4322; .NET CLR 2.0.50727) MIME-Version: 1.0 Content-Type: TEXT/PLAIN; CHARSET=ISO-8859-2 Content-Transfer-Encoding: QUOTED-PRINTABLE X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200709/394 X-Sequence-Number: 26939 Simon Riggs wrote: > On Fri, 2007-09-21 at 21:20 +0200, D=E1niel D=E9nes wrote: >=20 > > The costs may be different because I've tuned the query planner's=20 > > parameters. >=20 > OK, understood. >=20 > > > 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=20 > > child.parent_id, child.something because this has all the > > information in child and can rely on a single multicolumn index.) >=20 > Why "except"? Whats wrong with ordering that way?=20 >=20 > Make the case. **I** want it is not sufficient... >=20 > --=20 > Simon Riggs > 2ndQuadrant http://www.2ndQuadrant.com In reply to Simon Riggs : > > How else can you order by parent, child other than first ordering by > > a unique key of parent, then something in child? (Except for=20 > > child.parent_id, child.something because this has all the > > information in child and can rely on a single multicolumn index.) >=20 > Why "except"? Whats wrong with ordering that way? Well, nothing, but what if I have to order by some other unique key? Of=20 course I could do that by redundantly storing the parent's data in child=20 and then creating a multicolumn index, but... Just to see clear: when I found this, I was trying to make a slightly=20 different query. It was like: SELECT * FROM tparent JOIN tchild ON tchild.par_id =3D tparent.id WHERE tparent.uniqcol1 =3D 123 ORDER BY tparent.uniqcol2, tchild.ord; where there was a unique index on (tparent.uniqcol1, tparent.uniqcol2)=20 and the columns are marked NOT NULL. I expected a plan like doing an index scan on parent.uniqcol2 where=20 uniqcol1 =3D 123, and (using a nestloop and child's pkey) joining in the=20 children in the correct order (without a sort). But I got something else,= =20 so I tried everything to get what I wanted -- just to see the costs why=20 the planner chose something else. After some time I found out that=20 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= =20 & simple, I thought I'd make a test case, that's easy to follow, and=20 illustrates the problem: that the planner doesn't even consider my=20 plan. If it did, I think that'd be the one that gets executed. But tell me = if=20 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= =20 clarify so I can do what you ask me to. Denes Daniel ----------------------------------------------------- =0A=0AOlvasd az [origo]-t a mobilodon: mini magazinok a Mobizin-en ___________________________________________________ www.t-mobile.hu/mobizin