Thread: 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
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.
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 <polobo@yahoo.com> wrote:
JORGE MALDONADO wroteSome thoughts:> 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
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
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.