On a differt DB the index is used, maybe because this is a much
smaller one (5 users) :
Hash Join (cost=1104.77..1823.33 rows=200 width=78)
Hash Cond: ("outer".message_idnr = "inner".message_idnr)
-> Seq Scan on dbmail_messages (cost=0.00..594.37 rows=24437
width=78)
-> Hash (cost=1104.27..1104.27 rows=200 width=8)
-> HashAggregate (cost=1102.27..1104.27 rows=200 width=8)
-> Limit (cost=54.49..951.34 rows=12075 width=8)
-> Hash Join (cost=54.49..951.34 rows=12075
width=8)
Hash Cond: ("outer".mailbox_idnr =
"inner".mailbox_idnr)
-> Seq Scan on dbmail_messages c
(cost=0.00..655.46 rows=24127 width=16)
Filter: (status <> 3)
-> Hash (cost=53.13..53.13 rows=544
width=8)
-> Hash Join (cost=16.38..53.13
rows=544 width=8)
Hash Cond:
("outer".owner_idnr = "inner". user_idnr)
-> Seq Scan on
dbmail_mailboxes b (cost=0.00..25.87 rows=1087 width=16)
-> Hash (cost=16.36..16.36
rows=9 width=8)
-> Seq Scan on
dbmail_users a (cost=0.00..16.36 rows=9 width=8)
Filter:
((curmail_size > 1000
000) AND (last_login < (now() - '2 mons'::interval)))
(17 rows)
After:
dbmail=> alter table dbmail_users alter column user_idnr type int;
ALTER TABLE
dbmail=> alter table dbmail_mailboxes alter column owner_idnr type int;
ALTER TABLE
dbmail=> alter table dbmail_sievescripts alter column owner_idnr type
int;
ALTER TABLE
dbmail=> alter table dbmail_acl alter column user_id type int;
ALTER TABLE
dbmail=> alter table dbmail_mailboxes alter column mailbox_idnr type
int;
ALTER TABLE
dbmail=> alter table dbmail_messages alter column mailbox_idnr type int;
ALTER TABLE
Hash Join (cost=799.17..1482.37 rows=200 width=74)
Hash Cond: ("outer".message_idnr = "inner".message_idnr)
-> Seq Scan on dbmail_messages (cost=0.00..558.80 rows=24480
width=74)
-> Hash (cost=798.67..798.67 rows=200 width=8)
-> HashAggregate (cost=796.67..798.67 rows=200 width=8)
-> Limit (cost=31.31..783.05 rows=1089 width=8)
-> Hash Join (cost=31.31..783.05 rows=1089
width=8)
Hash Cond: ("outer".mailbox_idnr =
"inner".mailbox_idnr)
-> Seq Scan on dbmail_messages c
(cost=0.00..620.00 rows=24170 width=12)
Filter: (status <> 3)
-> Hash (cost=31.19..31.19 rows=49
width=4)
-> Hash Join (cost=1.38..31.19
rows=49 width=4)
Hash Cond:
("outer".owner_idnr = "inner".user_idnr)
-> Seq Scan on
dbmail_mailboxes b (cost=0.00..23.88 rows=1088 width=8)
-> Hash (cost=1.36..1.36
rows=9 width=4)
-> Seq Scan on
dbmail_users a (cost=0.00..1.36 rows=9 width=4)
Filter:
((curmail_size > 1000000) AND (last_login < (now() - '2
mons'::interval)))
(17 rows)
There is still a performance gain to be seen here. I'm not sure why
it still uses width=8 for the mailbox_idnr because they both are INT4
now.
_______________________________________________
Dbmail-dev mailing list
Dbmail-dev@dbmail.org
http://twister.fastxs.net/mailman/listinfo/dbmail-dev
