On Saturday 21 July 2007 08:00:11 Tom Lane wrote: > "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 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]
