Received: from localhost (unknown [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id E0E399FB517 for ; Fri, 21 Sep 2007 16:30:04 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 90276-09 for ; Fri, 21 Sep 2007 16:29:55 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from fmx12.freemail.hu (fmx12.freemail.hu [195.228.245.62]) by postgresql.org (Postfix) with SMTP id 5E8AA9FB512 for ; Fri, 21 Sep 2007 16:29:57 -0300 (ADT) Received: (qmail 97732 invoked from network); 21 Sep 2007 21:29:55 +0200 Received: from fm03.freemail.hu (195.228.245.103) by fmx12.freemail.hu with SMTP; 21 Sep 2007 21:29:55 +0200 Received: (qmail 15132 invoked by uid 1909201); 21 Sep 2007 21:29:55 +0200 Date: Fri, 21 Sep 2007 21:29:55 +0200 (CEST) From: Denes Daniel Subject: Re: Query planner unaware of possibly best plan To: pgsql-performance@postgresql.org In-Reply-To: <1190396973.4202.116.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/384 X-Sequence-Number: 26929 Simon Riggs =EDrta: > Ordering by parent, child is fairly common but the variation you've got > here isn't that common. You'd need to make a case considering all the > alternatives; nobody will agree without a balanced case that includes > what is best for everyone. >=20 > Your EXPLAIN looks edited. Have you also edited the sort costs? They > look slightly higher than we might expect. Please provide the full > normal EXPLAIN output. >=20 > --=20 > Simon Riggs > 2ndQuadrant http://www.2ndQuadrant.com I've just inserted some newlines, so it's better to read than when my=20 email-client wraps the lines automatically. Did not touch the information= =20 itself. But here is the normal output of EXPLAIN ANALYZE: EXPLAIN ANALYZE SELECT * FROM tparent JOIN tchild ON tchild.par_id =3D=20 tparent.id WHERE tparent.ord BETWEEN 1 AND 4 ORDER BY tparent.ord,=20 tchild.ord; QUERY PLAN ---------------------------------------------------------------------------= ---------------- -------------------------------------------- Sort (cost=3D100000132.10..100000140.10 rows=3D8 width=3D16) (actual=20 time=3D0.302..0.319 rows=3D9 loops=3D1) Sort Key: tparent.ord, tchild.ord -> Nested Loop (cost=3D0.00..84.10 rows=3D8 width=3D16) (actual=20 time=3D0.181..0.267 rows=3D9 loops=3D1) -> Index Scan using par_uniq_ord on tparent (cost=3D0.00..20.40= =20 rows=3D4 width=3D8) (actual time=3D0.100..0.109 rows=3D4 loops=3D1) Index Cond: ((ord >=3D 1) AND (ord <=3D 4)) -> Index Scan using chi_pkey_parid_ord on tchild =20 (cost=3D0.00..9.93 rows=3D2 width=3D8) (actual time=3D0.020..0.026 rows=3D2= =20 loops=3D4) Index Cond: (tchild.par_id =3D "outer".id) Total runtime: 0.412 ms (8 rows) The costs may be different because I've tuned the query planner's=20 parameters. > 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=20 unique key of parent, then something in child? (Except for=20 child.parent_id, child.something because this has all the information in=20 child and can rely on a single multicolumn index.) Denes Daniel ------------------------------------------------------------------------ =0A=0AOlvasd az [origo]-t a mobilodon: mini magazinok a Mobizin-en ___________________________________________________ www.t-mobile.hu/mobizin