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

RESOLVED FIXED in Bugzilla 2.20

Status

()

P1
enhancement
RESOLVED FIXED
14 years ago
14 years ago

People

(Reporter: Tomas.Kopal, Assigned: mkanat)

Tracking

2.19.3
Bugzilla 2.20
Dependency tree / graph
Bug Flags:
approval +
approval2.20 +

Details

Attachments

(1 attachment)

(Reporter)

Description

14 years ago
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

14 years ago
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.
Status: NEW → ASSIGNED
(Assignee)

Updated

14 years ago
Priority: -- → P1
Target Milestone: --- → Bugzilla 2.20
(Reporter)

Comment 2

14 years ago
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).
(Assignee)

Comment 3

14 years ago
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.
(Assignee)

Updated

14 years ago
Assignee: edwardjsabol → mkanat
Status: ASSIGNED → NEW
(Assignee)

Updated

14 years ago
Status: NEW → ASSIGNED
Version: unspecified → 2.19.3
(Assignee)

Updated

14 years ago
Depends on: 285695
(Assignee)

Comment 4

14 years ago
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.
Attachment #188886 - Flags: review?(dkl)
(Assignee)

Updated

14 years ago
Severity: normal → enhancement
Component: Bugzilla-General → Database
Summary: Index on some text columns on Postgres needs to use LOWER → [PostgreSQL] Index on some text columns on Postgres could use LOWER
(Assignee)

Updated

14 years ago
Attachment #188886 - Flags: review?(dkl) → review?(bugreport)

Comment 5

14 years ago
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.
Attachment #188886 - Flags: review?(bugreport) → review+
(Assignee)

Comment 6

14 years ago
I have indeed tested it. :-)
Flags: approval?
Flags: approval2.20?
Flags: approval?
Flags: approval2.20?
Flags: approval2.20+
Flags: approval+
(Assignee)

Comment 7

14 years ago
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
Status: ASSIGNED → RESOLVED
Last Resolved: 14 years ago
Resolution: --- → FIXED
You need to log in before you can comment on or make changes to this bug.