Re: Difference between PRIMARY KEY index and UNIQUE-NOT NULL index

[Michael Glaesemann <grzm(at)seespotcode(dot)net>]

On Jul 20, 2007, at 17:54 , Vincenzo Romano wrote:

> In an inner join involving a 16M+ rows table and a 100+ rows table
> performances got drastically improved by 100+ times by replacing a
> UNIQUE-NOT NULL index with a PRIMARY KEY on the very same columns in
> the very same order. The query has not been modified.

There should be no difference in query performance, AIUI.

> In the older case, thanks to the EXPLAIN command, I saw that the join
> was causing a sort on the index elements, while the primary key was
> not.

Can you provide the actual EXPLAIN ANALYZE  (not just EXPLAIN)  
outputs you can provide for us to look at? I suspect there's a  
difference wrt the size of the tables, the distribution of the values  
of the involved columns, index bloat, or how recent the tables have  
been analyzed. (Most likely the last.) Dropping the UNIQUE NOT NULL  
constraint and adding the PRIMARY KEY constraint will cause the index  
to be recreated, which could affect which plan is chosen and its  
efficacy. Without the EXPLAIN ANALYZE output, I don't think there's a  
lot of hope in understanding what's different.

Michael Glaesemann
grzm seespotcode net