"Josh Tolley" <eggyknap@xxxxxxxxx> 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
