Received: from localhost (unknown [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 1CD982E376B for ; Wed, 19 Dec 2007 11:33:15 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 55709-02 for ; Wed, 19 Dec 2007 11:32:59 -0400 (AST) Received: from wr-out-0506.google.com (wr-out-0506.google.com [64.233.184.228]) by postgresql.org (Postfix) with ESMTP id 837CE2E2D4D for ; Wed, 19 Dec 2007 11:33:10 -0400 (AST) Received: by wr-out-0506.google.com with SMTP id 58so2357481wri.10 for ; Wed, 19 Dec 2007 07:33:08 -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:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; bh=K6sJpneNFHUeDvXKtWo80d2j3M8fiGeYRTqK5lZ0U0k=; b=GZeomvMTEvL8ZIpgF94640uL3SdVZm4AETQzNYQ3/egI6ZfdgvlXbU+4o5XxnDw8swrXnzmlUfR8/Nbqag7IGiLGQc2Nxvz+jMdTc4mxEfRbC4ZzSCcNnNeX8ZRb2HlDAGBjc6Z3wdd9sxZJdgBZKTXxpmIlnfVU9LPm3/29F2M= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=eShS23MTRMQjQHo04xAMivO5ht4LCRC3u1DAwgdfSbnutfS2KL9r+sLShatSwat5H6zYwT1dnLJngD7Z3CkOICnVzszWo2XMbLErf5c2dBXWbZ820gdRVlP+lHe5HsCEXZFTcEoE2dLp+rbipvRKNkhDIJzVLcftx7QqeKSrUPA= Received: by 10.142.252.11 with SMTP id z11mr1596704wfh.50.1198078386511; Wed, 19 Dec 2007 07:33:06 -0800 (PST) Received: by 10.142.143.6 with HTTP; Wed, 19 Dec 2007 07:33:06 -0800 (PST) Message-ID: Date: Wed, 19 Dec 2007 09:33:06 -0600 From: "Scott Marlowe" To: "olivier boissard" Subject: Re: How to uses self query plan Cc: pgsql-admin@postgresql.org In-Reply-To: <4768CEEF.9060506@cerene.fr> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline References: <476847DB.9090401@cerene.fr> <4768CEEF.9060506@cerene.fr> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200712/177 X-Sequence-Number: 27694 On Dec 19, 2007 1:57 AM, olivier boissard wrot= e: > Scott Marlowe a =E9crit : > > > On Dec 18, 2007 4:21 PM, olivier.boissard@cerene.fr > > wrote: > > > >> Hello, > >> > >> My purpose is to uses different query plan depending on queries > >> I got the following problem : some queries are taking too much time an= d > >> postgresql does not take the most relevant index in account. > >> I search on internet and found that no HINT function was available in > >> postgresl. > >> I tried to change indexes parameters in postgresql.conf but eache time= I > >> set a param to OFF (example nested_loop ,seqscan, ...) , some queries > >> freeze database . > >> > >> How can we force postgresql to use a plan ? > >> > > > > That's not how we do things in postgresql land (usually). > > > > You should figure out WHY your queries are picking the wrong plan, and > > then see if you can get them to pick the right ones. If it's a query > > planner bug, you report it here, or the perform or general lists, and > > it gets fixed. Generic hints aren't likely to happen any time soon, > > although I do believe 8.3 is introducing function costing of some > > kind, which seems like a useful idea. > > > > But, back to fixing your slow queries. > > > > 1: Increase statistics targets on the guilty columns and reanalyze. > > 2: Run explain analyze select.... and post the output here. > > 3: ??? > > 4: profit? > > > Thanks for reply > > I will extract some queries. > I want to precise that I use postgresql 8.1.0 . First step would be to update to the latest 8.1.x version, whatever that is. There have been a LOT of fixes in the 8.1 branch since 8.1.0 came out, and one might be a fix for your query problem. minor updates are strictly bug and security fixes, so they're not likely to cause any problems.