285705 - [PostgreSQL] Index on some text columns on Postgres could use LOWER

Status: RESOLVED FIXED

(Bugzilla :: Database, enhancement, P1)

Description

[Tomas Kopal]

Some (maybe all) text columns will be searched case-insensitive. We are
currently trying to fix this for login_name and realname columns on postgres
(see bug 285695). This comparison will be done on postgres using LOWER(column).
To get the best performance, we need these columns to have functional index
using LOWER(column) as well.
We need either a means of specifying that particular index will be functional
LOWER(), or we need to modify postgres schema to create both normal and LOWER
index on all text fields.

Comment 1

[Ed Sabol]

Well, if it's just some fields and you want to create additional indexes, then you would do something 
like the following in Schema::Pg::_initialize():

    push(@{ $self->{schema}{profiles}{INDEXES} },
         (profiles_lower_login_idx => ['LOWER(login_name)']));
    push(@{ $self->{schema}{bugs}{INDEXES} },
         (profiles_lower_short_desc_idx => ['LOWER(short_desc)']));
    push(@{ $self->{schema}{longdescs}{INDEXES} },
         (profiles_lower_thetext_idx => ['LOWER(longdescs)']));

If it's every text and varchar fields in the schema, well, we would probably want to loop over every 
index, determine the type(s) of the column(s) being indexed, and then either modify the index to be 
LOWER or add a LOWER index to the list of indexes. (Probably the latter, in my opinion.)

So which is it? If it's the former, then I need the list of fields or indexes which need to be LOWER.

Comment 2

[Tomas Kopal]

It's not every text field and varchar. If the field has fulltext index, we don't
need any other. If the field is text or varchar and has normal index, then we
would probably need LOWER index. I think no filed needs both, but am still not
sure that we can change all indexes on text columns to LOWER, we would need to
check the code on case by case basis.
What would be the overhead of storing and maintaining both indexes, normal and
LOWER? Can we afford that? (At least temporarily, before we finalize which text
columns need what index).

Comment 3

[Max Kanat-Alexander]

If you make it a temporary change, make sure that you give one of those indexes
a very distinct name, so that it can be easily dropped if we need to, in a
future version. However, remember that one of them cannot be easily dropped,
because of the way that checksetup works.

I think the overhead of maintaining two indexes would not be terrible, because
Bugzilla is not a high-INSERT-rate product.

Comment 4

[Max Kanat-Alexander]

Attachment: Give an index for LOWER on login_name for Pg only.

Hey dkl. Here's a patch that adds a functional index, since we call
LOWER(login_name) in the WHERE clause all the time, since the patch in bug
285695.

Comment 5

[Joel Peshkin]

Comment on attachment 188886 [details] [diff] [review]
Give an index for LOWER on login_name for Pg only.

r=joel if you've tested it.

Comment 6

[Max Kanat-Alexander]

I have indeed tested it. :-)

Comment 7

[Max Kanat-Alexander]

Tip:

Checking in Bugzilla/DB/Pg.pm;
/cvsroot/mozilla/webtools/bugzilla/Bugzilla/DB/Pg.pm,v  <--  Pg.pm
new revision: 1.15; previous revision: 1.14
done

2.20:

Checking in Bugzilla/DB/Pg.pm;
/cvsroot/mozilla/webtools/bugzilla/Bugzilla/DB/Pg.pm,v  <--  Pg.pm
new revision: 1.12.2.2; previous revision: 1.12.2.1
done