From: | dan(at)sidhe(dot)org |
---|---|
To: | "Robert Haas" <robertmhaas(at)gmail(dot)com> |
Cc: | dan(at)sidhe(dot)org,"Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com>, pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Trying to track down weird query stalls |
Date: | 2009-03-30 20:33:14 |
Message-ID: | 56504.199.172.169.35.1238445194.squirrel@localhost |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
> On Mon, Mar 30, 2009 at 4:02 PM, <dan(at)sidhe(dot)org> wrote:
>>> On Mon, Mar 30, 2009 at 1:42 PM, <dan(at)sidhe(dot)org> wrote:
>>>>> On Mon, Mar 30, 2009 at 12:42 PM, <dan(at)sidhe(dot)org> wrote:
>>>>>> Arguably in this case the actual query should run faster than the
>>>>>> EXPLAIN
>>>>>> ANALYZE version, since the cache is hot. (Though that'd only likely
>>>>>> shave
>>>>>> a few dozen ms off the runtime)
>>>>>
>>>>> Joining a lot of tables together? Could be GEQO kicking in.
>>>>
>>>> Only if I get different query plans for the query depending on whether
>>>> it's being EXPLAIN ANALYZEd or not. That seems unlikely...
>>>
>>> Yes, you can. In fact you often will. Not because it's being
>>> explained or not, just because that's how GEQO works.
>>
>> Ouch. I did *not* know that was possible -- I assumed that the plan was
>> deterministic and independent of explain analyze. The query has seven
>> tables (one of them a temp table) and my geqo_threshold is set to 12. If
>> I'm reading the docs right GEQO shouldn't kick in.
>
> Any chance we could see the actual query? Right now I think we are
> shooting in the dark.
The query is:
select distinct
temp_symbol.entityid,
temp_symbol.libname,
temp_symbol.objid,
temp_symbol.objname,
temp_symbol.fromsymid,
temp_symbol.fromsymtype,
temp_symbol.objinstance,
NULL,
temp_symbol.csid,
libinstance.entityid,
NULL,
libobject.objid,
NULL,
provide_symbol.symbolid,
provide_symbol.symboltype,
libobject.objinstance,
libobject.libinstanceid,
objectinstance.csid,
NULL,
provide_symbol.is_weak,
NULL,
provide_symbol.is_local,
NULL,
provide_symbol.is_template,
NULL,
provide_symbol.is_common
from libinstance,
library,
libobject,
provide_symbol,
temp_symbol,
objectinstance,
attributes
where libinstance.libdate <= 1238445044
and libinstance.enddate > 1238445044
and libinstance.libinstanceid = libobject.libinstanceid
and libinstance.architecture = ?
and attributes.entityid = libinstance.entityid
and attributes.branchid = libinstance.branchid
and attributes.architecture = libinstance.architecture
and library.libid = libinstance.libid
and not secondary
and attribute in ('notoffline', 'notoffline')
and (provide_symbol.symboltype = 'T')
and libobject.objinstance = provide_symbol.objinstance
and libinstance.branchid = ?
and provide_symbol.symbolid = temp_symbol.symbolid
and objectinstance.objinstance = libobject.objinstance
and libinstance.istemp = 0
The explain analyze for the query's attached in a (possibly hopeless)
attempt to keep it from being word-wrapped into unreadability.
-Dan
Attachment | Content-Type | Size |
---|---|---|
slowquery.txt | text/plain | 5.6 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Alvaro Herrera | 2009-03-30 20:39:39 | Re: Trying to track down weird query stalls |
Previous Message | Robert Haas | 2009-03-30 20:13:27 | Re: Trying to track down weird query stalls |