Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id C57A92E018E for ; Tue, 18 Dec 2007 18:46:33 -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 94403-07 for ; Tue, 18 Dec 2007 18:46:17 -0400 (AST) Received: from nz-out-0506.google.com (nz-out-0506.google.com [64.233.162.239]) by postgresql.org (Postfix) with ESMTP id EFA3F2E00BF for ; Tue, 18 Dec 2007 18:46:30 -0400 (AST) Received: by nz-out-0506.google.com with SMTP id l8so1303069nzf.28 for ; Tue, 18 Dec 2007 14:46:30 -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=8ZHtI9v+ONW3Y2dKdVPSEeiLkyv7nmXfS48oYJ2FwWY=; b=gNlldYgcMxQsq7VbEctNkIjWbH0fBguKnOMB17Jk/zA/+BW2Gjw7KRbbnbHJStx1sgP+L3OUdo2yGvfilRMlhLBXXXHDcXjlfhWrz4ZEf+eb4Aiu/sgjKjI+hteN8h5GH3m1NF4/qebUJlKz7pujE8FQqb0vpwNwu6SRHFlnJxs= 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=AQjtg1RGeP2FMlTAiP9l0kUeMeUNPEgaRfJ8t1n+BNHuJriaUKK7HsBbQQlnbT4n+yjncXI4z41hy9KrJscXY8GfekCC0ffrQxfDz5MCCxYuonEDH0mQy3lv+sBRmlWUnNl7demSDSsH7+0MdFdALCs0A9wbQRpjPP6josRPutY= Received: by 10.142.226.2 with SMTP id y2mr2022034wfg.46.1198017989348; Tue, 18 Dec 2007 14:46:29 -0800 (PST) Received: by 10.142.165.8 with HTTP; Tue, 18 Dec 2007 14:46:29 -0800 (PST) Message-ID: Date: Tue, 18 Dec 2007 16:46:29 -0600 From: "Scott Marlowe" To: "olivier.boissard@cerene.fr" Subject: Re: How to uses self query plan Cc: pgsql-admin@postgresql.org In-Reply-To: <476847DB.9090401@cerene.fr> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <476847DB.9090401@cerene.fr> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200712/175 X-Sequence-Number: 27692 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 and > 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?