This is going to take longer than I thought- my data set is too small,
and Pg is taking a completely different set of execution plans.
Need more data... Will try and scrounge some up.
On Fri, 2005-03-11 at 12:17 -0500, Geo Carncross wrote:
> 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/
