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 pol...@yahoo.com 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] 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
