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

"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