The nested loops (which are due to the joins) don't seem to be part of
the problem at all. The main time that is taken (actual time that is)
is in this part:
Index Scan using event_20090526_domain_idx on event_20090526 e
(cost=0.00..10694.13 rows=3606 width=1276) (actual
time=50.233..14305.211 rows=3453 loops=1)
Index Cond: (e.domain_id = d.id)
Which is the leaf node in the query plan, the total time for the query
being: Total runtime: 14380.000 ms
And as I said once that query is run once it then does the same query
plan and has this output for the same leaf node above:
Index Scan using event_20090526_domain_idx on event_20090526 e
(cost=0.00..10694.13 rows=3606 width=1276) (actual time=0.027..7.510
rows=3453 loops=1)
Index Cond: (e.domain_id = d.id)
So it seems to me that once the index is in memory everything is fine
with the world, but the loading of the index into memory is horrendous.
Tom Lane wrote:
> Scott Marlowe <scott.marlowe@gmail.com> writes:
>
>> Without looking at the explain just yet, it seems to me that you are
>> constraining the order of joins to insist that the left joins be done
>> first, then the regular joins second, because of your mix of explicit
>> and implicit join syntax. The query planner is constrained to run
>> explicit joins first, then implicit if I remember correctly.
>>
>
> That isn't true as of recent releases (8.2 and up, I think). It is true
> that there are semantic constraints that prevent certain combinations
> of inner and outer joins from being rearranged ... but if that applies
> here, it would also prevent manual rearrangement, unless the OP decides
> that this query doesn't express quite what he meant.
>
> regards, tom lane
>
--
*Bryce Ewing *| Platform Architect
*DDI:* +64 9 950 2195 *Fax:* +64 9 302 0518
*Mobile:* +64 21 432 293 *Freephone:* 0800 SMX SMX (769 769)
Level 11, 290 Queen Street, Auckland, New Zealand | SMX Ltd | smx.co.nz
<http://smx.co.nz>
SMX | Business Email Specialists
The information contained in this email and any attachments is
confidential. If you are not
the intended recipient then you must not use, disseminate, distribute or
copy any information
contained in this email or any attachments. If you have received this
email in error or you
are not the originally intended recipient please contact SMX immediately
and destroy this email.
