From: Tom Lane <tgl@sss.pgh.pa.us>
To: Greg Stark <stark@mit.edu>
cc: Josh Berkus <josh@agliodbs.com>, Merlin Moncure <mmoncure@gmail.com>,
 pgsql-performance@postgresql.org
Subject: Re: Yet another abort-early plan disaster on 9.3
In-reply-to: 
 <CAM-w4HODbu3jLFbENhUXmU3R6YQUQW2GZQOBzt3vHtVdUO5+hA@mail.gmail.com>
References: <541A2335.3060100@agliodbs.com>
 <CAHyXU0zSCWLYZ34fsgAGn9x+nFEwOih4ve3kyeYonTY6xG1Xbg@mail.gmail.com>
 <WM!2bfedb9f882c0aee19f96e138ce7ba810dc6737fca13f21c249ba37fd452020e43a863c0516a5aab9c7b1bd4f7201d15!@asav-3.01.com>
 <541C7891.7000902@agliodbs.com>
 <CAM-w4HODbu3jLFbENhUXmU3R6YQUQW2GZQOBzt3vHtVdUO5+hA@mail.gmail.com>
Comments: In-reply-to Greg Stark <stark@mit.edu>
 message dated "Sat, 20 Sep 2014 07:38:03 +0100"
Date: Sat, 20 Sep 2014 11:01:01 -0400
Message-ID: <10463.1411225261@sss.pgh.pa.us>
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

Greg Stark <stark@mit.edu> writes:
> On 19 Sep 2014 19:40, "Josh Berkus" <josh@agliodbs.com> wrote:
>> Yeah, here's an example of the canonical case:
>> 
>> Table t1 ( a, b, c )
>> 
>> - "b" is low-cardinality
>> - "c" is high-cardinality
>> - There are separate indexes on both b and c.
>> 
>> SELECT a, b, c FROM t1
>> WHERE b = 2
>> ORDER BY c LIMIT 1;

> You badly want a partial index on c WHERE b=2 for each value of 2 which
> appears in your queries.

Well, if it's *only* b = 2 that you ever search for, then maybe a partial
index would be a good answer.  Personally I'd use a plain btree index on
(b, c).  The planner's been able to match this type of query to
multicolumn indexes for a long time.

			regards, tom lane


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