Re: Performance trouble finding records through related records

On 3/3/2011 3:19 AM, sverhagen wrote:
>
> Andy Colson wrote:
>>
>> For your query, I think a join would be the best bet, can we see its
>> explain analyze?
>>
>
>
> Here is a few variations:
>
>
> 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;
>
> -- http://explain.depesz.com/s/Pnb
>
>
> explain analyze SELECT events_events.* FROM events_events,
> events_eventdetails customerDetails
> WHERE events_events.transactionid = customerDetails.transactionid
> AND customerDetails.keyname='customer_id'
> AND substring(customerDetails.value,0,32)='1957'
> AND customerDetails.transactionid IS NOT NULL
> ORDER BY id LIMIT 50;
>
> -- http://explain.depesz.com/s/rDh
>
>
> explain analyze SELECT events_events.* FROM events_events
> JOIN events_eventdetails customerDetails
> ON events_events.transactionid = customerDetails.transactionid
> AND customerDetails.keyname='customer_id'
> AND substring(customerDetails.value,0,32)='1957'
> AND customerDetails.transactionid IS NOT NULL
> ORDER BY id LIMIT 50;
>
> -- http://explain.depesz.com/s/6aB
>
>
> Thanks for your efforts!
>

Huh. Pretty much exactly the same. I'm sorry but I think I'm at my
limit. I'm not sure why the nested loop takes so long, or how to get it
to use something different.

-Andy