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.
Nested Loop (cost=0.00..14945.13 rows=37 width=36) (actual
time=287.826..50919.382 rows=1827 loops=1)
-> Merge IN Join (cost=0.00..10702.27 rows=973 width=32) (actual
time=287.751..49785.674 rows=1827 loops=1)
Merge Cond: ("outer".id = "inner".physmessage_id)
-> Index Scan using dbmail_physmessage_pkey on
dbmail_physmessage (cost=0.00..3842.59 rows=73428 width=24) (actual
time=32.733..6462.048 rows=103804 loops=1)
-> Index Scan using dbmail_messages_physmessage_idx on
dbmail_messages (cost=0.00..25097.05 rows=2750 width=8) (actual
time=161.119..42855.174 rows=1827 loops=1)
Filter: ((message_idnr >= 1::bigint) AND (message_idnr <=
417966::bigint) AND (mailbox_idnr = 81::bigint) AND ((status =
0::smallint) OR (status = 1::smallint)))
-> Index Scan using dbmail_messages_physmessage_idx on
dbmail_messages (cost=0.00..4.35 rows=1 width=28) (actual
time=0.143..0.596 rows=1 loops=1827)
Index Cond: (dbmail_messages.physmessage_id =
"outer".physmessage_id)
Filter: ((message_idnr >= 1::bigint) AND (message_idnr <=
417966::bigint) AND (mailbox_idnr = 81::bigint) AND ((status =
0::smallint) OR (status = 1::smallint)))
Total runtime: 50928.009 ms
Thomas
--
http://www.tmueller.com for pgp key (95702B3B)
