Re: [PERFORM] 57 minute SELECT

2013-10-03 Thread Samuel Stearns
-Original Message- From: Claudio Freire [mailto:klaussfre...@gmail.com] Sent: Thursday, 3 October 2013 11:16 AM To: Samuel Stearns Cc: David Johnston; pgsql-performance@postgresql.org Subject: Re: [PERFORM] 57 minute SELECT On Wed, Oct 2, 2013 at 10:17 PM, Samuel Stearns wrote: > T

Re: [PERFORM] 57 minute SELECT

2013-10-03 Thread Samuel Stearns
ay, 4 October 2013 1:55 AM To: Samuel Stearns Cc: Claudio Freire; David Johnston; pgsql-performance@postgresql.org Subject: Re: [PERFORM] 57 minute SELECT On Thu, Oct 03, 2013 at 09:20:52AM -0700, David Kerr wrote: - On Thu, Oct 03, 2013 at 01:47:29AM +, Samuel Stearns wrote: - - Thanks, Cl

Re: [PERFORM] 57 minute SELECT

2013-10-03 Thread David Kerr
On Thu, Oct 03, 2013 at 09:20:52AM -0700, David Kerr wrote: - On Thu, Oct 03, 2013 at 01:47:29AM +, Samuel Stearns wrote: - - Thanks, Claudio: - - - - http://explain.depesz.com/s/WJQx - - You're spending a lot of time in the hash join which can kill a system with - low ram. - - You may, just

Re: [PERFORM] 57 minute SELECT

2013-10-03 Thread David Kerr
On Thu, Oct 03, 2013 at 01:47:29AM +, Samuel Stearns wrote: - Thanks, Claudio: - - http://explain.depesz.com/s/WJQx You're spending a lot of time in the hash join which can kill a system with low ram. You may, just for fun, want to try the query with enable_hashjoin=false. -- Sent via pg

Re: [PERFORM] 57 minute SELECT

2013-10-03 Thread k...@rice.edu
On Thu, Oct 03, 2013 at 04:19:29AM +, Samuel Stearns wrote: > Thanks, Claudio. > > I'll have a look at the clustering. > > We have also noticed that the same query with a datetime range of 3 hours > (rather than 4 months) runs in just 30 seconds: > > AND datetime <= '2013-10-03 10:03:49' >

Re: [PERFORM] 57 minute SELECT

2013-10-03 Thread Ivan Voras
On 03/10/2013 03:17, Samuel Stearns wrote: > The last part, the EXPLAIN, is too big to send. Is there an alternative > way I can get it too you, other than chopping it up and sending in > multiple parts? The usual way is via http://explain.depesz.com/ . signature.asc Description: OpenPGP digi

Re: [PERFORM] 57 minute SELECT

2013-10-02 Thread Samuel Stearns
inal Message- From: Claudio Freire [mailto:klaussfre...@gmail.com] Sent: Thursday, 3 October 2013 1:44 PM To: Samuel Stearns Cc: David Johnston; pgsql-performance@postgresql.org Subject: Re: [PERFORM] 57 minute SELECT On Wed, Oct 2, 2013 at 10:47 PM, Samuel Stearns wrote: > Thanks, Claudi

Re: [PERFORM] 57 minute SELECT

2013-10-02 Thread Claudio Freire
On Wed, Oct 2, 2013 at 10:47 PM, Samuel Stearns wrote: > Thanks, Claudio: > > http://explain.depesz.com/s/WJQx If you have a test database, and if it doesn't hurt other queries of course, try clustering on the ip index. I believe your problem is that the index isn't helping much, it's probably h

Re: [PERFORM] 57 minute SELECT

2013-10-02 Thread Samuel Stearns
gresql.org Subject: Re: [PERFORM] 57 minute SELECT Samuel Stearns-2 wrote > EXPLAIN: &g

Re: [PERFORM] 57 minute SELECT

2013-10-02 Thread David Johnston
Samuel Stearns-2 wrote > EXPLAIN: > > QUERY PLAN > > ---

Re: [PERFORM] 57 minute SELECT

2013-10-02 Thread Samuel Stearns
Thanks, Claudio: http://explain.depesz.com/s/WJQx -Original Message- From: Claudio Freire [mailto:klaussfre...@gmail.com] Sent: Thursday, 3 October 2013 11:16 AM To: Samuel Stearns Cc: David Johnston; pgsql-performance@postgresql.org Subject: Re: [PERFORM] 57 minute SELECT On Wed, Oct

Re: [PERFORM] 57 minute SELECT

2013-10-02 Thread Claudio Freire
On Wed, Oct 2, 2013 at 10:17 PM, Samuel Stearns wrote: > The last part, the EXPLAIN, is too big to send. Is there an alternative way > I can get it too you, other than chopping it up and sending in multiple > parts? Try explain.depesz.com On Wed, Oct 2, 2013 at 10:30 PM, Samuel Stearns wrote

Re: [PERFORM] 57 minute SELECT

2013-10-02 Thread Samuel Stearns
ive = 't'::bpchar) OR (active = 's'::bpchar)) Total runtime: 3419878.638 ms Thank you, Sam -Original Message- From: pgsql-performance-ow...@postgresql.org [mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of Samuel Stearns Sent: Thursday, 3 October 2013 11:00

Re: [PERFORM] 57 minute SELECT

2013-10-02 Thread Samuel Stearns
;2013-08-01 00:00:00'::timestamp without time zone) AND (public.syslog_master.datetime >= '2013-04-12 00:00:00'::timestamp without time -Original Message- From: pgsql-performance-ow...@postgresql.org [mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of David Joh

Re: [PERFORM] 57 minute SELECT

2013-10-02 Thread Samuel Stearns
e-ow...@postgresql.org] On Behalf Of Samuel Stearns Sent: Thursday, 3 October 2013 10:26 AM To: pgsql-performance@postgresql.org Subject: [PERFORM] 57 minute SELECT Howdy, I'm going to post this in 2 parts as I think it's too big for 1 post. Environment: PG 8.4.17 Linux Ubuntu 10.0

Re: [PERFORM] 57 minute SELECT

2013-10-02 Thread David Johnston
Samuel Stearns-2 wrote > Total RAM - 1G > > > 1. Explain on SELECT. So either this is a typo (1 GB of RAM) or your query is likely ending up I/O bound. You should probably provide EXPLAIN and EXPLAIN (ANALYZE) output since even with the schema it is impossible for someone to see what the

Re: [PERFORM] 57 minute SELECT

2013-10-02 Thread Samuel Stearns
gresql.org] On Behalf Of Samuel Stearns Sent: Thursday, 3 October 2013 10:26 AM To: pgsql-performance@postgresql.org Subject: [PERFORM] 57 minute SELECT Howdy, I'm going to post this in 2 parts as I think it's too big for 1 post. Environment: PG 8.4.17 Linux Ubuntu 10.04 Total RAM - 1G Thing

[PERFORM] 57 minute SELECT

2013-10-02 Thread Samuel Stearns
Howdy, I'm going to post this in 2 parts as I think it's too big for 1 post. Environment: PG 8.4.17 Linux Ubuntu 10.04 Total RAM - 1G Things that have been performed: 1. Explain on SELECT. 2. ANALYZE database. 3. VACUUM database. 4. shared_buffers = 256M 5. e