[SQL] Unique index VS unique constraint
I have search for information about the difference between "unique index" and "unique constraint" in PostgreSQL without getting to a specific answer, so I kindly ask for an explanation that helps me clarify such concept. Respectfully, Jorge Maldonado
Re: [SQL] Unique index VS unique constraint
Il 04/10/2013 18:48, JORGE MALDONADO ha scritto: I have search for information about the difference between "unique index" and "unique constraint" in PostgreSQL without getting to a specific answer, so I kindly ask for an explanation that helps me clarify such concept. 2 main differences. First is the meaning: primary key identifies a record. A unique just tells you that that value of the record, in the table is unique. If you use keys, db structure will be more intelligible (my opinion). Second one is functional: in an unique constraint you can allow NULL values and ignore them. A primary key does not allow this. Respectfully, Jorge Maldonado Regards, Luca. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Unique index VS unique constraint
On 10/04/2013 09:48 AM, JORGE MALDONADO wrote: I have search for information about the difference between "unique index" and "unique constraint" in PostgreSQL without getting to a specific answer, so I kindly ask for an explanation that helps me clarify such concept. The way I think of it is, that since the SQL standard does not mention indices an INDEX (UNIQUE or otherwise) is just the databases application of a constraint. So for practical purposes they are the same thing. Respectfully, Jorge Maldonado -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Unique index VS unique constraint
On 10/04/2013 10:41 AM, luca...@gmail.com wrote: Il 04/10/2013 18:48, JORGE MALDONADO ha scritto: I have search for information about the difference between "unique index" and "unique constraint" in PostgreSQL without getting to a specific answer, so I kindly ask for an explanation that helps me clarify such concept. 2 main differences. First is the meaning: primary key identifies a record. A unique just tells you that that value of the record, in the table is unique. If you use keys, db structure will be more intelligible (my opinion). Not sure I follow, you can have a unique index that is not a primary key. A primary key is special kind of unique index: http://www.postgresql.org/docs/9.3/interactive/sql-createtable.html The primary key constraint specifies that a column or columns of a table can contain only unique (non-duplicate), nonnull values. Technically, PRIMARY KEY is merely a combination of UNIQUE and NOT NULL, but identifying a set of columns as primary key also provides metadata about the design of the schema, as a primary key implies that other tables can rely on this set of columns as a unique identifier for rows. Second one is functional: in an unique constraint you can allow NULL values and ignore them. A primary key does not allow this. Respectfully, Jorge Maldonado Regards, Luca. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Advice on defining indexes
I have a table with fields that I guess would be a good idea to set as indexes because users may query it to get results ordered by different criteria. For example: -- Artists Table -- 1. art_id 2. art_name 3. art_bday 4. art_sex 5. art_country (foreign key, there is a table of countries) 6. art_type (foreign key, there is a table of types of artists) 7. art_email 8. art_comment 9. art_ bio "art_id" is the primary key. Users query the table to get results ordered by fields (2) to (6). Is it wise to define such fields as indexes? I ask this question because our database has additional tables with the same characteristics and maybe there would be many indexes. With respect, Jorge Maldonado
Re: [SQL] Advice on defining indexes
JORGE MALDONADO wrote > I have a table with fields that I guess would be a good idea to set as > indexes because users may query it to get results ordered by different > criteria. For example: > > -- > Artists Table > -- > 1. art_id > 2. art_name > 3. art_bday > 4. art_sex > 5. art_country (foreign key, there is a table of countries) > 6. art_type (foreign key, there is a table of types of artists) > 7. art_email > 8. art_comment > 9. art_ bio > > "art_id" is the primary key. > Users query the table to get results ordered by fields (2) to (6). Is it > wise to define such fields as indexes? > > I ask this question because our database has additional tables with the > same characteristics and maybe there would be many indexes. > > With respect, > Jorge Maldonado Some thoughts: Indexes for sorting are less useful than indexes for filtering. I probably would not create an index if it was only intended for sorting. Note that in many situations the number of ordered records will be fairly small so on-the-fly sorting is not going to be that expensive anyway. Indexes decrease insertion/update performance but generally improve selection performance. The relative volume of each is important. Index keys which contain a large number of rows are generally ignored in favor of a table scan. For this reason gender is seldom indexed. You have the option of a partial index if a single key contains a large number of records. Simply index everything but that key. Smaller indexes are better and any searches for the ignored key would end up skipping the index in many cases anyway. Consider create full-text search indexes on the comment/bio column and you can probably also add in the other fields into some form of functional index so that performing a search over that single field will in effect search all of the columns. I'd probably index country and type to make the foreign key lookups faster and then create a functional full-text index on the different text fields. I would then add an index on art_bday and call it done. You can then write a view/function that performs a full-text search against the functional index (or just create an actual column) for most text searches and have separate criteria filters for country/type/birthday. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Advice-on-defining-indexes-tp5773423p5773424.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Advice on defining indexes
I really appreciate your fast and very complete answer. If a table has a foreign key on 2 fields, should I also create an index composed of such fields? For example: --- Table Sources --- 1. src_id 2. src_date 3. Other fields . . . Here, the "primary key" is "src_id + src_date". One "src_id" can exist for several "src_date". --- Table Lists --- 1. lst_id 2. lst_source (points to src_id) 3. lst_date 4. Other fields . . . Here, the "foreign key" is "lst_source + lst_date". Regards, Jorge Maldonado On Fri, Oct 4, 2013 at 5:09 PM, David Johnston wrote: > JORGE MALDONADO wrote > > I have a table with fields that I guess would be a good idea to set as > > indexes because users may query it to get results ordered by different > > criteria. For example: > > > > -- > > Artists Table > > -- > > 1. art_id > > 2. art_name > > 3. art_bday > > 4. art_sex > > 5. art_country (foreign key, there is a table of countries) > > 6. art_type (foreign key, there is a table of types of artists) > > 7. art_email > > 8. art_comment > > 9. art_ bio > > > > "art_id" is the primary key. > > Users query the table to get results ordered by fields (2) to (6). Is it > > wise to define such fields as indexes? > > > > I ask this question because our database has additional tables with the > > same characteristics and maybe there would be many indexes. > > > > With respect, > > Jorge Maldonado > > Some thoughts: > > Indexes for sorting are less useful than indexes for filtering. I probably > would not create an index if it was only intended for sorting. Note that > in > many situations the number of ordered records will be fairly small so > on-the-fly sorting is not going to be that expensive anyway. > > Indexes decrease insertion/update performance but generally improve > selection performance. The relative volume of each is important. > > Index keys which contain a large number of rows are generally ignored in > favor of a table scan. For this reason gender is seldom indexed. > > You have the option of a partial index if a single key contains a large > number of records. Simply index everything but that key. Smaller indexes > are better and any searches for the ignored key would end up skipping the > index in many cases anyway. > > Consider create full-text search indexes on the comment/bio column and you > can probably also add in the other fields into some form of functional > index > so that performing a search over that single field will in effect search > all > of the columns. > > I'd probably index country and type to make the foreign key lookups faster > and then create a functional full-text index on the different text fields. > I would then add an index on art_bday and call it done. You can then write > a view/function that performs a full-text search against the functional > index (or just create an actual column) for most text searches and have > separate criteria filters for country/type/birthday. > > David J. > > > > > > > > -- > View this message in context: > http://postgresql.1045698.n5.nabble.com/Advice-on-defining-indexes-tp5773423p5773424.html > Sent from the PostgreSQL - sql mailing list archive at Nabble.com. > > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql >
Re: [SQL] Unique index VS unique constraint
JORGE MALDONADO wrote > I have search for information about the difference between "unique index" > and "unique constraint" in PostgreSQL without getting to a specific > answer, > so I kindly ask for an explanation that helps me clarify such concept. A constraint says what valid data looks like. An index stores data in such a way as to enhance search performance. Uniqueness is a constraint. It happens to be implemented via the creation of a unique index since an index is quickly able to search all existing values in order to determine if a given value already exists. PostgreSQL has chosen to allow a user to create a unique index directly, instead of only via a constraint, but one should not do so. The uniqueness property is a constraint and so a "unique index" without a corresponding constraint is an improper model. If you look at the model without any indexes (which are non-model objects) you would not be aware of the fact that duplicates are not allowed yet in the implementation that is indeed the case. Logically the constraint layer sits on top of an index and performs its filtering of incoming data so that the index can focus on its roles of storing and retrieving. Extending this thought the underlying index should always be non-Unique and a unique filter/constraint would use that index for validation before passing the new value along. However, practicality leads to the current situation where the index takes on the added role of enforcing uniqueness. This is not the case for any other constraint but the UNIQUE constraints case is so integral to PRIMARY KEY usage that the special case behavior is understandable and much more performant. Conceptually the index is an implementation detail and uniqueness should be associated only with constraints. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Unique-index-VS-unique-constraint-tp5773386p5773427.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Advice on defining indexes
JORGE MALDONADO wrote > If a table has a foreign key on 2 fields, should I also create an index > composed of such fields? Yes. If you want to truly/actually model a foreign key the system will require you to create a unique constraint/index on the "primary/one" side of the relationship. CREATE TABLE list ( lst_source, lst_date, FOREIGN KEY (lst_source, lst_date) REFERENCES source (src_id, src_date) ...; If a unique constraint (in this case I'd suggest primary key) does not exist for source(src_id, src_date) the create table with the foreign key will fail. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Advice-on-defining-indexes-tp5773423p5773428.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Unique index VS unique constraint
Unique indexes can be partial, i.e. defined with a where clause (that must be included in a query so that PostgreSQL knows to use that index) whereas unique constraints cannot. JORGE MALDONADO wrote > I have search for information about the difference between "unique index" > and "unique constraint" in PostgreSQL without getting to a specific > answer, > so I kindly ask for an explanation that helps me clarify such concept. A constraint says what valid data looks like. An index stores data in such a way as to enhance search performance. Uniqueness is a constraint. It happens to be implemented via the creation of a unique index since an index is quickly able to search all existing values in order to determine if a given value already exists. PostgreSQL has chosen to allow a user to create a unique index directly, instead of only via a constraint, but one should not do so. The uniqueness property is a constraint and so a "unique index" without a corresponding constraint is an improper model. If you look at the model without any indexes (which are non-model objects) you would not be aware of the fact that duplicates are not allowed yet in the implementation that is indeed the case. Logically the constraint layer sits on top of an index and performs its filtering of incoming data so that the index can focus on its roles of storing and retrieving. Extending this thought the underlying index should always be non-Unique and a unique filter/constraint would use that index for validation before passing the new value along. However, practicality leads to the current situation where the index takes on the added role of enforcing uniqueness. This is not the case for any other constraint but the UNIQUE constraints case is so integral to PRIMARY KEY usage that the special case behavior is understandable and much more performant. Conceptually the index is an implementation detail and uniqueness should be associated only with constraints. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Unique-index-VS-unique-constraint-tp5773386p5773427.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Unique index VS unique constraint
Steve Grey-2 wrote > Unique indexes can be partial, i.e. defined with a where clause (that must > be included in a query so that PostgreSQL knows to use that index) whereas > unique constraints cannot. This implies there can be data in the table but not in the index and thus said index is not part of the model. This strikes me, though, as a shortcoming of the declarative constraint implementation since such behavior should not modeled via indexes even if that is how they are implemented. The where clause limitation on constraints is arbitrary though adding it just for this would not pass a cost-benefit analysis. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Unique-index-VS-unique-constraint-tp5773386p5773434.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
