Received: from localhost (unknown [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 3FEFD2E385A for ; Wed, 19 Dec 2007 03:57:44 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 08103-02 for ; Wed, 19 Dec 2007 03:57:33 -0400 (AST) Received: from mx2.imatech.net (mx2.imatech.net [81.255.215.243]) by postgresql.org (Postfix) with ESMTP id 6FCF82E3666 for ; Wed, 19 Dec 2007 03:57:35 -0400 (AST) Received: from mx.imatech.net ([81.255.215.253]:3284) by mx2.imatech.net with [XMail 1.23 ESMTP Server] id for from ; Tue, 18 Dec 2007 08:18:06 +0100 X-AuthUser: olivier.boissard@cerene.fr Received: from [200.200.200.35] ([195.6.193.141]:43054) by mx.imatech.net with [XMail 1.23 ESMTP Server] id for from ; Wed, 19 Dec 2007 08:57:21 +0100 Message-ID: <4768CEEF.9060506@cerene.fr> Date: Wed, 19 Dec 2007 08:57:35 +0100 From: olivier boissard User-Agent: Thunderbird 2.0.0.9 (Windows/20071031) 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> In-Reply-To: Content-Type: multipart/mixed; boundary="------------080607070700080707020201" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200712/176 X-Sequence-Number: 27693 This is a multi-part message in MIME format. --------------080607070700080707020201 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 8bit 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? > > ---------------------------(end of broadcast)--------------------------- > TIP 7: You can help support the PostgreSQL project by donating at > > http://www.postgresql.org/about/donate > > Thanks for reply I will extract some queries. I want to precise that I use postgresql 8.1.0 . Olivier --------------080607070700080707020201 Content-Type: text/x-vcard; charset=utf-8; name="olivier_boissard.vcf" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="olivier_boissard.vcf" begin:vcard fn:Olivier Boissard n:Boissard;Olivier org;quoted-printable:CERENE SERVICES;d=C3=A9veloppement adr:;;3 rue archimede;La Chapelle Saint Luc;AUBE;10600;FRANCE email;internet:olivier.boissard@cerene.fr tel;work:+33.3.25.74.11.78 tel;fax:+33.3.25.78.39.67 note;quoted-printable:"Ce message peut contenir des informations confidentielles et/ou=0D=0A= prot=C3=A9g=C3=A9es. Il est =C3=A0 l'usage exclusif de son destinataire.= Toute=0D=0A= utilisation non autoris=C3=A9e peut =C3=AAtre illicite. Si vous recevez= ce=0D=0A= message par erreur, nous vous remercions d'en aviser imm=C3=A9diatement=0D=0A= l'exp=C3=A9diteur en utilisant la fonction r=C3=A9ponse de votre gestionn= aire=0D=0A= de courrier =C3=A9lectronique."=0D=0A= =0D=0A= "This email may contain confidential information and/or copyright=0D=0A= material. This email is intended for the use of the addressee only.=0D=0A= Any unauthorised use may be unlawful. If you receive this email by=0D=0A= mistake, please advise the sender immediately by using the reply=0D=0A= facility in your email software." url:www.cerene.fr version:2.1 end:vcard --------------080607070700080707020201--