[PERFORM] Trying to track down weird query stalls

2009-03-30 Thread dan
I'm running a 64-bit build of Postgres 8.3.5 on AIX 5.3, and have a really strange, annoying transient problem with one particular query stalling. The symptom here is that when this query is made with X or more records in a temp table involved in the join (where X is constant when the problem

Re: [PERFORM] Trying to track down weird query stalls

2009-03-30 Thread Robert Haas
On Mon, Mar 30, 2009 at 1:50 PM, d...@sidhe.org wrote: I'm running a 64-bit build of Postgres 8.3.5 on AIX 5.3, and have a really strange, annoying transient problem with one particular query stalling. The symptom here is that when this query is made with X or more records in a temp table

Re: [PERFORM] Trying to track down weird query stalls

2009-03-30 Thread dan
On Mon, Mar 30, 2009 at 1:50 PM, d...@sidhe.org wrote: I'm running a 64-bit build of Postgres 8.3.5 on AIX 5.3, and have a really strange, annoying transient problem with one particular query stalling. The symptom here is that when this query is made with X or more records in a temp table

Re: [PERFORM] Trying to track down weird query stalls

2009-03-30 Thread dan
On Mon, Mar 30, 2009 at 2:42 PM, d...@sidhe.org wrote: On Mon, Mar 30, 2009 at 1:50 PM,  d...@sidhe.org wrote: I'm not executing any of the EXPLAINs by hand, because I didn't want to have to worry about typos or filling in temp tables with test data. Inside the app the SQL for the

Re: [PERFORM] Trying to track down weird query stalls

2009-03-30 Thread Scott Marlowe
On Mon, Mar 30, 2009 at 12:42 PM, d...@sidhe.org wrote: On Mon, Mar 30, 2009 at 1:50 PM,  d...@sidhe.org wrote: I'm running a 64-bit build of Postgres 8.3.5 on AIX 5.3, and have a really strange, annoying transient problem with one particular query stalling. The symptom here is that when

Re: [PERFORM] Trying to track down weird query stalls

2009-03-30 Thread dan
On Mon, Mar 30, 2009 at 1:42 PM, d...@sidhe.org wrote: On Mon, Mar 30, 2009 at 12:42 PM,  d...@sidhe.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

Re: [PERFORM] Trying to track down weird query stalls

2009-03-30 Thread Robert Haas
On Mon, Mar 30, 2009 at 4:02 PM, d...@sidhe.org wrote: On Mon, Mar 30, 2009 at 1:42 PM,  d...@sidhe.org wrote: On Mon, Mar 30, 2009 at 12:42 PM,  d...@sidhe.org wrote: Arguably in this case the actual query should run faster than the EXPLAIN ANALYZE version, since the cache is hot. (Though

Re: [PERFORM] Trying to track down weird query stalls

2009-03-30 Thread dan
On Mon, Mar 30, 2009 at 4:02 PM, d...@sidhe.org wrote: On Mon, Mar 30, 2009 at 1:42 PM,  d...@sidhe.org wrote: On Mon, Mar 30, 2009 at 12:42 PM,  d...@sidhe.org wrote: Arguably in this case the actual query should run faster than the EXPLAIN ANALYZE version, since the cache is hot. (Though

Re: [PERFORM] Trying to track down weird query stalls

2009-03-30 Thread Alvaro Herrera
d...@sidhe.org escribió: where libinstance.libdate = 1238445044 and libinstance.enddate 1238445044 and libinstance.libinstanceid = libobject.libinstanceid and libinstance.architecture = ? How are you generating the explain? My bet is that you're just substituting a literal in the

Re: [PERFORM] Trying to track down weird query stalls

2009-03-30 Thread dan
d...@sidhe.org escribió: where libinstance.libdate = 1238445044 and libinstance.enddate 1238445044 and libinstance.libinstanceid = libobject.libinstanceid and libinstance.architecture = ? How are you generating the explain? My bet is that you're just substituting a literal in

Re: [PERFORM] Trying to track down weird query stalls

2009-03-30 Thread Alvaro Herrera
d...@sidhe.org escribió: d...@sidhe.org escribió: where libinstance.libdate = 1238445044 and libinstance.enddate 1238445044 and libinstance.libinstanceid = libobject.libinstanceid and libinstance.architecture = ? How are you generating the explain? My bet is that you're

Re: [PERFORM] Trying to track down weird query stalls

2009-03-30 Thread dan
d...@sidhe.org escribió: d...@sidhe.org escribió: where libinstance.libdate = 1238445044 and libinstance.enddate 1238445044 and libinstance.libinstanceid = libobject.libinstanceid and libinstance.architecture = ? How are you generating the explain? My bet is that you're

Re: [PERFORM] Trying to track down weird query stalls

2009-03-30 Thread Alvaro Herrera
d...@sidhe.org escribió: So what's the ? in the query you pasted earlier? The first ? (for architecture) is 1, the second ? (for branchid) is 0. They both should get passed to Postgres as $1 and $2, respectively, assuming DBD::Pg does its substitution right. (They're both supposed to go

Re: [PERFORM] Trying to track down weird query stalls

2009-03-30 Thread dan
d...@sidhe.org escribió: So what's the ? in the query you pasted earlier? The first ? (for architecture) is 1, the second ? (for branchid) is 0. They both should get passed to Postgres as $1 and $2, respectively, assuming DBD::Pg does its substitution right. (They're both supposed to go

Re: [PERFORM] Trying to track down weird query stalls

2009-03-30 Thread Alvaro Herrera
d...@sidhe.org escribió: Fair enough. (And sorry about the mis-read) Next time this occurs I'll try and duplicate this in psql. FWIW, a quick read of the C underlying the DBD::Pg module shows it using PQexecPrepared, so I'm pretty sure it is using prepared statements with placeholders, but

Re: [PERFORM] Trying to track down weird query stalls

2009-03-30 Thread Scott Carey
On 3/30/09 2:34 PM, d...@sidhe.org d...@sidhe.org wrote: d...@sidhe.org escribió: So what's the ? in the query you pasted earlier? The first ? (for architecture) is 1, the second ? (for branchid) is 0. They both should get passed to Postgres as $1 and $2, respectively, assuming DBD::Pg