Re: Performance trouble finding records through related records

On Thu, Mar 3, 2011 at 9:55 AM, Andy Colson <andy(at)squeakycode(dot)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