On Thu, 2005-03-10 at 23:13 +0100, Thomas Mueller wrote: > Geo Carncross wrote: > > On Thu, 2005-03-10 at 21:16 +0100, Thomas Mueller wrote: > > > >>> 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); > >>> > [..] > >> 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... > > You only missed 'using(id)'. > The result is even worse than the original query :-) I created both > indizes first.
We'll go back to the other one -- I'm including it here for fun -- without the ORDER BY -- Tom's right, it's not necessary. Can you put explains for this one (just without order by)? DROP the index for dbmail_messages_physmessage_idx - I think Pg is selecting it because it has less collisions, the other one just isn't being used. I'd like an explain of it AFTER the index drop too (remember to REINDEX and do a VACUUM ANALYZE) 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 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 JOIN ( 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 USING (id); -- Internet Connection High Quality Web Hosting http://www.internetconnection.net/
