sql - indexing multi-column index with Rails / Postgresql -

i have doubt correct indexing. use rails 3.2.13, using posgresql behind. btw, more relational databases/indexing question...

i have table:

# table name: exams # #  id             :integer          not null, primary key #  cognomenome    :string(255) #  matricola      :string(255) #  corsolaurea    :string(255) #  annoaccademico :string(255) #  blablabla # # indexes # #  index_exams_on_annoaccademico  (annoaccademico) #  index_exams_on_cognomenome     (cognomenome) #  index_exams_on_corsolaurea     (corsolaurea) #  index_exams_on_matricola       (matricola)    

i'd query thousands record table (the nymber of records increase every year linearly, 500 items evey years, i.e. 5000-6000 in ten years);

i have make these kind of queries:

select "exams".* "exams" (upper(cognomenome) '%giorgio%') order annoaccademico desc, corsolaurea, cognomenome limit 50 offset 0 

or that:

select "exams".* "exams" (matricola '%8327483274%') order annoaccademico desc, corsolaurea, cognomenome limit 50 offset 0 

or that:

select "exams".* "exams" (annoaccademico = '2013') , (upper(cognomenome) '%giorgio%') order annoaccademico desc, corsolaurea, cognomenome limit 50 offset 0 

or that:

select "exams".* "exams" (corsolaurea = 'infermieristica') , (upper(cognomenome) '%giorgio%') order annoaccademico desc, corsolaurea, cognomenome limit 50 offset 0 

or that:

select "exams".* "exams" (corsolaurea = 'medicina-anatomia i' , annoaccademico = '2013') , (upper(cognomenome) '%giorgio%') order annoaccademico desc, corsolaurea, cognomenome limit 50 offset 0 

in few words, query table using , combination of columns annoaccademico corsolaurea cognomenome matricola

always have order columns: annoaccademico desc corsolaurea cognomenome

my questions:

1) considering table size, suggest anyway use indexes ? 2) showed set indexes on single columns; that's correct ? 3) need add 2 multicolumn index like:

add_index :exams, [:annoaccademico, :corsolaurea, :cognomenome]  add_index :exams, [:annoaccademico, :corsolaurea, :matricola] 

that's right ?

what not clear me point: apart select conditions, indexes useful order clause ?

thanks lot patience / db/sql ingnorance. giorgio solyaris.altervista.org

i'm you, not geek of databases. that's when go through kind of question:

• i identify page perform heavy query, remove temporary authentication / set statically current_user, or other temporary fix allow accessing page directly without going through log-in process
• i write small script access page 100 time (or more, depending of time page load, , time ready wait)
• i write down execution time
• i modify code (in case: add indexes , migrate database, can else trying optimize)
• i run page manually once (cause rails cache lot of stuff , don't want overhead in computation)
• i run script again , compare results

off course need code complete , database filled-up

here script i'm using (you need curl)

#!/bin/bash  time (for ((i=0; i<100;i++)); curl -s -o /dev/null http://127.0.0.1:3000/my_page; done) 

so answer is: test it, kind of case depends on app , data, way know test it