Re: 57 minute SELECT

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Missed the 2nd part of Claudio's reply here.

I actually tried different settings of work_mem up to 512M which didn't make any difference.

Check constraints appear to be there:

nms=# \d syslog_201304
                                     Table "public.syslog_201304"
  Column  |            Type             |                          Modifiers
----------+-----------------------------+-------------------------------------------------------------
 ip       | inet                        |
 facility | character varying(10)       |
 level    | character varying(10)       |
 datetime | timestamp without time zone |
 program  | character varying(25)       |
 msg      | text                        |
 seq      | bigint                      | not null default nextval('syslog_master_seq_seq'::regclass)
Indexes:
    "syslog_201304_datetime_idx" btree (datetime)
    "syslog_201304_ip_idx" btree (ip)
    "syslog_201304_seq_idx" btree (seq)
Check constraints:
    "syslog_201304_datetime_check" CHECK (datetime >= '2013-04-01'::date AND datetime < '2013-05-01'::date)
Inherits: syslog_master

nms=#


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





[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux