All right. I just got Pg installed here, will to some hacking.
On Fri, 2005-03-11 at 18:00 +0100, Thomas Mueller wrote:
> Geo Carncross wrote:
>
> > 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)
>
> Sorry this will be little longer. To be sure I got everything right I'll
> write in detail what I did.
>
> This is where I started (list of indizes):
> public | dbmail_messages_mailbox_idx | index | dbmail
> | dbmail_messages
> public | dbmail_messages_physmessage_idx | index | dbmail
> | dbmail_messages
> public | dbmail_messages_pkey | index | dbmail
> | dbmail_messages
> public | dbmail_messages_seen_flag_idx | index | dbmail
> | dbmail_messages
> public | dbmail_messages_status_idx | index | dbmail
> | dbmail_messages
> public | dbmail_messages_status_notdeleted_idx | index | dbmail
> | dbmail_messages
> public | dbmail_messages_tmm_status_recent_idx | index | dbmail
> | dbmail_messages
> public | dbmail_messages_unique_id_idx | index | dbmail
> | dbmail_messages
>
> Where dbmail_messages_tmm_status_recent_idx = mailbox_idnr, status,
> recent_flag
>
> VACUUM ANALYZE;
>
> explain 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);
>
> QUERY PLAN
> ----------
> Nested Loop (cost=6467.78..9818.32 rows=8 width=36) (actual
> time=636.215..14318.093 rows=1827 loops=1) -> Nested Loop
> (cost=6467.78..7955.77 rows=467 width=32) (actual
> time=617.371..13171.764 rows=1827 loops=1)
> -> HashAggregate (cost=6467.78..6467.78 rows=467 width=8)
> (actual time=553.705..578.470 rows=1827 loops=1)
> -> Index Scan using dbmail_messages_mailbox_idx on
> dbmail_messages (cost=0.00..6463.54 rows=1699 width=8) (actual
> time=17.364..544.043 rows=1827 loops=1)
> Index Cond: (mailbox_idnr = 81::bigint)
> Filter: ((message_idnr >= 1::bigint) AND
> (message_idnr <= 417966::bigint) AND ((status = 0::smallint) OR (status
> = 1::smallint)))
> -> Index Scan using dbmail_physmessage_pkey on
> dbmail_physmessage (cost=0.00..3.17 rows=1 width=24) (actual
> time=6.862..6.869 rows=1 loops=1827)
> Index Cond: (dbmail_physmessage.id = "outer".physmessage_id)
> -> Index Scan using dbmail_messages_physmessage_idx on
> dbmail_messages (cost=0.00..3.98 rows=1 width=28) (actual
> time=0.588..0.601 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: 14326.437 ms
>
> DROP INDEX dbmail_messages_physmessage_idx;
> 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);
> REINDEX TABLE dbmail_messages;
> VACUUM ANALYZE;
>
> explain 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);
>
> QUERY PLAN
> ----------
> Hash Join (cost=7875.69..13937.33 rows=9 width=36) (actual
> time=14235.767..14269.361 rows=1827 loops=1)
> Hash Cond: ("outer".physmessage_id = "inner".id)
> -> Index Scan using dbmail_messages_mailbox_idx on dbmail_messages
> (cost=0.00..6053.64 rows=1581 width=28) (actual time=0.052..13.376
> rows=1827 loops=1)
> Index Cond: (mailbox_idnr = 81::bigint)
> Filter: ((message_idnr >= 1::bigint) AND (message_idnr <=
> 417966::bigint) AND ((status = 0::smallint) OR (status = 1::smallint)))
> -> Hash (cost=7874.26..7874.26 rows=572 width=32) (actual
> time=14235.648..14235.648 rows=0 loops=1) -> Nested Loop
> (cost=6057.60..7874.26 rows=572 width=32) (actual
> time=741.976..14223.254 rows=1827 loops=1)
> -> HashAggregate (cost=6057.60..6057.60 rows=572
> width=8) (actual time=684.463..708.010 rows=1827 loops=1)
> -> Index Scan using dbmail_messages_mailbox_idx on
> dbmail_messages (cost=0.00..6053.64 rows=1581 width=8) (actual
> time=9.096..674.740 rows=1827 loops=1)
> Index Cond: (mailbox_idnr = 81::bigint)
> Filter: ((message_idnr >= 1::bigint) AND
> (message_idnr <= 417966::bigint) AND ((status = 0::smallint) OR (status
> = 1::smallint)))
> -> Index Scan using dbmail_physmessage_pkey on
> dbmail_physmessage (cost=0.00..3.16 rows=1 width=24) (actual
> time=7.363..7.372 rows=1 loops=1827)
> Index Cond: (dbmail_physmessage.id =
> "outer".physmessage_id)
> Total runtime: 14276.660 ms
>
> Different plans, same runtime. But Pg seems not to want your indizes :-)
>
>
> Thomas
--
Internet Connection High Quality Web Hosting
http://www.internetconnection.net/
