On Thu, Mar 3, 2011 at 9:55 AM, Andy Colson <andy@squeakycode.net> wrote:
>> 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.
The join condition is showing up in the explain output as:
Join Filter: ((events_events.transactionid)::text =
(customerdetails.transactionid)::text)
Now why is there a cast to text there on both sides? Do those two
columns have exactly the same datatype? If not, you probably want to
fix that, as it can make a big difference.
Also, how many rows are there in events_events and how many in
events_eventdetails?
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company