Обсуждение: Query Optimization
PostgreSQL version: 8.1.6
OS: Debian etch
The following query needs a lot of time because the query planner
reorders the joins:
select m.message_idnr, v.headervalue, n.headername from dbmail_messages m
join dbmail_headervalue v ON v.physmessage_id=m.physmessage_id
join dbmail_headername n ON v.headername_id=n.id
where m.mailbox_idnr = 1022 AND message_idnr BETWEEN 698928 AND 1496874
and lower(n.headername) IN
('from','to','cc','subject','date','message-id',
'priority','x-priority','references','newsgroups','in-reply-to',
'content-type','x-spam-status','x-spam-flag');
If I prevent the query planner from reordering the joins with 'set
join_collapse_limit=1;' then the same query is faster. At the end of the
Mail is the output of a explain analyze for both cases.
The statistics of the database are updated each night. Is there an error
(in the statistical data) which is responsible for the reordering of the
joins? And if not are there other alternatives for preventing join
reordering?
Thanks
Reinhard
Explain analyze with set join_collapse_limit=8:
Merge Join (cost=388657.62..391332.20 rows=821 width=127) (actual
time=82677.950..89103.192 rows=2699 loops=1)
Merge Cond: ("outer".physmessage_id = "inner".physmessage_id)
-> Sort (cost=2901.03..2902.61 rows=632 width=16) (actual
time=247.238..247.578 rows=373 loops=1)
Sort Key: m.physmessage_id
-> Bitmap Heap Scan on dbmail_messages m (cost=9.16..2871.63
rows=632 width=16) (actual time=38.072..246.509 rows=373 loops=1)
Recheck Cond: (mailbox_idnr = 1022)
Filter: ((message_idnr >= 698928) AND (message_idnr <=
1496874))
-> Bitmap Index Scan on dbmail_messages_8
(cost=0.00..9.16 rows=902 width=0) (actual time=25.561..25.561 rows=615
loops=1)
Index Cond: (mailbox_idnr = 1022)
-> Sort (cost=385756.58..387089.35 rows=533108 width=127) (actual
time=80156.731..85760.186 rows=3278076 loops=1)
Sort Key: v.physmessage_id
-> Hash Join (cost=51.00..285787.17 rows=533108 width=127)
(actual time=34.519..28260.855 rows=3370242 loops=1)
Hash Cond: ("outer".headername_id = "inner".id)
-> Seq Scan on dbmail_headervalue v
(cost=0.00..241200.39 rows=7840939 width=115) (actual
time=0.006..16844.479 rows=7854485 loops=1)
-> Hash (cost=50.72..50.72 rows=113 width=28) (actual
time=34.493..34.493 rows=35 loops=1)
-> Bitmap Heap Scan on dbmail_headername n
(cost=28.44..50.72 rows=113 width=28) (actual time=11.796..34.437
rows=35 loops=1)
Recheck Cond: ((lower((headername)::text) =
'from'::text) OR (lower((headername)::text) = 'to'::text) OR
(lower((headername)::text) = 'cc'::text) OR (lower((headername)::text) =
'subject'::text) OR (lower((headername)::text) = 'date'::text) OR
(lower((headername)::text) = 'message-id'::text) OR
(lower((headername)::text) = 'priority'::text) OR
(lower((headername)::text) = 'x-priority'::text) OR
(lower((headername)::text) = 'references'::text) OR
(lower((headername)::text) = 'newsgroups'::text) OR
(lower((headername)::text) = 'in-reply-to'::text) OR
(lower((headername)::text) = 'content-type'::text) OR
(lower((headername)::text) = 'x-spam-status'::text) OR (lower((hea
dername)::text) = 'x-spam-flag'::text))
-> BitmapOr (cost=28.44..28.44 rows=116
width=0) (actual time=11.786..11.786 rows=0 loops=1)
-> Bitmap Index Scan on
dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
(actual time=0.037..0.037 rows=3 loops=1)
Index Cond:
(lower((headername)::text) = 'from'::text)
-> Bitmap Index Scan on
dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
(actual time=0.013..0.013 rows=3 loops=1)
Index Cond:
(lower((headername)::text) = 'to'::text)
-> Bitmap Index Scan on
dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
(actual time=0.013..0.013 rows=3 loops=1)
Index Cond:
(lower((headername)::text) = 'cc'::text)
-> Bitmap Index Scan on
dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
(actual time=0.014..0.014 rows=3 loops=1)
Index Cond:
(lower((headername)::text) = 'subject'::text)
-> Bitmap Index Scan on
dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
(actual time=0.014..0.014 rows=3 loops=1)
Index Cond:
(lower((headername)::text) = 'date'::text)
-> Bitmap Index Scan on
dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
(actual time=0.019..0.019 rows=4 loops=1)
Index Cond:
(lower((headername)::text) = 'message-id'::text)
-> Bitmap Index Scan on
dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
(actual time=0.012..0.012 rows=2 loops=1)
Index Cond:
(lower((headername)::text) = 'priority'::text)
-> Bitmap Index Scan on
dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
(actual time=0.032..0.032 rows=4 loops=1)
Index Cond:
(lower((headername)::text) = 'x-priority'::text)
-> Bitmap Index Scan on
dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
(actual time=0.015..0.015 rows=1 loops=1)
Index Cond:
(lower((headername)::text) = 'references'::text)
-> Bitmap Index Scan on
dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
(actual time=0.009..0.009 rows=0 loops=1)
Index Cond:
(lower((headername)::text) = 'newsgroups'::text)
-> Bitmap Index Scan on
dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
(actual time=0.014..0.014 rows=3 loops=1)
Index Cond:
(lower((headername)::text) = 'in-reply-to'::text)
-> Bitmap Index Scan on
dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
(actual time=0.013..0.013 rows=1 loops=1)
Index Cond:
(lower((headername)::text) = 'content-type'::text)
-> Bitmap Index Scan on
dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
(actual time=11.549..11.549 rows=2 loops=1)
Index Cond:
(lower((headername)::text) = 'x-spam-status'::text)
-> Bitmap Index Scan on
dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
(actual time=0.019..0.019 rows=3 loops=1)
Index Cond:
(lower((headername)::text) = 'x-spam-flag'::text)
Total runtime: 89277.937 ms
(47 rows)
Explain analyze with set join_collapse_limit=1:
Hash Join (cost=51.00..1607155.00 rows=821 width=127) (actual
time=14.640..47.851 rows=2699 loops=1)
Hash Cond: ("outer".headername_id = "inner".id)
-> Nested Loop (cost=0.00..1607035.43 rows=12071 width=115) (actual
time=0.085..25.057 rows=7025 loops=1)
-> Index Scan using dbmail_messages_mailbox_idx on
dbmail_messages m (cost=0.00..3515.08 rows=632 width=16) (actual
time=0.064..1.070 rows=373 loops=1)
Index Cond: (mailbox_idnr = 1022)
Filter: ((message_idnr >= 698928) AND (message_idnr <=
1496874))
-> Index Scan using dbmail_headervalue_physmsg_id on
dbmail_headervalue v (cost=0.00..2526.34 rows=870 width=115) (actual
time=0.010..0.035 rows=19 loops=373)
Index Cond: (v.physmessage_id = "outer".physmessage_id)
-> Hash (cost=50.72..50.72 rows=113 width=28) (actual
time=14.540..14.540 rows=35 loops=1)
-> Bitmap Heap Scan on dbmail_headername n (cost=28.44..50.72
rows=113 width=28) (actual time=14.429..14.492 rows=35 loops=1)
Recheck Cond: ((lower((headername)::text) = 'from'::text)
OR (lower((headername)::text) = 'to'::text) OR
(lower((headername)::text) = 'cc'::text) OR (lower((headername)::text) =
'subject'::text) OR (lower((headername)::text) = 'date'::text) OR
(lower((headername)::text) = 'message-id'::text) OR
(lower((headername)::text) = 'priority'::text) OR
(lower((headername)::text) = 'x-priority'::text) OR
(lower((headername)::text) = 'references'::text) OR
(lower((headername)::text) = 'newsgroups'::text) OR
(lower((headername)::text) = 'in-reply-to'::text) OR
(lower((headername)::text) = 'content-type'::text) OR
(lower((headername)::text) = 'x-spam-status'::text) OR
(lower((headername)::text) = 'x-spam-flag'::text))
-> BitmapOr (cost=28.44..28.44 rows=116 width=0)
(actual time=14.418..14.418 rows=0 loops=1)
-> Bitmap Index Scan on
dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
(actual time=14.197..14.197 rows=3 loops=1)
Index Cond: (lower((headername)::text) =
'from'::text)
-> Bitmap Index Scan on
dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
(actual time=0.015..0.015 rows=3 loops=1)
Index Cond: (lower((headername)::text) =
'to'::text)
-> Bitmap Index Scan on
dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
(actual time=0.012..0.012 rows=3 loops=1)
Index Cond: (lower((headername)::text) =
'cc'::text)
-> Bitmap Index Scan on
dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
(actual time=0.013..0.013 rows=3 loops=1)
Index Cond: (lower((headername)::text) =
'subject'::text)
-> Bitmap Index Scan on
dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
(actual time=0.014..0.014 rows=3 loops=1)
Index Cond: (lower((headername)::text) =
'date'::text)
-> Bitmap Index Scan on
dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
(actual time=0.019..0.019 rows=4 loops=1)
Index Cond: (lower((headername)::text) =
'message-id'::text)
-> Bitmap Index Scan on
dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
(actual time=0.011..0.011 rows=2 loops=1)
Index Cond: (lower((headername)::text) =
'priority'::text)
-> Bitmap Index Scan on
dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
(actual time=0.031..0.031 rows=4 loops=1)
Index Cond: (lower((headername)::text) =
'x-priority'::text)
-> Bitmap Index Scan on
dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
(actual time=0.013..0.013 rows=1 loops=1)
Index Cond: (lower((headername)::text) =
'references'::text)
-> Bitmap Index Scan on
dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
(actual time=0.008..0.008 rows=0 loops=1)
Index Cond: (lower((headername)::text) =
'newsgroups'::text)
-> Bitmap Index Scan on
dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
(actual time=0.014..0.014 rows=3 loops=1)
Index Cond: (lower((headername)::text) =
'in-reply-to'::text)
-> Bitmap Index Scan on
dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
(actual time=0.012..0.012 rows=1 loops=1)
Index Cond: (lower((headername)::text) =
'content-type'::text)
-> Bitmap Index Scan on
dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
(actual time=0.028..0.028 rows=2 loops=1)
Index Cond: (lower((headername)::text) =
'x-spam-status'::text)
-> Bitmap Index Scan on
dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
(actual time=0.018..0.018 rows=3 loops=1)
Index Cond: (lower((headername)::text) =
'x-spam-flag'::text)
Total runtime: 49.634 ms
(41 rows)
--
Reinhard Vicinus
rjm business solutions GmbH
Sperlingweg 3,
68623 Lampertheim
Tel. 06206 9513084
Fax 06206 910315
--
Reinhard Vicinus <rvicinus@rjm.de> writes:
> PostgreSQL version: 8.1.6
> The following query needs a lot of time because the query planner
> reorders the joins:
Try reducing random_page_cost, increasing effective_cache_size, and/or
updating to PG 8.2. Any of these are likely to make it like the
nestloop plan better...
regards, tom lane
It's not necessarily the join order that's an issue; it could also be
due to the merge join that it does in the first case. I've also run into
situations where the cost estimate for a merge join is way off the mark.
Rather than forcing the join order, you might try setting
enable_mergejoin=false.
On Mon, Feb 19, 2007 at 06:03:22PM +0100, Reinhard Vicinus wrote:
> PostgreSQL version: 8.1.6
> OS: Debian etch
>
> The following query needs a lot of time because the query planner
> reorders the joins:
>
> select m.message_idnr, v.headervalue, n.headername from dbmail_messages m
> join dbmail_headervalue v ON v.physmessage_id=m.physmessage_id
> join dbmail_headername n ON v.headername_id=n.id
> where m.mailbox_idnr = 1022 AND message_idnr BETWEEN 698928 AND 1496874
> and lower(n.headername) IN
> ('from','to','cc','subject','date','message-id',
> 'priority','x-priority','references','newsgroups','in-reply-to',
> 'content-type','x-spam-status','x-spam-flag');
>
> If I prevent the query planner from reordering the joins with 'set
> join_collapse_limit=1;' then the same query is faster. At the end of the
> Mail is the output of a explain analyze for both cases.
>
> The statistics of the database are updated each night. Is there an error
> (in the statistical data) which is responsible for the reordering of the
> joins? And if not are there other alternatives for preventing join
> reordering?
>
> Thanks
> Reinhard
>
>
>
> Explain analyze with set join_collapse_limit=8:
>
> Merge Join (cost=388657.62..391332.20 rows=821 width=127) (actual
> time=82677.950..89103.192 rows=2699 loops=1)
> Merge Cond: ("outer".physmessage_id = "inner".physmessage_id)
> -> Sort (cost=2901.03..2902.61 rows=632 width=16) (actual
> time=247.238..247.578 rows=373 loops=1)
> Sort Key: m.physmessage_id
> -> Bitmap Heap Scan on dbmail_messages m (cost=9.16..2871.63
> rows=632 width=16) (actual time=38.072..246.509 rows=373 loops=1)
> Recheck Cond: (mailbox_idnr = 1022)
> Filter: ((message_idnr >= 698928) AND (message_idnr <=
> 1496874))
> -> Bitmap Index Scan on dbmail_messages_8
> (cost=0.00..9.16 rows=902 width=0) (actual time=25.561..25.561 rows=615
> loops=1)
> Index Cond: (mailbox_idnr = 1022)
> -> Sort (cost=385756.58..387089.35 rows=533108 width=127) (actual
> time=80156.731..85760.186 rows=3278076 loops=1)
> Sort Key: v.physmessage_id
> -> Hash Join (cost=51.00..285787.17 rows=533108 width=127)
> (actual time=34.519..28260.855 rows=3370242 loops=1)
> Hash Cond: ("outer".headername_id = "inner".id)
> -> Seq Scan on dbmail_headervalue v
> (cost=0.00..241200.39 rows=7840939 width=115) (actual
> time=0.006..16844.479 rows=7854485 loops=1)
> -> Hash (cost=50.72..50.72 rows=113 width=28) (actual
> time=34.493..34.493 rows=35 loops=1)
> -> Bitmap Heap Scan on dbmail_headername n
> (cost=28.44..50.72 rows=113 width=28) (actual time=11.796..34.437
> rows=35 loops=1)
> Recheck Cond: ((lower((headername)::text) =
> 'from'::text) OR (lower((headername)::text) = 'to'::text) OR
> (lower((headername)::text) = 'cc'::text) OR (lower((headername)::text) =
> 'subject'::text) OR (lower((headername)::text) = 'date'::text) OR
> (lower((headername)::text) = 'message-id'::text) OR
> (lower((headername)::text) = 'priority'::text) OR
> (lower((headername)::text) = 'x-priority'::text) OR
> (lower((headername)::text) = 'references'::text) OR
> (lower((headername)::text) = 'newsgroups'::text) OR
> (lower((headername)::text) = 'in-reply-to'::text) OR
> (lower((headername)::text) = 'content-type'::text) OR
> (lower((headername)::text) = 'x-spam-status'::text) OR (lower((hea
> dername)::text) = 'x-spam-flag'::text))
> -> BitmapOr (cost=28.44..28.44 rows=116
> width=0) (actual time=11.786..11.786 rows=0 loops=1)
> -> Bitmap Index Scan on
> dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
> (actual time=0.037..0.037 rows=3 loops=1)
> Index Cond:
> (lower((headername)::text) = 'from'::text)
> -> Bitmap Index Scan on
> dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
> (actual time=0.013..0.013 rows=3 loops=1)
> Index Cond:
> (lower((headername)::text) = 'to'::text)
> -> Bitmap Index Scan on
> dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
> (actual time=0.013..0.013 rows=3 loops=1)
> Index Cond:
> (lower((headername)::text) = 'cc'::text)
> -> Bitmap Index Scan on
> dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
> (actual time=0.014..0.014 rows=3 loops=1)
> Index Cond:
> (lower((headername)::text) = 'subject'::text)
> -> Bitmap Index Scan on
> dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
> (actual time=0.014..0.014 rows=3 loops=1)
> Index Cond:
> (lower((headername)::text) = 'date'::text)
> -> Bitmap Index Scan on
> dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
> (actual time=0.019..0.019 rows=4 loops=1)
> Index Cond:
> (lower((headername)::text) = 'message-id'::text)
> -> Bitmap Index Scan on
> dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
> (actual time=0.012..0.012 rows=2 loops=1)
> Index Cond:
> (lower((headername)::text) = 'priority'::text)
> -> Bitmap Index Scan on
> dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
> (actual time=0.032..0.032 rows=4 loops=1)
> Index Cond:
> (lower((headername)::text) = 'x-priority'::text)
> -> Bitmap Index Scan on
> dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
> (actual time=0.015..0.015 rows=1 loops=1)
> Index Cond:
> (lower((headername)::text) = 'references'::text)
> -> Bitmap Index Scan on
> dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
> (actual time=0.009..0.009 rows=0 loops=1)
> Index Cond:
> (lower((headername)::text) = 'newsgroups'::text)
> -> Bitmap Index Scan on
> dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
> (actual time=0.014..0.014 rows=3 loops=1)
> Index Cond:
> (lower((headername)::text) = 'in-reply-to'::text)
> -> Bitmap Index Scan on
> dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
> (actual time=0.013..0.013 rows=1 loops=1)
> Index Cond:
> (lower((headername)::text) = 'content-type'::text)
> -> Bitmap Index Scan on
> dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
> (actual time=11.549..11.549 rows=2 loops=1)
> Index Cond:
> (lower((headername)::text) = 'x-spam-status'::text)
> -> Bitmap Index Scan on
> dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
> (actual time=0.019..0.019 rows=3 loops=1)
> Index Cond:
> (lower((headername)::text) = 'x-spam-flag'::text)
> Total runtime: 89277.937 ms
> (47 rows)
>
>
>
> Explain analyze with set join_collapse_limit=1:
>
> Hash Join (cost=51.00..1607155.00 rows=821 width=127) (actual
> time=14.640..47.851 rows=2699 loops=1)
> Hash Cond: ("outer".headername_id = "inner".id)
> -> Nested Loop (cost=0.00..1607035.43 rows=12071 width=115) (actual
> time=0.085..25.057 rows=7025 loops=1)
> -> Index Scan using dbmail_messages_mailbox_idx on
> dbmail_messages m (cost=0.00..3515.08 rows=632 width=16) (actual
> time=0.064..1.070 rows=373 loops=1)
> Index Cond: (mailbox_idnr = 1022)
> Filter: ((message_idnr >= 698928) AND (message_idnr <=
> 1496874))
> -> Index Scan using dbmail_headervalue_physmsg_id on
> dbmail_headervalue v (cost=0.00..2526.34 rows=870 width=115) (actual
> time=0.010..0.035 rows=19 loops=373)
> Index Cond: (v.physmessage_id = "outer".physmessage_id)
> -> Hash (cost=50.72..50.72 rows=113 width=28) (actual
> time=14.540..14.540 rows=35 loops=1)
> -> Bitmap Heap Scan on dbmail_headername n (cost=28.44..50.72
> rows=113 width=28) (actual time=14.429..14.492 rows=35 loops=1)
> Recheck Cond: ((lower((headername)::text) = 'from'::text)
> OR (lower((headername)::text) = 'to'::text) OR
> (lower((headername)::text) = 'cc'::text) OR (lower((headername)::text) =
> 'subject'::text) OR (lower((headername)::text) = 'date'::text) OR
> (lower((headername)::text) = 'message-id'::text) OR
> (lower((headername)::text) = 'priority'::text) OR
> (lower((headername)::text) = 'x-priority'::text) OR
> (lower((headername)::text) = 'references'::text) OR
> (lower((headername)::text) = 'newsgroups'::text) OR
> (lower((headername)::text) = 'in-reply-to'::text) OR
> (lower((headername)::text) = 'content-type'::text) OR
> (lower((headername)::text) = 'x-spam-status'::text) OR
> (lower((headername)::text) = 'x-spam-flag'::text))
> -> BitmapOr (cost=28.44..28.44 rows=116 width=0)
> (actual time=14.418..14.418 rows=0 loops=1)
> -> Bitmap Index Scan on
> dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
> (actual time=14.197..14.197 rows=3 loops=1)
> Index Cond: (lower((headername)::text) =
> 'from'::text)
> -> Bitmap Index Scan on
> dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
> (actual time=0.015..0.015 rows=3 loops=1)
> Index Cond: (lower((headername)::text) =
> 'to'::text)
> -> Bitmap Index Scan on
> dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
> (actual time=0.012..0.012 rows=3 loops=1)
> Index Cond: (lower((headername)::text) =
> 'cc'::text)
> -> Bitmap Index Scan on
> dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
> (actual time=0.013..0.013 rows=3 loops=1)
> Index Cond: (lower((headername)::text) =
> 'subject'::text)
> -> Bitmap Index Scan on
> dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
> (actual time=0.014..0.014 rows=3 loops=1)
> Index Cond: (lower((headername)::text) =
> 'date'::text)
> -> Bitmap Index Scan on
> dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
> (actual time=0.019..0.019 rows=4 loops=1)
> Index Cond: (lower((headername)::text) =
> 'message-id'::text)
> -> Bitmap Index Scan on
> dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
> (actual time=0.011..0.011 rows=2 loops=1)
> Index Cond: (lower((headername)::text) =
> 'priority'::text)
> -> Bitmap Index Scan on
> dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
> (actual time=0.031..0.031 rows=4 loops=1)
> Index Cond: (lower((headername)::text) =
> 'x-priority'::text)
> -> Bitmap Index Scan on
> dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
> (actual time=0.013..0.013 rows=1 loops=1)
> Index Cond: (lower((headername)::text) =
> 'references'::text)
> -> Bitmap Index Scan on
> dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
> (actual time=0.008..0.008 rows=0 loops=1)
> Index Cond: (lower((headername)::text) =
> 'newsgroups'::text)
> -> Bitmap Index Scan on
> dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
> (actual time=0.014..0.014 rows=3 loops=1)
> Index Cond: (lower((headername)::text) =
> 'in-reply-to'::text)
> -> Bitmap Index Scan on
> dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
> (actual time=0.012..0.012 rows=1 loops=1)
> Index Cond: (lower((headername)::text) =
> 'content-type'::text)
> -> Bitmap Index Scan on
> dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
> (actual time=0.028..0.028 rows=2 loops=1)
> Index Cond: (lower((headername)::text) =
> 'x-spam-status'::text)
> -> Bitmap Index Scan on
> dbmail_headername_lower_headername (cost=0.00..2.03 rows=8 width=0)
> (actual time=0.018..0.018 rows=3 loops=1)
> Index Cond: (lower((headername)::text) =
> 'x-spam-flag'::text)
> Total runtime: 49.634 ms
> (41 rows)
>
> --
> Reinhard Vicinus
> rjm business solutions GmbH
> Sperlingweg 3,
> 68623 Lampertheim
> Tel. 06206 9513084
> Fax 06206 910315
> --
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>
--
Jim Nasby jim@nasby.net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)