Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 59DEA2E003D for ; Tue, 26 Feb 2008 17:57:45 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 79509-09 for ; Tue, 26 Feb 2008 17:57:42 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 24FC32E0030 for ; Tue, 26 Feb 2008 17:57:42 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id m1QLvGuE008618; Tue, 26 Feb 2008 16:57:16 -0500 (EST) To: "Laurent Raufaste" cc: pgsql-performance@postgresql.org Subject: Re: PG planning randomly ? In-reply-to: <669dc9710802261205j42250581w9fcc8b2def694650@mail.gmail.com> References: <669dc9710802260719h61d846b3hd50b7f547bcb82c3@mail.gmail.com> <3637.1204043502@sss.pgh.pa.us> <669dc9710802260912y7fe58f9epe42fe7eb360119d8@mail.gmail.com> <5296.1204048779@sss.pgh.pa.us> <669dc9710802261205j42250581w9fcc8b2def694650@mail.gmail.com> Comments: In-reply-to "Laurent Raufaste" message dated "Tue, 26 Feb 2008 21:05:24 +0100" Date: Tue, 26 Feb 2008 16:57:16 -0500 Message-ID: <8617.1204063036@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200802/353 X-Sequence-Number: 28895 "Laurent Raufaste" writes: > 2008/2/26, Tom Lane : >> ... I'm wondering if you have a definition of operator <@ >> that doesn't specify the new selectivity estimator. Please try a >> pg_dump -s and see what it shows as the definition of <@. > Here's the first definition of the <@ operator in my dump: > CREATE OPERATOR <@ ( > PROCEDURE = ltree_risparent, > LEFTARG = ltree, > RIGHTARG = ltree, > COMMUTATOR = @>, > RESTRICT = ltreeparentsel, > JOIN = contjoinsel > ); That's the right RESTRICT function, but what exactly did you mean by "first definition"? Are there more? It may be that it's just not possible for the estimator to come up with accurate rowcount estimates given the amount of info it has available. The query you are complaining about confuses the issue quite a lot by involving other issues. Would you try just "explain analyze select 1 from _commment where path <@ '....';" for various typical path values, and see if it's coming up with estimated rowcounts that are in the right ballpark compared to the actual ones? regards, tom lane