Thomas:
I just got done loading a shitload of messages. I'm seeing _exactly_ the
query plan I suspected, and I'm using the create_tables.sql from
branch_2.0 -- after adding indexes found in the create_tables.sql in
trunk, I noticed an insignificant speed change.
I've got 6000 messages here, and subsecond queries; here's the EXACT
query:
SELECT
a.seen_flag,
a.answered_flag,
a.deleted_flag,
a.flagged_flag,
a.draft_flag,
a.recent_flag,
TO_CHAR(b.internal_date, 'YYYY-MM-DD HH24:MI:SS'),
b.rfcsize,
a.message_idnr
FROM dbmail_physmessage AS b JOIN dbmail_messages AS a
ON a.physmessage_id=b.id
WHERE message_idnr BETWEEN '1' AND '417966'
AND mailbox_idnr = '1'
AND status < '2';
and the plan:
QUERY PLAN
-----------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.00..46.85 rows=2 width=36)
-> Index Scan using dbmail_messages_8 on dbmail_messages a
(cost=0.00..42.01 rows=1 width=28)
Index Cond: ((mailbox_idnr = 1::bigint) AND (status <
2::smallint))
Filter: ((message_idnr >= 1::bigint) AND (message_idnr <=
417966::bigint))
-> Index Scan using dbmail_physmessage_pkey on dbmail_physmessage b
(cost=0.00..4.82 rows=1 width=24)
Index Cond: ("outer".physmessage_id = b.id)
(6 rows)
This time, it _IS_ using the right plans, and it's still very fast.
If you're still seeing these problems, it may be the version of Pg (I'm
using 7.4.7) - or it MAY be data related.
If it is data related, see if you can give me a postgresql dump that
causes the problem (that is, if it IS your mailing lists, reload 'em
into a new db first) - you CAN send it directly to me (just remember to
remove the -dbmail from my email address)
This is with a FRESH install of Pg, and using the dbmail that was in
dbmail_2_0 at rev 1674.
On Fri, 2005-03-11 at 19:17 +0100, Thomas Mueller wrote:
> Geo Carncross wrote:
>
> > 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.
>
> Thanks a lot for your help!
>
> > Need more data... Will try and scrounge some up.
>
> When I did tests I imported some mbox archives of mailing lists, 200000
> mails within minutes :)
>
>
> Thomas
--
Internet Connection High Quality Web Hosting
http://www.internetconnection.net/
