On Thu, 2005-03-10 at 21:16 +0100, Thomas Mueller wrote: > > Another option (probably would help other dbs too) is to use two > > subselects - one on dbmail_messages and one on dbmail_physmessage. This > > way, the best index for dbmail_messages can be used- > > > > CREATE INDEX dbmail_messages_wide ON dbmail_messages > > (mailbox_idnr,message_idnr,status); > > CREATE INDEX dbmail_messages_wider ON dbmail_messages > > (mailbox_idnr,message_idnr,status,physmessage_id); > > > > I'd have to experiment to find out which one would generate the better > > query plan -- only one of those two indexes should actually be used. > > > > The query then should look like this: > [..] > > That one is impressive too! More than 3 times faster than the original > query! > It doesn't matter if I add one of the indizes, the plan is always the same:
Try swapping the two subselects- and taking out the "ORDER BY" line- The dbimapsession code doesn't need it. This produces a marginally worse plan for SQLite, but it should cause us to skip the sorts in the plan and [hopefully] select the other index. Sorry I can't actually test Pg from here- I'm working largely out of memory here... SELECT a.seen_flag, a.answered_flag, a.deleted_flag, a.flagged_flag, a.draft_flag, a.recent_flag, b.internaldate, b.rfcsize, a.message_idnr FROM ( SELECT id,internal_date AS internaldate, rfcsize FROM dbmail_physmessage WHERE id IN ( SELECT physmessage_id FROM dbmail_messages WHERE message_idnr BETWEEN '1' AND '417966' AND mailbox_idnr = '81' AND (status = '0' OR status = '1') ) ) AS b JOIN ( SELECT seen_flag,answered_flag,deleted_flag, flagged_flag,draft_flag,recent_flag, message_idnr,physmessage_id AS id FROM dbmail_messages WHERE message_idnr BETWEEN '1' AND '417966' AND mailbox_idnr = '81' AND (status = '0' OR status = '1') ) AS a -- Internet Connection High Quality Web Hosting http://www.internetconnection.net/
