The mail_status table

mail_status is a (mail_id, status) table containing the subset of the

mail that is not “current”, which in terms of status meant, technically:

status & (16+32+256) = 0

Commit de2ee18  and related commit 7804642 in the user interface remove that table

in favor of a partial index on the mail table with the expression:

(status & 32 = 0) which means exactly: “not archived”.

This might raise a few questions among users who have developed their own set of queries. Hopefully the rest of this post will answer them in advance.

Can we keep the old queries (involving mail_status) unchanged?

Yes, by creating a mail_status view emulating the old table, taking advantage of the new index:

CREATE VIEW mail_status AS
 SELECT mail_id, status FROM mail WHERE status&32=0 AND status&(16+256)=0;

Simple views like that are normally inlined by the PostgreSQL optimizer, so this should perform pretty well. When in doubt, use EXPLAIN in SQL to check the execution plan.

What is the motivation behind the change ?

Mostly performance. According to EXPLAIN ANALYZE, joining against a mail_status real table populated with a few thousand messages is fast (which is why this table existed in the first place), but avoiding the join and using the partial index instead is faster.

Also mail_status was maintained by triggers on INSERT, UPDATE, DELETE, and these triggers were not free in execution time. Now they’re no longer necessary and have been removed in the above-mentioned commits.

Why is the index on status&32=0, instead of status&(16+32+256)=0 ?

For simplicity. The triggers maintaining mail_status used the latter expression, but a message with the status “sent” (256) or “trashed” (16), but not “archived”, is a bit of a weird case, because there’s generally no action pending on a message that was sent or moved into the trashcan. It’s easier to reason about this new index knowing that it partitions the mail simply between archived and not archived, matching exactly the “archived” bit in the status.

In most cases, status&32=0 is the expression that should be used to mean this message is “current”. For exact compatibility with the old expression, status&32=0 AND status&(16+256)=0 should be used, so that the PostgreSQL optimizer can use the new index.