Re: 57 minute SELECT

Thanks, Claudio:

http://explain.depesz.com/s/WJQx


-----Original Message-----
From: Claudio Freire [mailto:klaussfreire@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 <sstearns@staff.iinet.net.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?


Try explain.depesz.com


On Wed, Oct 2, 2013 at 10:30 PM, Samuel Stearns <sstearns@staff.iinet.net.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
thrashingthe OS cache. 

Also, you seem to have a problem with constraint exclusion. Some of those bitmap heap scans aren't necessary, and the
plannershould know it. Are you missing the corresponding CHECK constraints on datetime?