On 7/20/07, Michael Glaesemann <[EMAIL PROTECTED]> wrote:
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.
If I read the documentation correctly, PRIMARY KEY is simply syntactic
sugar equivalent to UNIQUE + NOT NULL, the only difference being that
a PRIMARY KEY is reported as such to someone looking at the table
structure, which becomes more intuitive than seeing UNIQUE + NOT NULL.
> 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.
Might it just be that the original UNIQUE + NOT NULL index was bloated
or otherwise degraded, and reindexing it would have resulted in the
same performance gain? That's just a guess.
-Josh
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
