Paul J Stevens wrote:
Tom Allison wrote:
Does is make sense to add yet another index?
Since postgres performs better with than without, is that in question?
Verified with a couple production 2.0 installs, this does increase IMAP
performance.
I can't see a serious downside. Maybe slightly increased storage
requirements, but nothing to write home about.
or to ensure that all the userid records are inserted as lowercase only?
That only works if you make damn sure there aren't any reasons for doing
things mixed case. I'm not saying there are any - just that it needs
careful deliberation and a lot of QA testing on a stable branch.
Also, we can never be too certain about the source of the inserted
records. Perhaps a trigger that would force the lower-case could cover
that though.
So: yes, my hunch is you have a case, but only if the trigger is
provided as well.
As another PostgreSQL user, personally I prefer the less-invasive
approach of using lower(). Otherwise, any other application that deals
with this data will have to be made aware of the distinction. However,
that does bring us to another point: It might be better to make that a
unique index:
CREATE UNIQUE INDEX lower_userid ON dbmail_users (lower(userid));
Then we preserve case but at the same time prevent duplicate users.
I would think that the existing index can be made to work with a
modification to the INSERT/UPDATE SQL and not have to go through the
process of building out two nearly identical indexes.
We can work on this on the trunk. Could you please file a wishlist bug,
so I won't forget about this?
