Re: Query hitting empty tables taking 48 minutes

> On Jun 18, 2018, at 4:33 PM, Robert Creager wrote: > >> I cannot explain the discrepancy between the runtimes of 85 seconds versus >> 10857 seconds. > > It would be nice if the auto_explain analyze did include the other > information like the psql analyze does. Like this. I’ll try again.

Re: Query hitting empty tables taking 48 minutes

> On Jun 18, 2018, at 4:04 PM, Laurenz Albe wrote: > > Robert Creager wrote: >> A different query started showing up as the problem, the auto_explain with >> analyze shows an oddity, >> the total query duration is 11k seconds, while the query itself is 3 >> seconds. I captured a ZFS >> snaps

Re: Query hitting empty tables taking 48 minutes

Robert Creager wrote: > A different query started showing up as the problem, the auto_explain with > analyze shows an oddity, > the total query duration is 11k seconds, while the query itself is 3 seconds. > I captured a ZFS > snapshot as soon as the problem was noticed. > >db=tapesystem,us

Re: Query hitting empty tables taking 48 minutes

> On Jun 7, 2018, at 4:18 PM, Robert wrote: > >> You can capture the execution plan of the bad statement by using >> auto_explain, >> that would certainly shed more light on the problem. > A different query started showing up as the problem, the auto_explain with analyze shows an oddity, th

Re: Query hitting empty tables taking 48 minutes

> On Jun 8, 2018, at 10:23 AM, David G. Johnston > wrote: > > ​Not sure what the right answer is but its seems your database (those tables > at least) are mis-configured for the workload being ​executed against them. > Significantly increasing the aggressiveness of the auto-vacuum process a

Re: Query hitting empty tables taking 48 minutes

On Fri, Jun 8, 2018 at 9:17 AM, Robert Creager wrote: > A nightly VACUUM FULL which ran based on heuristics resolved the problem. > This would seem to point to a db problem more than an app problem? I’m > unsure how the app could have an affect of this magnitude on the database, > although I’d l

Re: Query hitting empty tables taking 48 minutes

On Jun 7, 2018, at 4:58 PM, David G. Johnston mailto:david.g.johns...@gmail.com>> wrote: I would suspect that vacuuming these tables would solve your problem. Whether there is an issue beyond a lack of vacuuming, or related to auto-vacuum, I am unsure. Though at this point it may take a vac

Re: Query hitting empty tables taking 48 minutes

> On Jun 7, 2018, at 4:58 PM, David G. Johnston > wrote: > > On Thu, Jun 7, 2018 at 3:02 PM, Robert Creager > wrote: > ​[...]​ > job_id | f | 1 | cc54ca5d-0dca-4b35-acd9-e0fe69c6b247 > > IIUC, the system believes​ your job_entry table h

Re: Query hitting empty tables taking 48 minutes

On Thu, Jun 7, 2018 at 3:02 PM, Robert Creager wrote: > Executing with the job_id shown in the stats of the empty table below > (didn’t change after bunches of executions). The job_entry table has very > ephemeral data in general. > > tapesystem=# EXPLAIN ANALYZE EXECUTE foo('cc54ca5d-0dca-4b35-

Re: Query hitting empty tables taking 48 minutes

Re-sending from a subscribed address (sigh). On Jun 7, 2018, at 4:18 PM, Robert wrote: > On Jun 7, 2018, at 2:15 PM, Laurenz Albe > wrote: > > Robert Creager wrote: >> I have a system running FreeBSD 11.1-STABLE, PostgreSQL 9.6.8,Java OpenJDK >> 1.8.0_131, >

Re: Query hitting empty tables taking 48 minutes

> On Jun 7, 2018, at 3:34 PM, Tom Lane wrote: > > Robert Creager writes: >> Jun 7 17:24:21 blackpearl postgres[10670]: [7737-1] >> db=tapesystem,user=Administrator,app=[unknown],client=127.0.0.1 LOG: >> duration: 2903612.206 ms execute fetch from S_2037436/C_2037437: SELECT * >> FROM ds3

Re: Query hitting empty tables taking 48 minutes

