Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 534796326FC for ; Tue, 13 Jul 2010 05:51:59 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 70766-04 for ; Tue, 13 Jul 2010 08:51:51 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from smtp2f.orange.fr (smtp2f.orange.fr [80.12.242.152]) by mail.postgresql.org (Postfix) with ESMTP id 35A366324F8 for ; Tue, 13 Jul 2010 05:51:50 -0300 (ADT) Received: from me-wanadoo.net (localhost [127.0.0.1]) by mwinf2f21.orange.fr (SMTP Server) with ESMTP id 8AC0E8008EBF; Tue, 13 Jul 2010 10:51:48 +0200 (CEST) Received: from me-wanadoo.net (localhost [127.0.0.1]) by mwinf2f21.orange.fr (SMTP Server) with ESMTP id 7B7DF8008EBA; Tue, 13 Jul 2010 10:51:48 +0200 (CEST) Received: from [10.0.0.18] (LNeuilly-152-22-23-133.w193-251.abo.wanadoo.fr [193.251.23.133]) by mwinf2f21.orange.fr (SMTP Server) with ESMTP id 445B38008EBF; Tue, 13 Jul 2010 10:51:48 +0200 (CEST) X-ME-UUID: 20100713085148280.445B38008EBF@mwinf2f21.orange.fr Message-ID: <4C3C2923.8070409@axege.com> Date: Tue, 13 Jul 2010 10:51:47 +0200 From: damien hostin User-Agent: Thunderbird 2.0.0.24 (X11/20100317) MIME-Version: 1.0 To: phb07 CC: pgsql-performance@postgresql.org Subject: Re: Slow query with planner row strange estimation References: <4C2DA7FB.1080705@axege.com> <4C318983.6040600@axege.com> <4C332604.1040409@axege.com> <4C3491AA.60301@axege.com> <4C36F647.4060902@axege.com> <4C3B7C0D.6070404@apra.asso.fr> In-Reply-To: <4C3B7C0D.6070404@apra.asso.fr> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 8bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.8 tagged_above=-10 required=5 tests=BAYES_50=0.8, RCVD_IN_DNSWL_NONE=-0.0001 X-Spam-Level: X-Archive-Number: 201007/205 X-Sequence-Number: 39594 phb07 a écrit : > > Dimitri a écrit : >> It's probably one of the cases when having HINTS in PostgreSQL may be >> very helpful.. >> >> SELECT /*+ enable_nestloop=off */ ... FROM ... >> >> will just fix this query without impacting other queries and without >> adding any additional instructions into the application code.. >> >> So, why there is a such resistance to implement hints withing SQL >> queries in PG?.. >> >> Rgds, >> -Dimitri >> >> > +1. > Another typical case when it would be helpful is with setting the > cursor_tuple_fraction GUC variable for a specific statement, without > being obliged to issue 2 SET statements, one before the SELECT and the > other after. > > I remember that the "dimension" columns of the fact table have indexes like with "WHERE IS NOT NULL" on the column indexed. Example: CREATE INDEX dwhinv_pd2_idx ON dwhinv USING btree (dwhinv_p2rfodstide) TABLESPACE tb_index WHERE dwhinv_p2rfodstide IS NOT NULL; Is the where clause being used to select the sample rows on which the stats will be calculated or just used to exclude values after collecting stat ? As I am writing I realize there's must be no link between a table column stats and an index a the same column. (By the way, If I used is not null on each column with such an index, it changes nothing) About the oracle-like hints, it does not really help, because the query is generated in an external jar that I should fork to include the modification. I would prefer forcing a plan based on the query hashcode, but this does not fix what make the planner goes wrong. -- HOSTIN Damien - Equipe R&D Tel:+33(0)4 63 05 95 40 Société Axège 23 rue Saint Simon 63000 Clermont Ferrand www.axege.com