Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 




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.

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.

Can you provide the actual EXPLAIN ANALYZE (not just EXPLAIN) outputs you can provide for us to look at? I suspect there's a difference wrt the size of the tables, the distribution of the values of the involved columns, index bloat, or how recent the tables have been analyzed. (Most likely the last.) Dropping the UNIQUE NOT NULL constraint and adding the PRIMARY KEY constraint will cause the index to be recreated, which could affect which plan is chosen and its efficacy. Without the EXPLAIN ANALYZE output, I don't think there's a lot of hope in understanding what's different.

Michael Glaesemann
grzm seespotcode net




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux