Re: UNIQUE( col1, col2 ) creates what indexes?

Rob Hoopman <rob(at)tuna(dot)nl> writes:
> UNIQUE(iso639, iso3166)

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

It's a two-column index on (iso639, iso3166)

> 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;

A two-column index cannot support a search based on only the second
column. It can support a search based on only the first column,
however. (Think about the physical index ordering to see why.)

Presently, EXPLAIN doesn't show you exactly what the indexscan
conditions are, so it's not obvious what the difference is between
your first three cases. You can figure it out if you have the patience
to study EXPLAIN VERBOSE output, but that's pretty ugly :-(. I have
been thinking about whether it wouldn't be possible for EXPLAIN to emit
a pretty-printed version of the scan conditions. In that case you could
see what was happening in examples like this.

regards, tom lane