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