Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 69278632E39 for ; Mon, 12 Jul 2010 17:33:32 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 32491-06 for ; Mon, 12 Jul 2010 20:33:23 +0000 (UTC) X-Greylist: whitelisted by SQLgrey-1.7.6 Received: from smtp6-g21.free.fr (smtp6-g21.free.fr [212.27.42.6]) by mail.postgresql.org (Postfix) with ESMTP id B6D84632C5C for ; Mon, 12 Jul 2010 17:33:22 -0300 (ADT) Received: from [192.168.1.4] (bgd76-1-82-233-212-24.fbx.proxad.net [82.233.212.24]) by smtp6-g21.free.fr (Postfix) with ESMTP id 43018E080CF; Mon, 12 Jul 2010 22:33:14 +0200 (CEST) Message-ID: <4C3B7C0D.6070404@apra.asso.fr> Date: Mon, 12 Jul 2010 22:33:17 +0200 From: phb07 User-Agent: Thunderbird 2.0.0.24 (X11/20100411) MIME-Version: 1.0 To: Dimitri CC: Robert Haas , damien hostin , 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> 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-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9, RCVD_IN_DNSWL_NONE=-0.0001 X-Spam-Level: X-Archive-Number: 201007/201 X-Sequence-Number: 39590 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. > On 7/9/10, Robert Haas wrote: > >> On Fri, Jul 9, 2010 at 6:13 AM, damien hostin >> wrote: >> >>>> Have you tried running ANALYZE on the production server? >>>> >>>> You might also want to try ALTER TABLE ... SET STATISTICS to a large >>>> value on some of the join columns involved in the query. >>>> >>> Hello, >>> >>> Before comparing the test case on the two machines, I run analyse on the >>> whole and look at pg_stats table to see if change occurs for the columns. >>> but on the production server the stats never became as good as on the >>> desktop computer. I set statistic at 10000 on column used by the join, run >>> analyse which take a 3000000 row sample then look at the stats. The stats >>> are not as good as on the desktop. Row number is nearly the same but only >>> 1 >>> or 2 values are found. >>> >>> The data are not balanced the same way on the two computer : >>> - Desktop is 12000 rows with 6000 implicated in the query (50%), >>> - "Production" (actually a dev/test server) is 6 million rows with 6000 >>> implicated in the query (0,1%). >>> Columns used in the query are nullable, and in the 5994000 other rows that >>> are not implicated in the query these columns are null. >>> >>> I don't know if the statistic target is a % or a number of value to >>> obtain, >>> >> It's a number of values to obtain. >> >> >>> but event set at max (10000), it didn't managed to collect good stats (for >>> this particular query). >>> >> I think there's a cutoff where it won't collect values unless they >> occur significantly more often than the average frequency. I wonder >> if that might be biting you here: without the actual values in the MCV >> table, the join selectivity estimates probably aren't too good. >> >> >>> As I don't know what more to do, my conclusion is that the data need to be >>> better balanced to allow the analyse gather better stats. But if there is >>> a >>> way to improve the stats/query with this ugly balanced data, I'm open to >>> it >>> ! >>> >>> I hope that in real production, data will never be loaded this way. If >>> this >>> appened we will maybe set enable_nestloop to off, but I don't think it's a >>> good solution, other query have a chance to get slower. >>> >> Yeah, that usually works out poorly. >> >> -- >> Robert Haas >> EnterpriseDB: http://www.enterprisedb.com >> The Enterprise Postgres Company >> >> -- >> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-performance >> >> Regards. Philippe Beaudoin.