Обсуждение: UNIQUE( col1, col2 ) creates what indexes?

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

Rob Hoopman <rob@tuna.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

Tom Lane wrote:

>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.)
>
I see, I'll declare a seperate index on the second column if the need
arises.

>
>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.
>
To be honest I've never understood too much of the EXPLAIN output to
begin with, so I'll probably get in way over my head when I try and
understand the VERBOSE output. (Chances are I'll get it all backwards
and degrade performance instead :-( ).

Is there any recommended reading on understanding EXPLAIN/ performance
in general? There's probably more to be said on the subject than what's
in the Users Guide.

Thanks for the quick responses, mostly I just read the list and I am
impressed how fast and (mostly) on target the advice on this list often is.
( Thought I'd do some kissing up, I might have a couple more questions
over the next few days ;-)

Regards,
Rob

...
> Thanks for the quick responses, mostly I just read the list and I am
> impressed how fast and (mostly) on target the advice on this list often is.

+2 points for insight and intelligence...

> ( Thought I'd do some kissing up, I might have a couple more questions
> over the next few days ;-)

-1 point for transparent motivation. It would have been -2 points, but
even kiss-ups are right once in a while.

;) *grin*

                  - Thomas