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

On Saturday 21 July 2007 08:00:11 Tom Lane wrote:
> "Josh Tolley" <eggyknap(at)gmail(dot)com> writes:
> > 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.
>
> Yeah. There is precious little difference between UNIQUE+NOT NULL
> and PRIMARY KEY --- to be exact, the latter will allow another
> table to reference this one in FOREIGN KEY without specifying
> column names. The planner knows nothing of that little convenience.
>
> The interesting thing about this report is that the plan changed
> after creating the new index. That has to mean that some statistic
> visible to the planner changed. Creating an index does update the
> pg_class columns about the table's size and number of rows, but
> probably those weren't that far off to start with. My bet is that
> the new index is a lot smaller than the old because of bloat in the
> old index. If so, REINDEX would have had the same result.
>
> regards, tom lane

I've done a bit deeper analisys.

In the original setup, the "UNIQUE" constraint had been dropped
*before* doing the tests. So the "slow" case is without the UNIQUE
constraint but with an index. The NOT NULL was instead there.

What I don't understand is why the planner in order to accomplish
a JOIN does the sort if it has no UNIQUEness constraint and doesn't
need to sort if it has.

--
Vincenzo Romano
--
Maybe Computer will never become as intelligent as Humans.
For sure they won't ever become so stupid. [VR-1988]