Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtp (Exim 4.84_2)
	(envelope-from
 <pgsql-performance-owner+M54655=pgsql-performance-ar@postgresql.org>)
	id 1cFoLn-0007zM-Oy
	for pgsql-performance@arkaria.postgresql.org; Sat, 10 Dec 2016 20:34:31 +0000
Received: from localhost ([127.0.0.1] helo=postgresql.org)
	by malur.postgresql.org with smtp (Exim 4.84_2)
	(envelope-from
 <pgsql-performance-owner+M54655=pgsql-performance-ar@postgresql.org>)
	id 1cFoLm-0000yy-Uh
	for pgsql-performance@arkaria.postgresql.org; Sat, 10 Dec 2016 20:34:30 +0000
Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29])
	by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256)
	(Exim 4.84_2)
	(envelope-from <tomas.vondra@2ndquadrant.com>)
	id 1cFoLk-0000yT-Si
	for pgsql-performance@postgresql.org; Sat, 10 Dec 2016 20:34:29 +0000
Received: from mail-wj0-x233.google.com ([2a00:1450:400c:c01::233])
	by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256)
	(Exim 4.84_2)
	(envelope-from <tomas.vondra@2ndquadrant.com>)
	id 1cFoLh-0004D2-4P
	for pgsql-performance@postgresql.org; Sat, 10 Dec 2016 20:34:28 +0000
Received: by mail-wj0-x233.google.com with SMTP id xy5so41263722wjc.0
        for <pgsql-performance@postgresql.org>;
 Sat, 10 Dec 2016 12:34:24 -0800 (PST)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed;
        d=2ndquadrant-com.20150623.gappssmtp.com; s=20150623;
        h=subject:to:references:cc:from:message-id:date:user-agent
         :mime-version:in-reply-to:content-transfer-encoding;
        bh=9in1j8yDna6WyEon5MZNDTlNV2DcugP5BaW61ARPOl0=;
        b=YM8RHDt9fx5lW9j7G2n+vV2a8I8acG3Wy8RZwiFba47IlsXMsGHDoG1oYzkLMIGpdJ
         8WiACpVy4JWqz5SYHUoNI19Ofvzg9uX1fe8lSzXryOI2VT5LdwglqPAs8D1JRHuDBtnH
         PKC3RS1yonDpJXRBPgKBad5tcIk7qArWYM+LpYvWGC/aVQohmrXyLx8KQmK6q/OGjDsH
         noyZzi5BhZysXRql98PcMmqYWdmpXfVgAxozwuY4wFbJ9TXalFbUhecDhVIGjBzzjdQ1
         E/x+8JAiAoPFvootJqDo/qos0RWLm/3/CnwARCMlq5zkW+5m9H98O3yXE0MkeqweYrKM
         tJZA==
X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed;
        d=1e100.net; s=20130820;
        h=x-gm-message-state:subject:to:references:cc:from:message-id:date
         :user-agent:mime-version:in-reply-to:content-transfer-encoding;
        bh=9in1j8yDna6WyEon5MZNDTlNV2DcugP5BaW61ARPOl0=;
        b=YRz0vjFiiXFMzNzUSK5dJC933GNG2W9wY7YXa297hTxTxj17Y9gV829BLBGmAJ2K8J
         +okRKwmeb2iwubCXfd3IhSKLrcsszj0CdMMIZaBLKdbqXfwClOAKvRMPTf5SBgXB1v02
         5f9jGG4lLhHIeDRNnMVegJjXXPZAQMP2WDqfHiOGViSz6YQSP/PXVWEDRSDLp0vpVNKi
         2yGxN4kmF144dz+nj7TpmQD2UHk8PJl5drSm7nRJ/QVjDRgWZjSEfdIztIVegoaOaxy2
         aQI1HVOov3Y1AxAh0gpkIrrhW/GDVBs7VD/1yioedP7352R6EcERvTgsX28SomYNxcfU
         BBtg==
X-Gm-Message-State: 
 AKaTC02Fno72dCM5cPeOCpGnHPiEU+BFZCOhoBQ+Lq1K5uV/V+YutEcRaCv97A+pWHQ8O13p
X-Received: by 10.194.113.169 with SMTP id iz9mr10808225wjb.57.1481402062084;
        Sat, 10 Dec 2016 12:34:22 -0800 (PST)
Received: from [10.137.2.17] (ip-89-177-46-65.net.upcbroadband.cz.
 [89.177.46.65])
        by smtp.gmail.com with ESMTPSA id
 l2sm49110571wji.7.2016.12.10.12.34.21
        (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128);
        Sat, 10 Dec 2016 12:34:21 -0800 (PST)
Subject: Re: Querying with multicolumn index
To: Tom Lane <tgl@sss.pgh.pa.us>, Eric Jiang <eric@doublemap.com>
References: 
 <CAOfJSTwzQ7Fx6Yjeg9mFkMsM5OVKPoa=EgkHceGdkr1TWg8vkA@mail.gmail.com>
 <5353.1481327501@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org, Eric Clark <eclark@doublemap.com>
From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
Message-ID: <b92cec0d-c4b2-5e44-a6b9-949d2b93e935@2ndquadrant.com>
Date: Sat, 10 Dec 2016 21:34:18 +0100
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:45.0) Gecko/20100101
 Thunderbird/45.4.0
MIME-Version: 1.0
In-Reply-To: <5353.1481327501@sss.pgh.pa.us>
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Transfer-Encoding: 7bit
X-Pg-Spam-Score: -1.9 (-)
List-Archive: <http://archives.postgresql.org/pgsql-performance>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-performance.postgresql.org>
List-Owner: <mailto:pgsql-performance-owner@postgresql.org>
List-Post: <mailto:pgsql-performance@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-performance>
List-Unsubscribe: 
 <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
X-Mailing-List: pgsql-performance
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

Hi,

On 12/10/2016 12:51 AM, Tom Lane wrote:
> Eric Jiang <eric@doublemap.com> writes:
>> I have a query that I *think* should use a multicolumn index, but
>> sometimes isn't, resulting in slow queries.
>
> I tried to duplicate this behavior, without success.  Are you running
> with nondefault planner parameters?
>

My guess is this is a case of LIMIT the matching rows are uniformly 
distributed in the input data. The planner likely concludes that for a 
driver with a lot of data we'll find the first row using ix_updates_time 
very quickly, and that it will be cheaper than inspecting the larger 
multi-column index. But imagine a driver with a lots of data long time 
ago. That breaks the LIMIT fairly quickly.

regards

-- 
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


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