Re: Performance trouble finding records through related records

On Wed, Mar 2, 2011 at 6:12 PM, sverhagen <sverhagen(at)wps-nl(dot)com> wrote:
> Thanks for your help already!
> Hope you're up for some more :-)
>
>
> Andy Colson wrote:
>>
>> First off, excellent detail.
>>
>> Second, your explain analyze was hard to read... but since you are not
>> really interested in your posted query, I wont worry about looking at
>> it... but... have you seen:
>>
>> http://explain.depesz.com/
>>
>
> Thanks for that. Using it below :-)
>
>
> Andy Colson wrote:
>>
>> If you run the individual queries, without the union, are the part's slow
>> too?
>>
>
> Only problem is the second part. So that part can safely be isolated. Also
> the following does not play a role at this point: WHERE events2.eventtype_id
> IN
> (100,103,105,...
>
> Then I went ahead and denormalized the transactionId on both ends, so that
> both events_events records and events_eventdetails records have the
> transactionId (or NULL). That simplifies the query to this:
>
>        SELECT events_events.* FROM events_events WHERE transactionid IN (
>                SELECT transactionid FROM events_eventdetails customerDetails
>                WHERE customerDetails.keyname='customer_id'
>                AND substring(customerDetails.value,0,32)='1957'
>                AND transactionid IS NOT NULL
>        ) ORDER BY id LIMIT 50;

8.3? try converting the above to WHERE EXISTS or (even better) a JOIN...

merlin