Thanks, Claudio: http://explain.depesz.com/s/WJQx -----Original Message----- From: Claudio Freire [mailto:klaussfreire@xxxxxxxxx] Sent: Thursday, 3 October 2013 11:16 AM To: Samuel Stearns Cc: David Johnston; pgsql-performance@xxxxxxxxxxxxxx Subject: Re: 57 minute SELECT On Wed, Oct 2, 2013 at 10:17 PM, Samuel Stearns <sstearns@xxxxxxxxxxxxxxxxxx> 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@xxxxxxxxxxxxxxxxxx> 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? -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
