Received: from malur.postgresql.org ([217.196.149.56])
 by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from
 <pgsql-bugs-owner+M39681=pgsql+2Dbugs=arkaria.postgresql.org@postgresql.org>)
 id 1XXV2f-0002Db-EJ
 for pgsql-bugs@arkaria.postgresql.org; Fri, 26 Sep 2014 12:54:33 +0000
Received: from localhost ([127.0.0.1] helo=postgresql.org)
 by malur.postgresql.org with smtp (Exim 4.80) (envelope-from
 <pgsql-bugs-owner+M39681=pgsql+2Dbugs=arkaria.postgresql.org@postgresql.org>)
 id 1XXV2e-0000fe-80
 for pgsql-bugs@arkaria.postgresql.org; Fri, 26 Sep 2014 12:54:32 +0000
Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29])
 by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256)
 (Exim 4.80) (envelope-from <marko@joh.to>) id 1XXV2d-0000fW-9e
 for pgsql-bugs@postgresql.org; Fri, 26 Sep 2014 12:54:31 +0000
Received: from mail-wi0-f169.google.com ([209.85.212.169])
 by magus.postgresql.org with esmtps (TLS1.0:RSA_AES_256_CBC_SHA1:256)
 (Exim 4.80) (envelope-from <marko@joh.to>) id 1XXV2V-00007r-Ij
 for pgsql-bugs@postgresql.org; Fri, 26 Sep 2014 12:54:29 +0000
Received: by mail-wi0-f169.google.com with SMTP id hi2so876876wib.4
 for <pgsql-bugs@postgresql.org>; Fri, 26 Sep 2014 05:54:21 -0700 (PDT)
X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed;
 d=1e100.net; s=20130820;
 h=x-gm-message-state:message-id:date:from:user-agent:mime-version:to
 :cc:subject:references:in-reply-to:content-type
 :content-transfer-encoding;
 bh=xtdl/MfNYaDREPOET7LMAOGgDLFbipXjMU9yZA1yGm0=;
 b=X8F3FgHxgUdWraeqNM6vDA/T4Xn+T1buw8qYlmY5yYv5O7o4O/WZAS3PfF7xgjBdTO
 Gae9jhdUPKiaHEaZ0nNJaKSYTt2K75+Q1ZD/InmpAOfm761g9JuVZyfloc8dDCsmIvcz
 A4I8V5//IyoId2TRZRyS2Qf2czjU0g2RtsIld1kXaPr53t5hhcalMQ/8cjzvt/uDqxnk
 q4VvbTP08A6y95DQpPrLVbSySVfiNSyJmisnO89fpBgzKsREvnBTGNB3f4rEQfVo4OlH
 bSnR0qSt8DGoRq9B5YPye4l/TT3Py+igJ+X8Q5NCVqS4vgthFnyxp+nn/fk0V/wJdPzF
 GMAg==
X-Gm-Message-State: 
 ALoCoQl1zOY1vuMBVnmf87LEwUKPNciGqq+29ztYNGOSW0KMcX2sXg1YVDdEPw7SXt98/6slpeSc
X-Received: by 10.194.8.232 with SMTP id u8mr24421804wja.64.1411736061822;
 Fri, 26 Sep 2014 05:54:21 -0700 (PDT)
Received: from Markos-MacBook-Pro.local ([46.246.117.202])
 by mx.google.com with ESMTPSA id pk9sm6072295wjb.16.2014.09.26.05.54.20
 for <multiple recipients>
 (version=TLSv1 cipher=ECDHE-RSA-RC4-SHA bits=128/128);
 Fri, 26 Sep 2014 05:54:20 -0700 (PDT)
Message-ID: <542561FB.3050900@joh.to>
Date: Fri, 26 Sep 2014 14:54:19 +0200
From: Marko Tiikkaja <marko@joh.to>
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.8;
 rv:24.0) Gecko/20100101 Thunderbird/24.6.0
MIME-Version: 1.0
To: Marti Raudsepp <marti@juffo.org>
CC: pgsql-bugs@postgresql.org
Subject: Re: BUG #11500: PRIMARY KEY index not being used
References: <20140926080211.7639.68358@wrigleys.postgresql.org>
 <CABRT9RB0_AhrhYQqDZtuvsqi22weHP9GcCKxLiwbOCR+Ymm1XQ@mail.gmail.com>
In-Reply-To: 
 <CABRT9RB0_AhrhYQqDZtuvsqi22weHP9GcCKxLiwbOCR+Ymm1XQ@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
X-Pg-Spam-Score: -2.6 (--)
List-Archive: <http://archives.postgresql.org/pgsql-bugs>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-bugs.postgresql.org>
List-Owner: <mailto:pgsql-bugs-owner@postgresql.org>
List-Post: <mailto:pgsql-bugs@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-bugs>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-bugs>
X-Mailing-List: pgsql-bugs
Precedence: bulk
Sender: pgsql-bugs-owner@postgresql.org

On 9/26/14 2:02 PM, Marti Raudsepp wrote:
> On Fri, Sep 26, 2014 at 11:02 AM,  <marko@joh.to> wrote:
>> The statistics say that
>> there are no rows where processed=0 (and it's not far from the truth), but
>> it's still a risky plan compared to the PK lookup.
>
>> Any thoughts?
>
> PostgreSQL 9.0 introduced this optimization for greater/less operators:
>
>> When looking up statistics for greater/less-than comparisons, if the
>> comparison value is in the first or last histogram bucket, use an index
>> (if available) to fetch the current actual column minimum or maximum.
>> This greatly improves the accuracy of estimates for comparison values
>> near the ends of the data range, particularly if the range is constantly
>> changing due to addition of new data.
>
> Not sure whether it's a good idea a bad idea, but perhaps a solution
> is to expand this to equality lookups too?

I'm not sure that's the right idea to be honest.  The problem is that 
the planner is taking a risk by using an index which could contain 
(theoretically) any number of matching rows, instead of using the 
primary key which is guaranteed to only contain 0 or 1 rows.  Sure, 
peeking into the index to see that there are indeed some processed=0 
rows would probably discourage the planner from using it, but why bother?

> Does using "WHERE processed <= 0" work around the problem? (Assuming
> you don't have any negative numbers in this column).

I unfortunately already dropped the problematic index, so I can't answer 
that.


.marko


-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs