Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 0073B2E00E2 for ; Tue, 26 Feb 2008 16:07:52 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 12937-01 for ; Tue, 26 Feb 2008 16:07:45 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from wx-out-0506.google.com (wx-out-0506.google.com [66.249.82.232]) by postgresql.org (Postfix) with ESMTP id CEAEF2E0043 for ; Tue, 26 Feb 2008 16:05:26 -0400 (AST) Received: by wx-out-0506.google.com with SMTP id r21so2191353wxc.28 for ; Tue, 26 Feb 2008 12:05:25 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:sender:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references:x-google-sender-auth; bh=Us7fxyvH9lvbq0AOLg8mmkKegd85rBmZPWYjQ4sOawM=; b=XfrU0s4XG7uaCMRp61F4hzjltU+dYYg+tkrCLAcvc4yOy6LEAFQaN0AmE7oWHv8oUwKsjvHGWHxbXFcBZw1+HwZmUIY4gSwPg2S5oETV5LxzH+3CAT1u+/LviLSwPMhyS4LbXOsiq71Y6sJ5LsWEQFiSbd+EZQSD4DsFwlbMAiE= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:sender:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references:x-google-sender-auth; b=U6W0TByImthfbbTCRwuH8mZ1H5hXqgSClXhCcCJWADlJJu34jZ4iOxrI5NMMJRmrFU8aM0GkFeM2p7bC3czfTROIeM94E4LzcLf3QK0FsA4v4VFZ0mV6KFjJzuJzBJvfa5BA1fqnuwbKVb0yPmgVE9pCr5hS/bUzs27eI/JU0E4= Received: by 10.65.132.13 with SMTP id j13mr10044656qbn.5.1204056324741; Tue, 26 Feb 2008 12:05:24 -0800 (PST) Received: by 10.65.38.3 with HTTP; Tue, 26 Feb 2008 12:05:24 -0800 (PST) Message-ID: <669dc9710802261205j42250581w9fcc8b2def694650@mail.gmail.com> Date: Tue, 26 Feb 2008 21:05:24 +0100 From: "Laurent Raufaste" To: "Tom Lane" Subject: Re: PG planning randomly ? Cc: pgsql-performance@postgresql.org In-Reply-To: <5296.1204048779@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <669dc9710802260719h61d846b3hd50b7f547bcb82c3@mail.gmail.com> <3637.1204043502@sss.pgh.pa.us> <669dc9710802260912y7fe58f9epe42fe7eb360119d8@mail.gmail.com> <5296.1204048779@sss.pgh.pa.us> X-Google-Sender-Auth: 4857b2f4688cc3c3 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200802/348 X-Sequence-Number: 28890 2008/2/26, Tom Lane : > "Laurent Raufaste" writes: > > > 2008/2/26, Tom Lane : > > >> If it's 8.2 or later then increasing the stats target for _comment.path > >> to 100 or more would likely help. > > > I'm using PG 8.2.4. > > We are using 100 as default_statistics_target by default and all our > > column are using this value: > > > Hmm, that ought to be enough to activate the better selectivity > estimator. > > Unless ... did you update this database from a pre-8.2 DB that already > had contrib/ltree in it? If so, did you just load the existing old > definition of ltree as part of your dump, or did you install 8.2's > version fresh? 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 <@. > > regards, tom lane > Here's the first definition of the <@ operator in my dump: -- -- Name: <@; Type: OPERATOR; Schema: public; Owner: postgres -- CREATE OPERATOR <@ ( PROCEDURE = ltree_risparent, LEFTARG = ltree, RIGHTARG = ltree, COMMUTATOR = @>, RESTRICT = ltreeparentsel, JOIN = contjoinsel ); ALTER OPERATOR public.<@ (ltree, ltree) OWNER TO postgres; Our data was created on an older PG (8.1.x) but we installed 8.2.x from scratch, only dumping the schema and the data in it. I used ltree found in the 8.2.4 source. Do you think an update of ltree, or better of the database will fix the problem ? We plan on upgrading to the 8.3 branch in the next weeks, but this behavior can't wait this much as our servers are overburned from time to time =( Thanks for your help ;) -- Laurent Raufaste