Re: Composite keys

On Mon, Oct 31, 2011 at 1:52 PM, Claudio Freire <klaussfreire(at)gmail(dot)com> wrote:
> On Mon, Oct 31, 2011 at 2:08 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>>> Multicolumn indices on (c1, c2, ..., cn) can only be used on where
>>> clauses involving c1..ck with k<n.
>>
>> I don't think that's true.  I believe it can be used for a query that
>> only touches, say, c2.  It's just extremely inefficient.
>
> Does postgres generate those kinds of plans?
> I do not think so. I've never seen it happening.

Sure it does:

rhaas=# create table baz (a bool, b int, c text, primary key (a, b));
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"baz_pkey" for table "baz"
CREATE TABLE
rhaas=# insert into baz select true, g,
random()::text||random()::text||random()::text||random()::text from
generate_series(1,400000) g;
INSERT 0 400000
rhaas=# analyze baz;
ANALYZE
rhaas=# explain analyze select * from baz where b = 1;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
Index Scan using baz_pkey on baz (cost=0.00..7400.30 rows=1
width=74) (actual time=0.104..20.691 rows=1 loops=1)
Index Cond: (b = 1)
Total runtime: 20.742 ms
(3 rows)

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company