Aaron Stone wrote:
> On Sat, 2005-12-10 at 19:59 -0800, Kevin Brown wrote:
> [snip]
>
> > h = g_list_join((GList *)headers,"'),upper('");
> > g_string_printf(q,"SELECT physmessage_id,headername,headervalue "
> > "FROM %sheadervalue v "
> > "JOIN %sheadername n ON v.headername_id=n.id "
> > "WHERE physmessage_id IN (%s) "
> > "AND upper(headername) %s IN (upper('%s'))",
> > DBPFX, DBPFX, r->str, not?"NOT":"", h->str);
> [snip]
>
>
> Should we also add / change the indexes to uppercase?
Hmm....probably, though only PostgreSQL allows you to create
functional indexes, so it's the only engine that'll benefit from that.
The index creation would (using lower instead of upper as suggested
below) look like this:
CREATE INDEX dbmail_headername_lower ON
dbmail_headername(lower(headername));
Note that even with many tens of thousands of messages, the headername
table winds up with only a few hundred entries, and the individual
rows are small, 8 bytes for the id plus up to 100 for the headername
(plus whatever is used to store the length). So the table is almost
always going to easily be small enough that the PG planner will choose
a sequential scan over the table rather than an index scan (the
executor has to visit the rows directly anyway because that's where
the visibility information is stored).
Also, since "lower" is also ANSI-standard and supported by all three
databases, we should probably use that instead of "upper", since the
data will be more readable should we decide to store the data in
case-converted form some time in the future. So, s/upper/lower/g in
the diff (and in the functional index, of course).
--
Kevin Brown [EMAIL PROTECTED]
