Re: Index Scan taking long time

On Tue, Jun 16, 2009 at 9:30 PM, Bryce Ewing<bryce@smx.co.nz> wrote:
> Hi,
>
> I have been trying to fix a performance issue that we have which I have
> tracked down to index scans being done on a particular table (or set of
> tables):
>
> The following query:
> explain analyze select *
> FROM inbound.event_20090526 e
> LEFT OUTER JOIN inbound.internal_host i ON (e.mta_host_id = i.id)
> LEFT OUTER JOIN inbound.internal_host iaa ON (e.aamta_host_id = iaa.id)
> LEFT OUTER JOIN inbound.event_status es ON (e.event_status_id = es.id)
> LEFT OUTER JOIN inbound.threat t ON (e.threat_id = t.id), inbound.domain d,
> inbound.event_type et
> WHERE e.domain_id = d.id
> AND e.event_type_id = et.id
> AND d.name IN (
>   'testdomain.com'
> );

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.  So,
making it all explicit might help.  Might not.  But it's a thought