Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id AE60D64FF27 for ; Thu, 4 Sep 2008 16:55:04 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 28316-01 for ; Thu, 4 Sep 2008 16:54:59 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.175]) by postgresql.org (Postfix) with ESMTP id B5DF564FCEF for ; Thu, 4 Sep 2008 16:54:59 -0300 (ADT) Received: by ug-out-1314.google.com with SMTP id 29so81902ugo.7 for ; Thu, 04 Sep 2008 12:54:57 -0700 (PDT) Received: by 10.67.19.17 with SMTP id w17mr1132184ugi.3.1220558096754; Thu, 04 Sep 2008 12:54:56 -0700 (PDT) Received: from oxford.xeocode.com.enterprisedb.com ( [87.127.95.198]) by mx.google.com with ESMTPS id k30sm2763774ugc.31.2008.09.04.12.54.55 (version=TLSv1/SSLv3 cipher=RC4-MD5); Thu, 04 Sep 2008 12:54:56 -0700 (PDT) To: Tom Lane Cc: Guillaume Cottenceau , Matthew Wakeling , pgsql-performance@postgresql.org Subject: Re: limit clause breaks query planner? In-Reply-To: <25328.1220548472@sss.pgh.pa.us> (Tom Lane's message of "Thu\, 04 Sep 2008 13\:14\:32 -0400") User-Agent: Gnus/5.11 (Gnus v5.11) Emacs/22.1 (gnu/linux) X-Draft-From: ("nnimap+imap.gmail.com:PG" 1443) References: <48BD33B2.D078.0028.0@rentrak.com> <48BD33B2.D078.0028.0@rentrak.com> <5925.1220389248@sss.pgh.pa.us> <48BF195E.D078.0028.0@rentrak.com> <23341.1220542340@sss.pgh.pa.us> <87wshrrj10.fsf@mnc.ch> <25328.1220548472@sss.pgh.pa.us> From: Gregory Stark Organization: EnterpriseDB Date: Thu, 04 Sep 2008 20:54:53 +0100 Message-ID: <87wshrn0tu.fsf@oxford.xeocode.com> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200809/64 X-Sequence-Number: 31377 Tom Lane writes: > Guillaume Cottenceau writes: >> It seems to me that if the correlation is 0.99[1], and you're >> looking for less than 1% of rows, the expected rows may be at the >> beginning or at the end of the heap? > > Right, but if you know the value being searched for, you could then > estimate where it is in the table by consulting the histogram. > > Actually, an even easier hack (which would have the nice property of not > needing to know the exact value being searched for), would simply use > the existing cost estimates if the WHERE variables have low correlation > (meaning the random-locations assumption is probably good), but apply > some sort of penalty factor if the correlation is high. Fwiw this will have all the same problems our existing uses of the correlation have. That doesn't mean we shouldn't do it but I would expect it to be improved along with the other uses when we find a better metric. I did happen to speak to a statistician the other day and was given some terms to google. I'll investigate and see if I get anything useful. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's RemoteDBA services!