UNIQUE( col1, col2 ) creates what indexes?

From: Rob Hoopman <rob(at)tuna(dot)nl>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: UNIQUE( col1, col2 ) creates what indexes?
Date: 2002-03-09 17:52:15
Message-ID: 3C8A4BCF.9070201@tuna.nl
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi all,

I've created a table:
CREATE TABLE locales (
-- table specific columns
iso639 varchar(2) NOT NULL,
iso3166 varchar(2),
fallback boolean DEFAULT false,

id bigserial,

PRIMARY KEY(id),
UNIQUE(iso639, iso3166)
);

As the manual states this creates an index on the table, but what index is .

EXPLAIN tells me it does an index scan when doing
SELECT * FROM locales WHERE iso639 = 'fr';
or
SELECT * FROM locales WHERE iso639 = 'fr' AND iso3166 = 'CA';
or
SELECT * FROM locales WHERE iso639 = 'fr' AND fallback = TRUE;

but is doing a seq scan when doing
SELECT * FROM locales WHERE iso3166 = 'CA';
or
SELECT * FROM locales WHERE iso3166 = 'CA' AND fallback = TRUE;

At first I thought this might be due to the iso3166 field not being NOT
NULL but that doesn't seem to be the case. Can anyone tell me what I am
missing?

Regards,
Rob

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2002-03-09 18:11:18 Re: RULE with conditional behaviour?
Previous Message Randy Widell 2002-03-09 16:20:08 Re: PostgreSQL under Cygwin...