Robert Creager writes: > Jun 7 17:24:21 blackpearl postgres[10670]: [7737-1] > db=tapesystem,user=Administrator,app=[unknown],client=127.0.0.1 LOG: > duration: 2903612.206 ms execute fetch from S_2037436/C_2037437: SELECT * > FROM ds3.blob WHERE EXISTS (SELECT * FROM ds3.job_entry WHERE blob

Re: Query hitting empty tables taking 48 minutes

Robert Creager wrote: > I have a system running FreeBSD 11.1-STABLE, PostgreSQL 9.6.8,Java OpenJDK > 1.8.0_131, > jdbc 9.3-1104-jdbc41 which is exhibiting very bizarre behavior. > A query is executing against a couple of tables that have 1 and 0 records in > them. > ds3.job_entry has 0 rows, ds3.

Re: Query hitting empty tables taking 48 minutes

> On Jun 7, 2018, at 1:14 PM, Adrian Klaver wrote: > > On 06/07/2018 11:55 AM, Robert Creager wrote: >>> On Jun 7, 2018, at 12:40 PM, Adrian Klaver >> > wrote: >>> >>> On 06/07/2018 11:17 AM, Robert Creager wrote: I have a system running FreeBSD 11.1-STA

Re: Query hitting empty tables taking 48 minutes

On 06/07/2018 01:13 PM, Adrian Klaver wrote: On 06/07/2018 12:11 PM, Rob Sargent wrote: What's the url doing in "blob_id = ds3.blob.id

Re: Query hitting empty tables taking 48 minutes

> On Jun 7, 2018, at 12:11 PM, Rob Sargent wrote: > > What's the url doing in "blob_id = ds3.blob.id

Re: Query hitting empty tables taking 48 minutes

On 06/07/2018 11:55 AM, Robert Creager wrote: On Jun 7, 2018, at 12:40 PM, Adrian Klaver > wrote: On 06/07/2018 11:17 AM, Robert Creager wrote: I have a system running FreeBSD 11.1-STABLE, PostgreSQL 9.6.8,Java OpenJDK 1.8.0_131, jdbc 9.3-1104-jdbc41 which i

Re: Query hitting empty tables taking 48 minutes

On 06/07/2018 12:11 PM, Rob Sargent wrote: What's the url doing in "blob_id = ds3.blob.id

Re: Query hitting empty tables taking 48 minutes

On 06/07/2018 12:55 PM, Robert Creager wrote: On Jun 7, 2018, at 12:40 PM, Adrian Klaver > wrote: On 06/07/2018 11:17 AM, Robert Creager wrote: I have a system running FreeBSD 11.1-STABLE, PostgreSQL 9.6.8,Java OpenJDK 1.8.0_131, jdbc 9.3-1104-jdbc41 which

Re: Query hitting empty tables taking 48 minutes

> On Jun 7, 2018, at 12:40 PM, Adrian Klaver wrote: > > On 06/07/2018 11:17 AM, Robert Creager wrote: >> I have a system running FreeBSD 11.1-STABLE, PostgreSQL 9.6.8,Java OpenJDK >> 1.8.0_131, jdbc 9.3-1104-jdbc41 which is exhibiting very bizarre behavior. >> A query is executing against a

Re: Query hitting empty tables taking 48 minutes

On 06/07/2018 11:17 AM, Robert Creager wrote: I have a system running FreeBSD 11.1-STABLE, PostgreSQL 9.6.8,Java OpenJDK 1.8.0_131, jdbc 9.3-1104-jdbc41 which is exhibiting very bizarre behavior.  A query is executing against a couple of tables that have 1 and 0 records in them.  ds3.job_entr

Query hitting empty tables taking 48 minutes

I have a system running FreeBSD 11.1-STABLE, PostgreSQL 9.6.8,Java OpenJDK 1.8.0_131, jdbc 9.3-1104-jdbc41 which is exhibiting very bizarre behavior. A query is executing against a couple of tables that have 1 and 0 records in them. ds3.job_entry has 0 rows, ds3.blob has 1 row. If I execute