indexing multi-column index with Rails / Postgresql

Question

I have a doubt about correct indexing. I use rails 3.2.13, using Posgresql behind. BTW, Probably more a relational databases/indexing question...

I have this 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 like to query the thousands record table (the nymber of records increase every year linearly, say by 500 items evey years, i.e. 5000-6000 in ten years);

I have to make these kind of queries:

SELECT "exams".* FROM "exams" WHERE (upper(cognomenome) like '%GIORGIO%') ORDER BY annoaccademico desc, corsolaurea, cognomenome LIMIT 50 OFFSET 0

or that:

SELECT "exams".* FROM "exams" WHERE (matricola like '%8327483274%') ORDER BY annoaccademico desc, corsolaurea, cognomenome LIMIT 50 OFFSET 0

or that:

SELECT "exams".* FROM "exams" WHERE (annoaccademico = '2013') AND (upper(cognomenome) like '%GIORGIO%') ORDER BY annoaccademico desc, corsolaurea, cognomenome LIMIT 50 OFFSET 0

or that:

SELECT "exams".* FROM "exams" WHERE (corsolaurea = 'Infermieristica') AND (upper(cognomenome) like
'%GIORGIO%') ORDER BY annoaccademico desc, corsolaurea, cognomenome LIMIT 50 OFFSET 0

or that:

SELECT "exams".* FROM "exams" WHERE (corsolaurea = 'Medicina-Anatomia I' and annoaccademico = '2013') AND (upper(cognomenome) like '%GIORGIO%') ORDER BY annoaccademico desc, corsolaurea, cognomenome LIMIT 50 OFFSET 0

In few words, I query the table using some AND combination of columns annoaccademico corsolaurea cognomenome matricola

always I have to order by columns: annoaccademico desc corsolaurea cognomenome

My questions:

1) considering the table size, do you suggest anyway to use indexes ? 2) As showed I already set indexes on single columns; that's correct ? 3) Probably I need to add two multicolumn index like:

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

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

that's right ?

What is not very clear to me is the point: Apart select conditions, Are indexes useful also for the order by clause ?

Thanks a lot for your patience / my db/sql ingnorance. giorgio solyaris.altervista.org

If you want to be able to use an index on an expression like upper(cognomenome) then you have to create the index on that expression: for example "CREATE INDEX ON films ((lower(title)));" — David Aldridge
– David Aldridge, Commented May 4, 2013 at 7:54
Check out the execution plans for each query (using explain analyze select ...). Add inddexes you think might help, then check the execution plan again. For a good introduction on how indexes work see here: use-the-index-luke.com — user330315
– user330315, Commented May 4, 2013 at 15:38

Benjamin Bouchet · Accepted Answer · 2013-05-04 07:59:10Z

1

I'm like you, not a geek of databases. So that's what I do when I go through this kind of question:

I identify the page that will perform this heavy query, eventually remove temporary authentication / set statically a current_user, or any other temporary fix that allow accessing to this page directly without going through a log-in process
I write a small script that access this page 100 time (or more, depending of the time your page load, and the time you are ready to wait)
I write down the execution time
I modify my code (in your case: add indexes and migrate database, but it can be anything else you are trying to optimize)
I run the page manually once (cause Rails will cache a lot of stuff and I don't want this overhead in my computation)
I run the script again and compare the results

Off course you need your code to be complete and the database to be filled-up

Here is the script I'm using (you just need curl)

#!/bin/bash

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

So my answer is: test it, this kind of case depends on your app and your data, so the only way to know is to test it

edited May 4, 2013 at 7:59

answered May 4, 2013 at 7:38

Benjamin Bouchet

13.2k2 gold badges43 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

David Aldridge Over a year ago

If there are only a small number of values for an indexed column then that is generally going to make the index less useful, except for values that have very few occurrences in the table. So actually indexes will become more effective the larger the number of distinct values. Also, I disagree on the issue with strings -- indexing strings is just as effective as indexing other types of values.

Benjamin Bouchet Over a year ago

Alright, thanks for the tip, I'll review my knowledge in indexes. I'll edit my answer

Giorgio Robino Over a year ago

Thanks Benjamin; yes I'll test :-)

Collectives™ on Stack Overflow

indexing multi-column index with Rails / Postgresql

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related