Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id CB4122E379C for ; Wed, 19 Dec 2007 18:24:03 -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 06505-05 for ; Wed, 19 Dec 2007 18:24:00 -0400 (AST) Received: from mx2.imatech.net (mx2.imatech.net [81.255.215.243]) by postgresql.org (Postfix) with ESMTP id 5A5CB2E2EA4 for ; Wed, 19 Dec 2007 18:24:00 -0400 (AST) Received: from mx.imatech.net ([81.255.215.253]:3054) by mx2.imatech.net with [XMail 1.23 ESMTP Server] id for from ; Tue, 18 Dec 2007 22:44:30 +0100 X-AuthUser: olivier.boissard@cerene.fr Received: from [192.168.0.11] ([88.161.145.183]:2160) by mx.imatech.net with [XMail 1.23 ESMTP Server] id for from ; Wed, 19 Dec 2007 23:23:45 +0100 Message-ID: <476999FC.9090306@cerene.fr> Date: Wed, 19 Dec 2007 23:23:56 +0100 From: "olivier.boissard@cerene.fr" User-Agent: Thunderbird 1.5.0.13 (Windows/20070809) MIME-Version: 1.0 To: Scott Marlowe CC: pgsql-admin@postgresql.org Subject: Re: How to uses self query plan References: <476847DB.9090401@cerene.fr> <4768CEEF.9060506@cerene.fr> In-Reply-To: Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 8bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200712/180 X-Sequence-Number: 27697 You're right . I will begin by migrate postgresql version. postgresql is now to 8.3 version . A lot of performance inprovements have been made in this last version . In any case I must do a pg_dump/pg_restore. So , Should I try directly 8.3 version or is it more cautious to stay in 8.1.x version ? Olivier Scott Marlowe a écrit : > On Dec 19, 2007 1:57 AM, olivier boissard wrote: > >> Scott Marlowe a écrit : >> >> >>> 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? >>> >>> >> 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. > > ---------------------------(end of broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings > >