Re: Performance trouble finding records through related records

From: Merlin Moncure <mmoncure(at)gmail(dot)com>
To: sverhagen <sverhagen(at)wps-nl(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Performance trouble finding records through related records
Date: 2011-03-07 20:27:24
Message-ID: AANLkTinv1LnT-ioMWh66yZn8=pcbKEBLnNnreG6AOpwC@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Merlin Moncure 2011-03-07 20:40:48 Re: Query performance with disabled hashjoin and mergejoin
Previous Message Kevin Grittner 2011-03-07 20:24:36 Re: plan variations: join vs. exists vs. row comparison