From: | Samuel Stearns <sstearns(at)staff(dot)iinet(dot)net(dot)au> |
---|---|
To: | Claudio Freire <klaussfreire(at)gmail(dot)com> |
Cc: | David Johnston <polobo(at)yahoo(dot)com>, "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: 57 minute SELECT |
Date: | 2013-10-03 01:47:29 |
Message-ID: | CB03CD8D2C3F9347BAFEC8EA9DD89C9318D37A50@ISP-OSB-DAG2.win2k.iinet.net.au |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Thanks, Claudio:
http://explain.depesz.com/s/WJQx
-----Original Message-----
From: Claudio Freire [mailto:klaussfreire(at)gmail(dot)com]
Sent: Thursday, 3 October 2013 11:16 AM
To: Samuel Stearns
Cc: David Johnston; pgsql-performance(at)postgresql(dot)org
Subject: Re: [PERFORM] 57 minute SELECT
On Wed, Oct 2, 2013 at 10:17 PM, Samuel Stearns <sstearns(at)staff(dot)iinet(dot)net(dot)au> 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?
On Wed, Oct 2, 2013 at 10:30 PM, Samuel Stearns <sstearns(at)staff(dot)iinet(dot)net(dot)au> wrote:
>
> EXPLAIN:
>
> QUERY PLAN
> ----------------------------------------------------------------------
> ----------------------------------------------------------------------
> ----------------------------------------------------------------------
> - Hash Join (cost=408.53..1962721.39 rows=98068 width=126) (actual
> time=30121.265..3419306.752 rows=1929714 loops=1)
> Hash Cond: (public.syslog_master.ip = public.devices.ip)
So your query is returning 2M rows.
I think you should try lowering work_mem. 512M seems oversized for a query this complex on a system with 1G. You may be thrashing the OS cache.
Also, you seem to have a problem with constraint exclusion. Some of those bitmap heap scans aren't necessary, and the planner should know it. Are you missing the corresponding CHECK constraints on datetime?
From | Date | Subject | |
---|---|---|---|
Next Message | David Johnston | 2013-10-03 01:48:41 | Re: 57 minute SELECT |
Previous Message | Claudio Freire | 2013-10-03 01:45:41 | Re: 57 minute SELECT |