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 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?